Adding a nullable rowversion column to a table

Question

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?

Carmelocarmen

The rowversion is just a system-internal, binary counter - what's the harm of having values in that column for existing rows? – Gwenora

From my tests it seems completely pointless to allow this, apart from compatibility with CREATE TABLE syntax, it's probably a legacy of sybase heritage. sqlfiddle.com/#!6/6ea10 – Veratrine

@Gwenora - I just came back to this question after a long time away. In re: harm, I was hoping that allowing the column to be nullable would allow a rowversion column to be added as a metadata-only operation. Instead, it's a size-of-data operation. So the harm is in locking the table while the add column is in progress. – Carmelocarmen

Answer 1

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.

Pelagias

I was afraid of this. What I was trying to do was use the behavior of SQL Server to add a nullable column as a metadata operation to add that column. Instead, it looks like regardless of the nullability of the column, it's still a "size of data" operation. Bummer. Thanks for looking! – Carmelocarmen

"varbinary(8) takes more space on disk than binary(8)" If you have a varbinary(8) but only insert a 1 byte binary value into it, how big is it? According to the docs it is the length of the value + 2 bytes, so it would be 3 bytes. A binary(8) is always 8 bytes. The other question you quoted also says "If the field is variable width the NULL value takes up no space", and then "It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns)." – Veratrine

If you're not able to insert a NULL, then a potential benefit for specifying a NULLABLE ROWVERSION might be so that when rowversion values smaller than 8 bytes are inserted, they use less space. If that's not the case, then it seems pointless and you should always use non-nullable, but I am not convinced. How did you compare disk size in the test? Maybe there is the page size the same but pages less filled, or something similar. – Veratrine

@Davos, if I'm not mistaken, rowversion value is always exactly 8 bytes. It can't be less than 8 bytes. That's why I said that varbinary(8) takes more space on disk than binary(8). I did my test a while ago and I don't remember the details. It would be great if you could do another test and show us its results. – Pelagias

If rowversion always inserts 8 bytes then that would make sense for the rowversion case. I will do the test and let you know, thanks. – Veratrine

I did a test sqlfiddle.com/#!6/6ea10 and it appears to be storing left padded with zeros , i.e. instead of starting at 0x01 it starts at 0x0000000000000001 and doesn't left-truncate the zeros. Odd design to allow variable width and then not use it. That statement in the docs about rowversion being semantically equivalent to varbinary/binary seems completely meaningless, and given the storage overhead, you should always specify NOT NULL. I also discovered you can't use convert(varchar(max),rowversion,1) to display it as hex, so there are a few differences in these datatypes. – Veratrine

Answer 2

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

Sisyphus

Yes. I said as much in the statement of the question. My question is: if they give you the option of making it a nullable type, what is the functional difference between nullable vs non-nullable? – Carmelocarmen

Answer 3

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.

Leavings

Thanks! The first case is interesting but doesn't fit my use case. The second case would be very useful to me in being able to add a rowversion column to an existing table with minimal locking/logging. – Carmelocarmen

I also had a recent thought of trying to partition switch between tables whose only difference was rowversion vs binary(8) on the relevant column thinking that the on-disk structure is the same between them (similar to how you can do that between columns with/without the identity property) and it went poorly. – Carmelocarmen