This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column order?
Sql-server – What happens behind the scenes when adding a nullable column in SQL Server
sql serversql-server-2008
Related Question
- SQL Server – How sp_msforeachdb Works Behind the Scenes
- Data Page Level Changes When Record Length Changes in SQL Server
- SQL Server – Adding Constraint to Table with Existing Data
- SQL Server – Effects of Changing Column Data Type to Increase Data Length
- Sql-server – ‘Row Chaining/Overflow’ in SQL Server when adding column to table
- Sql-server – SQL Server Merge Replication Computed Column Conflict Resolution
- SQL Server SSDT – Adding Non-Nullable Column to Existing Table
- SQL Server – How to Get SQL Server to Recognize a Date Column
Best Answer
No.
This is a metadata only change in SQL Server 2008. See Size-of-data operations when adding and removing columns
SQL Server 2012 can also add non nullable columns with a default without having to update all rows however there are also some circumstances in 2012 in which adding a nullable column is no longer just a metadata change.