Basic Logics · Did you Know! · Play in SQL

Which Data Types has Non Nullable Default Values

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 Synonyms. Instead 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 and Show your Support for many more interesting upcoming Posts!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s