Overview of SQL Server sql_variant Data Type

Problem

You started a new job and you are assigned to maintain an existing database application. When you look at the SQL code, you find that the application uses the sql_variant data type in variables and table columns. What is this data type and when should it be used?

Solution

The sql_variant data type allows a table column or a variable to hold values of any data type with a maximum length of 8000 bytes plus 16 bytes that holds the data type information, but there are exceptions as noted below.  This allows you to have flexibility in the type of data that is stored.

Among the available data types that SQL Server offers, the sql_variant data type is the most relegated. Mostly because we were taught that when we design a database solution we should know at first what kind of data our application will handle in order to define a proper entity model. This lesson is also taught to programmers of different languages like Visual Basic and C.

The following list shows the data types not allowed for a sql_variant column or variable:

• varchar(max)
• varbinary(max)
• nvarchar(max)
• xml
• text
• ntext
• image
• rowversion (timestamp)
• geography
• hierarchyid
• geometry
• datetimeoffset
• User-defined types

Adding and Concatenating with the sql_variant Data Type

The sql_variant columns and variables can be part of arithmetic operations and string concatenation, but as with other programming languages you need to cast the variable or column to the proper data type. For example, you can have two sql_variant variables a and b with values 5 and 2; if you write a + b you need to specify if you want to sum 5 and 2 which will result in 7 or if you want to concatenate 5 and 2 that will result in 52.

Take a look at the following example.

DECLARE @chr SQL_VARIANT = '5' 
DECLARE @var INT  = 2
SELECT @chr + @var

If you execute the previous code you will receive an error message like on the next image.

Here is another example.

DECLARE @chr SQL_VARIANT = 5 
DECLARE @var SQL_VARIANT = 2
SELECT @chr + @var

The above gives us this error.

Msg 402, Level 16, State 1, Line 4
The data types sql_variant and sql_variant are incompatible in the add operator.

In order to avoid the error message you should cast the sql_variant variable to the desired  data type.

DECLARE @chr SQL_VARIANT = '5' 
DECLARE @var INT  = 2
SELECT CAST( @chr AS INT ) + @var

Another thing to note, variables and table columns of sql_variant data type can be created with a default value just like we do on variables and table columns of any other data type.

Creating an Index with sql_variant Data Type

A sql_variant column can be part of an index or constraint only if its total length is less than 900 bytes, which is the maximum length of an index. This means that if you insert a value with more than 900 bytes on an indexed sql_variant column, the insert operation will fail.

Let’s create a sample table to show the previously stated.

CREATE TABLE MyTable
    (
      ID SQL_VARIANT NOT NULL
                     PRIMARY KEY ,
      Item SQL_VARIANT NOT NULL
                       INDEX  ix ( Item )
    )

If you take a look at the image below you will see that we get a message warning us about the maximum length of the column data.

Let’s try to insert a row with more than 900 bytes on the primary key column.

INSERT INTO dbo.MyTable
        ( ID, Item )
SELECT REPLICATE('a', 1000  ),
'Sample'

As you can see on the next image the insert statement fails.

But if you decide to index a sql_variant column you should take into consideration that the values will be sorted according to the hierarchy rules of the data type.

Data Type Precedence

SQL Server data types are ruled by a hierarchy list grouped by a family that dictates how the comparison of different data types should be made. The following table shows the hierarchy ordered by precedence, with the sql_variant data type ranked the highest.

When two sql_variant values of different base data types are compared, if the data types are in different families the value whose data type family is higher in the hierarchy is considered the higher of the two values. On the other hand, if both sql_variant values share the same type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.

The SQL_VARIANT_PROPERTY Function

If you decide that you need to use sql_variant data type for a project, you need to know that the SQL_VARIANT_PROPERTY function will give you useful information. This function returns the data type information and properties for a given value.

SQL_VARIANT_PROPERTY ( expression , property )  

The function has two input parameters:

• Expression: it contains the value you want to get info on
• Property: it is the name of the property you want to be returned. The next table shows the possible values for this parameter and a brief description taken from msdn.

Something to note is that the expression can be of any data type and it’s not limited to sql_variant, this is because the sql_variant data type is in the top of the data type hierarchy list for conversion.

Here is an example.

DECLARE @CharVariable VARCHAR(500) = 'MSSQLTips.com'
DECLARE @DateVariable DATETIME = GETDATE()
SELECT  SQL_VARIANT_PROPERTY(@CharVariable, 'BaseType') ,
        SQL_VARIANT_PROPERTY(@DateVariable, 'BaseType')

As you can see on the next image we can use the SQL_VARIANT_PROPERTY function with other data types.

Next Steps

• Due to the fact that using sql_variant data type in tables is not one of the best choices, the following tip will aid you to review database design: Remodel Poorly Designed SQL Server Database Tables.
• If you need more information about Data Type Precedence check out Armando’s tip: SQL Server Data Type Precedence.
• Check out SQL Server Data Types Tips Category for more tips.