Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Overview of SQL Server sql_variant Data Type


By:   |   Last Updated: 2016-11-02   |   Comments   |   Related Tips: More > Data Types

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.

Addition of sql_variant variables error message.

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

Proper addition of sql_variant variables.

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.

Sample table creation with warning.

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.

Error when inserting more than 900 bytes on indexed column.

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.

Base Data Type

Data Type Family

sql_variant

sql_variant

datetime2

Date and time

datetimeoffset

datetime

smalldatetime

date

time

float

Approximate numeric

real

decimal

Exact numeric

money

smallmoney

bigint

int

smallint

tinyint

bit

nvarchar

Unicode

nchar

varchar

char

varbinary

Binary

binary

uniqueidentifier

Uniqueidentifier

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.

Value

Description

BaseType

SQL Server data type.

Precision

Number of digits of the numeric base data type.

 

Scale

Number of digits to the right of the decimal point of the numeric base data type.

TotalBytes

Number of bytes required to hold both the metadata and data of the value.

Collation

Represents the collation of the particular sql_variant value.

MaxLength

Maximum data type length, in bytes. For example, MaxLength of nvarchar(50) is 100, MaxLength of int is 4, MaxLength of char(50) is 50.

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.

Screen capture of SQL_VARIANT_PROPERTY function test.
Next Steps


Last Updated: 2016-11-02


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools