Does SQL Server's is_nullable mean anything at all?

Question

SQL Server has a type flag called is_nullable you can see it on sys.types. Currently, (SQL Server 2019) it's only set to FALSE two types, sys.timestamp and sys.sysname. On sys.timestamp this type seems to accept null. On sys.sysname it does not. Is there any explanation of this behavior?

You can see these types with

SELECT * FROM sys.types WHERE is_nullable = 0;  
SELECT TYPEPROPERTY('timestamp', 'AllowsNull');  -- returns 0

And you can use it like this,

CREATE TABLE foo ( a sys.timestamp );
INSERT INTO foo (a) VALUES ( null );

Note this won't work if you use sys.sysname

Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'a', table 'dbo.foo'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

It also will not work if you create a user-defined data type which is NOT NULL,

CREATE TYPE bar FROM int NOT NULL;

Is this just a hint for the user?

Answer 1

The answer to your question is yes.

That column specifies the default nullability of a data type, which can be important if the user doesn't express a preference e.g. in a CREATE TABLE statement with an explicit NULL or NOT NULL specification. Sadly, that information is only present in the documentation for the old compatibility view sys.systypes:

allownulls (bit)

Indicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE or ALTER TABLE.|

For example, one can override the default NOT NULL behaviour of the sysname type:

DECLARE @T table (s sysname NULL);
INSERT @T (s) VALUES (NULL);
SELECT s FROM @T AS T;

s
NULL

The timestamp type (preferred name rowversion) has additional special behaviours due to its intended use to detect row changes. See this other answer for some more details. You can still override its nullability in the same way, but it is not possible to actually store a NULL for a timestamp/rowversion attribute.

There are several complex layers to determining attribute nullability. Rather than repeat some of that detail here, I refer you to Why you Should Always Specify Whether a Column Accepts Nulls by Phil Factor for background information and additional reading.

Answer 2

Let me rephrase the question, to make sure we are on the same page:

Meta-data for the timestamp type say that it doesn't accept NULL, but if you create a table without specifying nullability you can specify NULL for that column on INSERT. Why?

If you SELECT from the table, you will see that the value isn't null after the INSERT. You have the generated "timestamp" (or rowversion, if you prefer that name). I.e., NULL on INSERT is meaningless, it means that the "default" (so to speak) is used.

One can go into a discussion whether this meta-data property for the timestamp type should reflect whether we can specify null on INSERT or whether we can in the end actually have NULL in that column.

I guess we would have to been present when Sybase designed the system tables some decades ago to be able to know that answer. I wasn't. :-)

So, in short: the column of a timestamp type doesn't allow null in the table, and based on that one can argue that the meta-data is correct.

(As an aside, I first though this was a backward compatibility thing, so I fired up a 6.5 version of SQL Server to test on. And I almost fired up a version 4.2 as well. But then I did a SELECT and it wasn't until then that I noticed that the value wasn't NULL at all.)

Here's the T-SQL I played with (on 2019), FWIW:

USE tempdb

DROP TABLE IF EXISTS myTable;
DROP TYPE IF EXISTS myTypeYesNull;
DROP TYPE IF EXISTS myTypeNoNull;

CREATE TYPE myTypeYesNull FROM int NULL;
CREATE TYPE myTypeNoNull FROM int NOT NULL;
GO

CREATE TABLE myTable (yesNull myTypeYesNull, noNull myTypeNoNull, ts timestamp, sname sysname);
GO

SELECT name, is_nullable FROM sys.types WHERE name IN ('myTypeYesNull', 'myTypeNoNull', 'timestamp', 'sysname')

SELECT 
 COLUMNPROPERTY(OBJECT_ID('myTable'), 'yesNull', 'AllowsNull') AS yesNull
,COLUMNPROPERTY(OBJECT_ID('myTable'), 'noNull', 'AllowsNull') AS noNull
,COLUMNPROPERTY(OBJECT_ID('myTable'), 'ts', 'AllowsNull') AS ts
,COLUMNPROPERTY(OBJECT_ID('myTable'), 'sname', 'AllowsNull') AS sname;

--The ts column accepts NULL even though table meta-data states differently
INSERT INTO myTable (yesNull, noNull, ts, sname)
VALUES(NULL, 1, NULL, 'x')

SELECT * FROM myTable

----------------------------------------------------------------------

--Code executable on really old versions
DROP TABLE myTable 
GO
SELECT name, allownulls FROM systypes WHERE name = 'sysname'
CREATE TABLE myTable(ts timestamp NULL)
GO

--Doesn't fail on 6.5
INSERT INTO myTable(ts) VALUES(null)
SELECT * FROM myTable