0

I read the document on "CREATE TABLE" at https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

It said

timestamp data types must be NOT NULL.

However, when I create a table, I can create a field with timestamp type and make it nullable. So, what is the problem?

Update

When using the following query:

USE MyDB6;

CREATE TABLE MyTable (Col1 timestamp NULL);

I expect an error saying the column Col1 cannot be NULL but nothing happens.

After creating the table, I run the following query:

USE MyDB6

SELECT COLUMNPROPERTY(OBJECT_ID('MyTable', 'U'), 'Col1', 'AllowsNull');

I expect the result is 0, but actually it is 1.

So, my question is, though the document has said "timestamp data types must be NOT NULL.", and in the real cases, this data type will also not be NULL, why the create table query does not prevent me from setting it to nullable and the system still save the column as nullable?

alancc
  • 487
  • 2
  • 24
  • 68
  • This was a driver bug exposed in SSMS before version 19 (the breaking change fix is in `Microsoft.Data.SqlClient`). A null `rowversion` is indeed possible see [Adding a nullable rowversion column to a table](https://stackoverflow.com/questions/42215930/adding-a-nullable-rowversion-column-to-a-table) – Paul White Apr 15 '23 at 11:29

1 Answers1

1

Like marc_s said in their comment, this datatype is handled internally and will never be null. Try the following:

declare @test timestamp = null -- ROWVERSION would be less confusing 

select @test

It does not return NULL

As to why you're allowed to set it to nullable; what would be won by creating this deviation from the standard? You cannot INSERT NULL into a TIMESTAMP/ROWVERSION column, you cannot UPDATE it at all. I imagine it is quite a lot of trouble to alter the CREATE syntax to make certain datatype not nullable; more trouble than its worth.

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48