Add a column in MS SQL Server table

Question

I wanted to add a column in MS SQL Server. Will it affect any existing stored procedures or triggers or dbms jobs etc? AFAIK, in Oracle once you add a new column to an existing table, you need to recompile any invalid objects. How bout in MS SQL Server?

Answer 1

Adding a column to an existing table, in theory and best practices world is easy, but in reality it is not always as easy as ALTER TABLE ADD COLUMN.

The risk is that any procedure, view or application code that uses a “Select *” or an “Insert Values <…>” (without column names explicitly stated) may not function, or may not function correctly, with an additional column added to the table.

Also note there could be temp tables, table variables and table value parameters built off the table that an additional column affects as well. Best practices and my database development guidelines says not to use the “Select *” or the “Insert Values <…>”.