As idicated in the question, I have a table which has around ~3 Mio rows and 12 columns, I would like to alter it by adding one column with some fixed value. What would be the best way to do it?
I was thinking to use ALTER table, however I do not know whether it is still efficient in this case, as I do not have much experience with SQL. Where is the limit of ALTER?
The best way to do it depends on the capabilities of your SQL Server version and your priorities.
If you're running SQL Server 2012 or later, and the Enterprise Edition (or an equivalent like Developer), adding a column with a fixed default value can be a very fast metadata-only operation. It does this by 'pretending' the table has the extra column and remembering the default value to use.
The downside is that the column value doesn't exist in every row, and so must be added whenever someone later changes the value in the new column. When the happens, if the row with the extra data no longer fits on the 8KB page it will split (for a clustered table) or result in a forwarded record (for a heap).
The choice is really about paying all the costs up front, or bit-by-bit as existing rows are updated.
If you're using a version earlier than 2012, and/or not an Enterprise-equivalent edition, the whole table will be rewritten when a new column is added.
The only exception to that is where the new column allows NULL and NULL is the value to be used in all existing rows - in that case, the change will be metadata-only, and future changes pay the price due. But that's not the scenario you describe anyway.
For a relatively small table like the one in the question, it will probably be just fine to just make the change using ALTER TABLE. Naturally, you'd try it out in your development environment first, just to be sure.
Related Q & A:
