SQL Server Adding a nullable rowversion column to a table

lymgl2op  于 2023-04-19  发布在  其他
关注(0)|答案(3)|浏览(118)

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?

u4vypkhs

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 a rowversion column.

Even if you specify NULL for the rowversion column in the INSERT statement, the server will insert the generated non-null value. And it will work like this regardless of how you declared the rowversion column ( NULL or NOT NULL ).

The docs mention nullable rowversion only once:
A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column.

If you specify rowversion nullable it should occupy more space on disk to allow storage of the possible NULL values (which in practice can't happen). Each nullable column incurs an overhead, see: How much size "Null" value takes in SQL Server

In 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 than binary(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-nullable rowversion column. In my test both tables occupied exactly the same amount of disk space. I tested on SQL Server 2014 Express.

5lhxktic

5lhxktic2#

Even if you set it as NULL, rowversion take a value:

CREATE TABLE MyTest (myKey int PRIMARY KEY, myValue int);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);  
GO  
SELECT * FROM MyTest;
GO
ALTER TABLE MyTest ADD rv rowversion NULL;
GO
SELECT * FROM MyTest;
GO
DROP TABLE MyTest;

+-------+---------+------------+
| myKey | myValue | rv         |
+-------+---------+------------+
| 1     | 0       | 0000020331 |
+-------+---------+------------+
| 2     | 0       | 0000020332 |
+-------+---------+------------+

Check it here: http://rextester.com/ENELE48783

zc0qhyus

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 using SELECT INTO :

SELECT rv = CAST(NULL AS rowversion) 
INTO dbo.T;

-- db<>fiddle doesn't display null rowversion correctly
SELECT rv = ISNULL(rv, 0xDEAD0000BEEF1111) 
FROM dbo.T AS T;

-- Show table has a nullable rowversion column
EXECUTE sys.sp_help @objname = N'dbo.T';
rv
0xDEAD0000BEEF1111

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:

ALTER TABLE dbo.TheTable
ADD COLUMN rv rowversion NULL;

This behaviour (a metadata-only operation) hasn't been officially documented yet and is currently enabled only through an undocumented trace flag.

相关问题