{"id":154,"date":"2016-03-14T16:26:14","date_gmt":"2016-03-14T16:26:14","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=154"},"modified":"2016-03-14T16:26:14","modified_gmt":"2016-03-14T16:26:14","slug":"the-mysterious-case-of-the-missing-default-value","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/03\/14\/the-mysterious-case-of-the-missing-default-value\/","title":{"rendered":"The Mysterious Case of the Missing Default Value"},"content":{"rendered":"

Sometimes things are not exactly how we think they are. Read the story of the missing default value and learn why. *First published @ www.SQLServerCentral.com<\/a> (2015\/12\/01)<\/em><\/p>\n

Once upon a time, there was a DBA\u2026 yeah right!<\/p>\n

As probably like many SQL DBA\u2019s, little by little I\u2019m getting more curious about how SQL Server works internally. To learn more, I\u2019m using a variety of resources, books, blog posts and more. An important part of internals is the SQL Server storage engine and one of the first things you need to know is how SQL Server stores the data.<\/p>\n

First, I have to say I\u2019m not claiming to be an expert, and I don\u2019t pretend to speak like one of them. I hope this story can come across to regular people who want to learn about something curious maybe you have never thought of before.<\/p>\n

Background<\/strong><\/p>\n

Not so long ago I had to add one column to a table (What a challenge for a DBA!!), but the table I had to deal with was a terrible, 120 million row table. Does that sound more like a challenge?<\/p>\n

The column was just a flag (bit), to facilitate some logic when accessing the table. This would also allow us to create some filtered indexes. I wanted the field to be NOT NULL and to have a DEFAULT VALUE of 0.<\/p>\n

First idea, easy peasy, just write the command and you\u2019re good to go.<\/p>\n

\r\nALTER TABLE dbo.MyTable \r\nADD Flag BIT NOT NULL CONSTRAINT DF_MyTable_Flag_0 DEFAULT 0\r\n<\/pre>\n
It\u2019s important to mention this happened on a SQL Server 2008R2 instance, which probably lots of people are still running.<\/p>\n
When running this statement, I had time to go for coffee, returning to find it was still running on my DEV box.<\/p>\n
OK, that\u2019s something I don\u2019t want to happen. The table was locked and no one could access it. This might not a big deal in a DEV environment, but a big problem in production (24×7), where people are trying to access the table constantly.<\/p>\n
According to Books online,<\/p>\n
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.\n<\/p><\/blockquote>\n
When you have millions of rows to update, writing the simplest and cheapest value (1 byte), will become really expensive. For many minutes your table will be locked and users will be waiting to access the data.<\/p>\n
So my idea of defining the column properly (in terms of nullability and default value) got to a point where I had to choose between my users or my ego. Since I can\u2019t have people waiting for something like this, the choice was clear.<\/p>\n
I worked around this issue by creating the column as NULL, updating the values to 0 in small batches, and that was it. The job was completed without locking problems.
\nOK, but what about the mysterious default value? Ups, I almost forgot. \ud83d\ude42<\/p>\n
Up to SQL Server 2008R2, when we add a column to a table that contains rows, the metadata is updated, the new column is added to the table, the default constraint is created and every single row within the table is UPDATED to hold the new value, not necessarily in that order.<\/p>\n
We can see how it happens in this demo.<\/p>\n
Create the playground (SQL Server 2008R2)<\/strong><\/p>\n
We need to create one table and insert some values.<\/p>\n
\"01_create_playground_2008R2\"<\/a><\/p>\n
Now let\u2019s see how this row looks like in a data page. You can use the undocumented<\/del> unsupported commands DBCC IND() and DBCC PAGE(), the first to get a list of pages that belong to my index table and the second to dump the content of one of the pages to my messages tab in SSMS.<\/p>\n
\"02_DBCC_PAGE_4_Columns\"<\/a> <\/p>\n
If we look at it, we see the first four bytes belong to metadata (I won\u2019t go into it further, though I\u2019d suggest further reading on this), then we can see the values corresponding to our four columns<\/p>\n
\u2022\t01000000 is column c1 value 1
\n\u2022\t02000000 is column c2 value 2
\n\u2022\t03000000 is column c3 value 3
\n\u2022\t04000000 is column c4 value 4
\n\u2022\t040000, the last 3 bytes, the first two belong to the number of columns and the third one to the NULL bitmap<\/p>\n
Also we can see how a value stored as INT datatype takes 4 bytes (as expected).<\/p>\n
Now if we add our column the way we want, NOT NULL WITH VALUES, we will see how this change is reflected to the data pages.<\/p>\n
 \"03_ALTER_TABLE_DBCC_PAGE_5_Columns\"<\/a><\/p>\n
The new column is added after the existing one, and the new value is written. See that 05000000 is column c5 value 5, and the number of columns is updated to five.
\nThis happened immediately because we had a single row in our table, but let’s see how this works when we have a large number of rows involved.<\/p>\n
 \"04_ALTER_TABLE_lock_problems\"<\/a><\/p>\n
In one of the other windows, we execute a SELECT against our table:<\/p>\n
\"05_SELECT_blocked\"<\/a><\/p>\n
And execute sp_WhoIsActive to see what is going on:<\/p>\n
 \"06_EXECUTE_sp_WhoIsActive\"<\/a><\/p>\n
So for each row in the table, SQL Server actually write the value (see the UPDATE without a WHERE clause). That’s why it takes so long to execute and prevents any other request to access the table)<\/p>\n
OK, but what about the mysterious default value?<\/p>\n
It seems like the SQL Server team has been working hard since this process, adding a NOT NULL column WITH VALUES, has become a much less painful process. I will demo it for you.<\/p>\n
If I say the same statement will take practically no time even when the table contains millions of rows, would you believe me? OK, get ready for what you\u2019re going to see. This will run in a SQL Server 2012+ instance.<\/p>\n
Create the playground (SQL2012 onwards)<\/strong><\/p>\n
We create the same table and insert a large amount of rows as we did back in SQL2008R2.<\/p>\n
 \"07_create_playground_2012\"<\/a><\/p>\n
Previously we have seen that when we create a new column with a default constraint WITH VALUES, SQL Server had to write those values for each row. We also saw how they are stored on each page for that table.<\/p>\n
Let’s give it a go.<\/p>\n
 \"08_ALTER_TABLE_2012\"<\/a><\/p>\n
Wow, that was quick! But wait\u2026 Maybe it didn\u2019t finish properly\u2026 <\/p>\n
 \"09_SELECT_sys_columns\"<\/a><\/p>\n
Ok, the column is there, and so are the values. Let\u2019s have a look at one of the pages to see one row. We’ve seen that specifying the last parameter as 3 in DBCC PAGE() will output each row individually together with each column and value.<\/p>\n
\"12_DBCC_PAGE_missing_value\"<\/a> <\/p>\n
Errrr, where is the DATA? The row does not have the value on it, but we can see in the detail for each row that c5 = 5. Note also that the column count at the end of the row does not match, still shows 4 (0x0400).<\/p>\n
Run again both statements and rub your eyes, the SELECT and DBCC PAGE bring up the column and its value but they actually DO NOT exist in the page, everything is a metadata illusion.<\/p>\n
The Metadata<\/strong><\/p>\n
The metadata associated to this new column is as follows. First, the SQL engine knows there is a new column.<\/p>\n
\"09_SELECT_sys_columns\"<\/a><\/p>\n
And it\u2019s aware of the Default constraint definition.<\/p>\n
\"10_SELECT_sys_default_constraints\"<\/a><\/p>\n
So technically the SQL engine does not care whether the value is stored along with the other values in each row within a data page as long as it knows what the value should be like.
\nWhat if we UPDATE some of the rows?<\/p>\n
\"13_UPDATE_DBCC_PAGE\"<\/a> <\/p>\n
After updating some rows we can see that those rows updated do have the value now (** Note I\u2019m updating c1, nothing to do with c5).<\/p>\n
SQL Server knows there is something not quite consistent and it does the job any other time it\u2019s got to go there to update something else\u2026 Smart!<\/p>\n
Conclusion<\/strong><\/p>\n
The change of behaviour explained is a major improvement and can save a lot of grief for DBAs.The actual work can happen at other more convenient time since the final output can rely exclusively on the column\u2019s metadata. This avoids long term SCHema Modification Locks required by ALTER TABLE that prevent other people from accessing the data. <\/p>\n
So seems like we\u2019ve solved the mystery of the missing default value, but wait… where is record 10?<\/p>\n
That’s another story my friends but don’t worry, one of these days I will tell you about the \u00abMystery of the 10th record\u00bb.<\/p>\n
Hope you have enjoyed the reading, just to finish I want to put together all references and concepts involved in this article.<\/p>\n
\u2022ALTER TABLE ADD COLUMN and Default constraints<\/a>
\n\u2022Locking and blocking<\/a>
\n\u2022DBCC IND and DBCC PAGE<\/a>
\n\u2022Database structures, row structure<\/a>
\n\u2022sys.fn_PhysLocFormatter<\/a>
\n\u2022sp_whoIsActive<\/a><\/p>\n
Just to say thanks to my friend and reviewer @jonwolds<\/a> for his help.<\/p>\n","protected":false},"excerpt":{"rendered":"
Sometimes things are not exactly how we think they are. Read the story of the missing default value and learn why. *First published @ www.SQLServerCentral.com (2015\/12\/01) Once upon a time, there was a…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[5,12,25],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/154"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=154"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/154\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}