Which Data Types has Non Nullable Default Values

Written by

Before Answering for this question, we should know the Available Datatypes in SQL.

As we all know, the Default Values for most of the datatypes in SQL is NULL.

How we can prove -‘Default Values for most of the datatypes in SQL is NULL‘ ?

SQL Script

--Like the below method, we can test with other datatypes too
DECLARE @int INT,@varchar VARCHAR(10),@bit BIT,@decimal DECIMAL(5,2);
SELECT @int AS 'INT',@varchar AS 'VARCHAR',
@bit AS 'BIT',@decimal AS 'DECIMAL';

Image Pic

Result Set

Which Data Types has Non Nullable Default Values

The Answer is ….

  • Rowversion
  • Timestamp

SQL Script

	
DECLARE @rowversion ROWVERSION,@timestamp TIMESTAMP;
SELECT @rowversion AS 'ROWVERSION',@timestamp AS 'TIMESTAMP';

Image Pic

Non Nullable Default value having SQL Datatypes

NOTES

Timestamp is the synonym for the Rowversion data type and it is subject to the behavior of Data Type SynonymsInstead of NULL, Both of these data types gives Non Nullable default values like ‘0x‘ and it is the HexaDecimal Value.

As per Microsoft Confirmation on below note-
The Timestamp syntax is deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

This works fine in both Azure SQL DB and On-Premises. Unit tested as well.

Thanks for reading and Follow Blog!

Article Categories:
SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares