I'll keep this short and sweet. I am trying to add a column of type rowversion
to an existing table. My thought was that by adding it as NULL
, existing rows wouldn't be stamped with a timestamp, but alas they were. If that is the behavior, in what circumstances will the column ever admit a null value?
3条答案
按热度按时间u4vypkhs1#
if they give you the option of making it a nullable type, what is the functional difference between nullable vs non-nullable
In practice there is no functional difference (but there could be storage difference, see below). You can't insert
NULL
into arowversion
column.Even if you specify
NULL
for therowversion
column in theINSERT
statement, the server will insert the generated non-null value. And it will work like this regardless of how you declared therowversion
column (NULL
orNOT NULL
).The docs mention nullable
rowversion
only once:A nonnullable
rowversion
column is semantically equivalent to abinary(8)
column. A nullablerowversion
column is semantically equivalent to avarbinary(8)
column.If you specify
rowversion
nullable it should occupy more space on disk to allow storage of the possibleNULL
values (which in practice can't happen). Each nullable column incurs an overhead, see: How much size "Null" value takes in SQL ServerIn addition to the space required to store a null value there is also an overhead for having a nullable column.
Besides,
varbinary(8)
takes more space on disk thanbinary(8)
to store the length of value.Having said all this, I tried to create two tables with 10M rows each. One with nullable
rowversion
column, second with non-nullablerowversion
column. In my test both tables occupied exactly the same amount of disk space. I tested on SQL Server 2014 Express.5lhxktic2#
Even if you set it as NULL, rowversion take a value:
Check it here: http://rextester.com/ENELE48783
zc0qhyus3#
If that is the behavior, in what circumstances will the column ever admit a null value?
You can create a NULL
rowversion
in an expression or variable and persist that to a table usingSELECT INTO
:db<>fiddle
Using SSMS 19, you can display the NULL
rowversion
directly.https://onecompiler.com/sqlserver/3z5qtqzn9 (does show NULL)
My thought was that by adding it as NULL, existing rows wouldn't be stamped with a timestamp, but alas they were.
In SQL Server 2022, existing rows will contain a NULL
rowversion
after:This behaviour (a metadata-only operation) hasn't been officially documented yet and is currently enabled only through an undocumented trace flag.