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?

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.

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