Adding new column to existing table with default value

Suppose I have a table called SerialNumbers which has columns:

• Id (int)
• SerialNumber (nvarchar)
• IsValid (bit)
• ToBeUsedBy (DateTime)

Currently this table is being used by numerous different applications. Now we have a requirement to add a new column named CurrentlyAt (int) which will always have a default value of 1. I have gone ahead and added the column with a default value of 1.

So my question is, would I now have to change all my previous insert queries to include the new column or would it add a value by default if I didn’t add it to the old insert statements?

Answer

The answer, of course, is that it depends.

As long as your existing INSERT statements have an explicit column list, you don’t need to edit them, and the default value will be inserted, well, by default.

INSERT INTO SerialNumbers (
  Id,
  SerialNumber,
  IsValid,
  ToBeUsedBy)
SELECT....

BUT if you have any inserts that do not have a column list, they’ll throw an error:

INSERT INTO SerialNumbers 
SELECT....

Because the number of columns in the SELECT, or VALUES clause perhaps, will no longer match the number of columns in the INSERT target table.

The solution there is to add the explicit column list, and then to get in the habit of always using said list, for this very reason.

EDIT: To Sean Lange’s point, I was assuming the new column was NOT NULL. If it’s nullable, then yes, there are more possible outcomes, which are likely to generate outcomes that are not as expected.