0

So I read this question: Add a column with a default value to an existing table in SQL Server

And it got me to wondering something:

Most of the answers to that question indicated that you should create a DEFAULT, and bind the new column to the default so that it would be seeded with values.

Seems fair.

BUT: What if I'm ok with supplying a default value for existing rows, but for new rows, I want to force code to set a value for the column -- so I don't want to keep a default on the column?

I could:

  • Add the column with the default and then drop the default
  • Add the column as nullable, UPDATE the table to set my default value, and then alter table to make the column nullable.

I think that both methods would work, but now suppose that I have a lot of rows in that table. Which method is going to be the most efficient?

Community
  • 1
  • 1
JMarsch
  • 21,484
  • 15
  • 77
  • 125
  • Using the default is almost certainly going to be faster because it has less operations to perform. But this could vary depending on the number of rows, the datatype involved and many other factors. This is not going to be very fast on a large table no matter you tackle it. As for which method is fastest...load your database on your test system and try it. – Sean Lange Nov 29 '16 at 20:24
  • Is this column changing data? Because *I want to force code to set a value for the column* might be solved with a computed (persisted?) column or a `VIEW`... – Shnugo Nov 29 '16 at 20:29

0 Answers0