Sysname datatype and NULL column constraint

Sysname is a system supplied user defined datatype that is functionally equivalent to NVARCHAR(128). The unicode data type may be used to reference database object names.  The maximum length of the Sql Server object names is 128 unicode characters with an exception of the local temp tables*

*The maximum length of a local temporary table name is 116(N’#’+115) unicode characters. The “missing” 12 characters belongs to the hexadecimal  value appended to the name. The value makes the temp table names unique across an Sql Server instance.

When we create a table, by default, the columns can store NULL values.
Dynamic query below creates a table with 34 columns. Each column is of a different Sql Server data type.

USE tempdb
GO

DROP TABLE IF EXISTS dbo.DefaultColumnConstraints
GO
                                      
 --craete dynamic sql 
 DECLARE @tsql NVARCHAR(MAX);

 SET @tsql = N'CREATE TABLE dbo.DefaultColumnConstraints(';

 SELECT @tsql += CHAR(13) + CHAR(9) + N'COL' + 
                 CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(2)) + N'_'+[name] +
                 CHAR(9)+ [name] + N',' 
 FROM sys.systypes 

 SET @tsql = STUFF(@tsql,LEN(@tsql),1,'') + N')' --remove the last ','
 
PRINT @tsql
EXEC( @tsql)

Figure 1, Create table script

We didn’t explicitly specified the NOT NULL column constraints. This means, by default, that it’s possible to store NULL values in the columns (the columns are nullable). However, column COL26_sysname  refuses to follow the rule 🙂

 INSERT INTO dbo.DefaultColumnConstraints 
    DEFAULT VALUES;

Msg 515, Level 16, State 2, Line 108
Cannot insert the value NULL into column ‘COL26_sysname’, table ‘tempdb.dbo.DefaultColumnConstraints’; column does not allow nulls. INSERT fails.
The statement has been terminated.

A Column of a SYSNAME data type is not nullable by default. To allow NULL values to be stored in the column, we need to explicitly specify the nullability of the column.

 ALTER TABLE dbo.DefaultColumnConstraints
    ALTER COLUMN COL26_sysname SYSNAME NULL;

After the change the insert query executes with no errors.

Fgure 2, Nullable Columns

Timestamp is another data type that does not allow NULL values by default.

The query below selects all non-nullable columns (by default).

    SELECT [name]
           ,is_nullable
           ,max_length
           ,collation_name
    FROM sys.types
    WHERE is_nullable = 0

Figure 3, Not nullable datatypes by default

Side note: 
TIMESTAMP is the data type synonym for the ROWVERSION datatype(Sql Server 2012+). A ROWVERSION is automatically generated, unique 8b binary number within a database. It is simply an incrementing number generally used to indicate a row version.

Columns of the ROWVERSION data type automatically assign a new, unique binary number. It is not possible to insert any value except NULL (which then gets replaced with a proper binary number). Column NULL  constraints does not work with the ROWVERSION data type.

USE tempdb
GO

DROP TABLE IF EXISTS dbo.TestRowversionDataType
GO
    CREATE TABLE dbo.TestRowversionDataType(
        RowId TIMESTAMP NULL --ROWVERSION
)
GO

--fails
INSERT INTO dbo.TestRowversionDataType(RowId)
    SELECT 0x0000000000000111
/*
Msg 273, Level 16, State 1, Line 126
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
*/
GO
--success (a new rowversion value is added)
INSERT INTO dbo.TestRowversionDataType(RowId)
    SELECT NULL;
GO
--success (a new rowversion value is added)
INSERT INTO dbo.TestRowversionDataType(RowId)
    DEFAULT VALUES;
GO

SELECT * FROM dbo.TestRowversionDataType

Figure 4, Rowversion data type

A question for the audience would be; why rowversion is the only Sql Server system data type that is represented with its synonym, timestamp, in sequel’s metadata

SELECT *
FROM sys.types
WHERE [name] IN ('rowversion','timestamp')

FIgure 5, timestamp vs rowversion

Conclusion

By default, all Sql Server system data types are nullable when assigned to the table columns – except sysname datatype. In case of SYSNAME, the default behavior does not allow NULL values. It is a good practice to explicitly include NULL constraints (nullability) when creating a table.

Thanks for reading.