November 30, 2015 at 11:49 pm
Comments posted to this topic are about the item The Mysterious Case of the Missing Default Value
December 1, 2015 at 3:00 am
It's articles like this when you realise how much effort and smart the MS-SQL development team put in. I don't remember seeing this change in any headline features (which often from my point of view are often worthless) but it's a small, but very useful change that any DBA responsible for a reasonable size database would appreciate.
December 1, 2015 at 3:32 am
Presumably this has a knock on effect on the size of the table. What about indexes, would the value be added to an index or would that use the meta information as well?
December 1, 2015 at 4:00 am
It's interesting but tricky. For SQL 2012. What if we drop Default constraint afterwards? It should not "delete" existing data, right? I mean since SQL server started substitution of data from Default value for empty column, it should preserve this effect even after Default constraint removal. Otherwise "data" will be cleared, which is not right, it's not expected behaviour. We should not loose any data just dropping constraint. does it mean SQL physically populates data into pages when we drop Default constraint?
In other words:
1. Create new not null column with Default.
2. Select - "data" is there.
3. Drop default constraint.
4. Select - data must be there just the way it was up to SQL 2008.
In one of these steps the actual physical work needs to be done.
December 1, 2015 at 4:12 am
Yup, it's one of the best almost unnoticed improvements in 2012.
December 1, 2015 at 4:24 am
n.ryan (12/1/2015)
It's articles like this when you realise how much effort and smart the MS-SQL development team put in. I don't remember seeing this change in any headline features (which often from my point of view are often worthless) but it's a small, but very useful change that any DBA responsible for a reasonable size database would appreciate.
Since the index has to be created after the column, the index rows will contain the actual value and not rely on the metadata
You can try to create an index on c4 (stored in page) and c5 (just metadata) and compare the index rows, you need to modify the script to make it work for you.
CREATE INDEX IX_4 ON dbo.MissingDefaultValues(c4)
DBCC IND('testdb', 'MissingDefaultValues', 2)
DBCC TRACEON(3604)
DBCC PAGE(9, 1, 70496, 3)
CREATE INDEX IX_5 ON dbo.MissingDefaultValues(c5)
DBCC IND('testdb', 'MissingDefaultValues', 3)
DBCC TRACEON(3604)
DBCC PAGE(9, 1, 70456, 3)
On both cases they look like
Index on c5
Slot 0 Offset 0x60 Length 16
Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 16
Memory Dump @0x000000004F7BA060
0000000000000000: 16050000 00000f01 00010000 00020000 ................
Index on c4
Slot 0 Offset 0x60 Length 16
Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 16
Memory Dump @0x000000004A13A060
0000000000000000: 16040000 00[/b]140f01 0001000e 00020000 ................
You can see that either 4 and 5 are physically there, but that was a good point.
Cheers!
December 1, 2015 at 4:36 am
kosha5 (12/1/2015)
It's interesting but tricky. For SQL 2012. What if we drop Default constraint afterwards? It should not "delete" existing data, right? I mean since SQL server started substitution of data from Default value for empty column, it should preserve this effect even after Default constraint removal. Otherwise "data" will be cleared, which is not right, it's not expected behaviour. We should not loose any data just dropping constraint. does it mean SQL physically populates data into pages when we drop Default constraint?In other words:
1. Create new not null column with Default.
2. Select - "data" is there.
3. Drop default constraint.
4. Select - data must be there just the way it was up to SQL 2008.
In one of these steps the actual physical work needs to be done.
Very interesting point of yours!
I just tested it and if you drop the constraint, all rows that were in the table when you created it (the constraint) retain their values, but the pages still do not store them (physically are not populated), but in a SELECT * will come up.
The reason for this behaviour (when there is "no" metadata) is out of my knowledge, but I'll try to find out!
Cheers!
December 1, 2015 at 6:30 am
Well that was a fun and informative article to read. Thanks.
December 1, 2015 at 6:40 am
It's interesting behaviour. And if we had to guess, we'd probably think SQL creates internally metadata substitution (Default Substitution), when we create Default Constraint. But this Default Substitution is not bound to Default Constraint anymore and acts independently and stays in place until column itself is dropped or all rows are updated explicitly. So the purpose of Default Constraint - supply values for new rows. And purpose of Default Substitution - supply values for rows where data is absent.
Funny.
I tried to confuse SQL and dropped initial Default Constraint, then created another one with different default value. But it still selects values taken from original Default, as if they all were populated into rows and sit there. So it seems that this new kind of object (Default Substitution) is created only during column creation process when it's NOT NULL and Default Constraint is supplied at the same time. And this Default Substitution stays in place (forever) to represent data that is implied to be in pages.
It's really something new. Thank you for revealing.
Actually it recollects calculated fields of the kind ISNULL(ColumnValue, OriginalDefaultValue).
December 1, 2015 at 7:14 am
Very informative article, well done! Being somewhat of a newbie to much of this, my only question is around the update process. So the data gets returned with any queries etc. to that table but when another record at some future point is updated, that's when sql server will physically update the new constraint value on that row? For a bit flag, that overhead should be minimal at that point in time, but I wonder if there would be any performance hit if lets say you had a larger data type (take your pick) that had a default constraint?
December 1, 2015 at 7:23 am
rcerney 63319 (12/1/2015)
Very informative article, well done! Being somewhat of a newbie to much of this, my only question is around the update process. So the data gets returned with any queries etc. to that table but when another record at some future point is updated, that's when sql server will physically update the new constraint value on that row? For a bit flag, that overhead should be minimal at that point in time, but I wonder if there would be any performance hit if lets say you had a larger data type (take your pick) that had a default constraint?
My guess is that yes, to get any job done SQL Server will incurr in some overhead, but, what kind of big values would anyone want by default?
I can't figure out when you might want to get for example a 8000 chars default value... but it's a fair point.
Cheers!
December 1, 2015 at 7:24 am
rcerney 63319 (12/1/2015)
Very informative article, well done! Being somewhat of a newbie to much of this, my only question is around the update process. So the data gets returned with any queries etc. to that table but when another record at some future point is updated, that's when sql server will physically update the new constraint value on that row? For a bit flag, that overhead should be minimal at that point in time, but I wonder if there would be any performance hit if lets say you had a larger data type (take your pick) that had a default constraint?
Let me reply. Default Constraint is created for a Column, not a Row. It supplies value when you don't specify it explicitly during insert operation. After you created new column not null with default constraint - you may think that this column gets updated for all rows in the table. When you update single row subsequently - it simply gets and stores new value, no problems with that. The rest of the rows continue keeping their values naturally - doesn't matter where they initially got those values.
Sorry if I misunderstood your "physically update the new constraint value on that row".
Or maybe your concern is that data will grow in size when we explicitly update table? Ok, it should grow.
December 1, 2015 at 7:24 am
kosha5 (12/1/2015)
It's interesting behaviour. And if we had to guess, we'd probably think SQL creates internally metadata substitution (Default Substitution), when we create Default Constraint. But this Default Substitution is not bound to Default Constraint anymore and acts independently and stays in place until column itself is dropped or all rows are updated explicitly. So the purpose of Default Constraint - supply values for new rows. And purpose of Default Substitution - supply values for rows where data is absent.Funny.
I tried to confuse SQL and dropped initial Default Constraint, then created another one with different default value. But it still selects values taken from original Default, as if they all were populated into rows and sit there. So it seems that this new kind of object (Default Substitution) is created only during column creation process when it's NOT NULL and Default Constraint is supplied at the same time. And this Default Substitution stays in place (forever) to represent data that is implied to be in pages.
It's really something new. Thank you for revealing.
Actually it recollects calculated fields of the kind ISNULL(ColumnValue, OriginalDefaultValue).
The SQL engine is full of surprises :hehe::hehe:
December 1, 2015 at 7:56 am
Kosha5,
I do understand that the constraint is on the column not the row, but what I was wondering is if I have four columns, c1,c2,c3, and c4 and then I add c5 as lets say as a nvarchar(25) field with a default constraint of 'This is an example', in the article, that default constraint doesn't populate in the metadata until I would go and update a column for row. So example, I want to update the top 10 rows and set c1 = 1. At that point, does the engine then update c5 with the value 'This is an example' so now it is stored in all of the metadata as well? If so, my only question really was that yeah, who would do such a thing to have a large default constraint, but maybe it's a default constraint using the newid() to generate a guid...so does that incur any additional overhead since not only is the value for c1 being updated, but also c5?
December 1, 2015 at 8:02 am
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply