Updating identity column sql server
The ‘magic’ is that the table metadata has changed and it now contains a column with a ‘default’ value: table (the third row in the image above) has a default with value 42.
This is how SQL Server 2012 knows how to show a value for Column 3 for this record, even though is physically missing on the page.
With this ‘magic’ in place the ALTER TABLE will no longer have to update every row in the table and the operation is fast, metadata-only, no matter the number of rows in the table.
This new behavior occurs automatically, no special syntax or setting is required, the engine will simply do the right thing.
Prior to SQL Server 2012 when you add a new non-NULLable column with default values to an existing table a size-of data operation occurs: every row in the table is updated to add the default value of the new column.
But starting with SQL Server 2012 the operation is, in most cases, instantaneous: only the table metadata is changed, no rows are being updated.Lets look at a simple example, we’ll create a table with some rows and then add a non-NULL column with default values.