Sunday, May 29, 2016

SQL-Variant Data Type in SQL Servers

Introduced in SQL Server 2008 and now available in all recent versions including Azure SQL Database; sql_variant is a data type that allows a column to have other data type elements. A single column can store int,char and binary data types. It can be used not only for columns but also for parameters, variables and return values of user defined functions.

These are some of the details you should know about:
sql_variant can have a maximum length of 8016 bytes.
sql_variant data type must first be cast to its base data type before participating in operations
sql_variant can be assigned a default value. This data type can have Null but they have no associated data type.
sql_variant cannot have sql_variant

Here is an example of a query run on a table that has a column (2nd) with sql_variant data type: