Normally one would think that adding a new, unused column to an existing database table wouldn’t break anything, right?

Well, normally it wouldn’t, unless the column name is the same as on another table, and if some of your queries don’t use the table.column syntax when referring to columns.

For example, assume you have two tables with a many-to-one relationship:

Employee
EmployeeID
EmployeeName
DepartmentID
Active

Department
DepartmentID
DepartmentName

Notice that the Employee table has an “Active” column, but the Department table does not.

Down the road, you decide to add an “Active” column to the Department table, too. You figure since it’s a brand-new column, it shouldn’t break anything. However, if your application uses queries like this:

SELECT EmployeeName, DepartmentName, Active
FROM Employee e INNER JOIN Department d ON e.DepartmentID = d.DepartmentID
WHERE Active=1

they’ll break with an “ambiguous column name ‘Active'” error as soon as you add an “Active” column to the Department table, because now the “Active” column in the above query is ambiguous: does it refer to Employee.Active or Department.Active?

The solution to all this is establishing some good naming & query-writing habits:

1. If you’re going to add a new column, do a search to see if other tables have columns with the same name (e.g. “select * from syscolumns where name = ‘<columnname>'” on SQL Server). If they do, double check procs & queries.
2. Try to use specific column names, e.g. EmployeeName instead of Name. That’ll reduce the change of conflict, as well as make the field names, proc parameters, etc more self-documenting.
3. Make a habit of always using the table.column name convention in queries. So the above query would instead be written as

   SELECT e.EmployeeName, d.DepartmentName, e.Active
   FROM Employee e INNER JOIN Department d ON e.DepartmentID = d.DepartmentID
   WHERE e.Active=1

   which would then protect it if the tables get new, ambiguously-named columns in the future.