The sql_variant Datatype

By Bill Graziano on 18 February 2002 | Tags: Data Types

Microsoft introduced the sql_variant in SQL Server 2000. You can use the sql_variant to store data of an unspecified or inconsistant type. Or to store data of almost any SQL Server datatype.

A sql_variant can hold any datatype except text, ntext, image, and timestamp without conversion. For example, the following code will run cleanly:

Declare @variant1 sql_variant, @variant2 sql_variant
Declare @variant3 sql_variant, @variant4 sql_variant

Declare @MyInt as integer, @MyDatetime as Datetime
Declare @MyMoney as Money, @MyBit as bit

Set @MyInt = 37
Set @MyDatetime = '2/18/02'
Set @MyMoney = $37.23
Set @MyBit = 1

Select 	@variant1 = @MyInt,
	@variant2 = @MyDatetime,
	@variant3 = @MyMoney,
	@variant4 = @MyBit


Select 	@variant1 as [Int Variant],
	@variant2 as [Datetime Variant],
	@variant3 as [Money Variant],
	@variant4 as [Bit Variant]

and generate this result:

Int Variant     Datetime Variant         Money Variant  Bit Variant
--------------- ------------------------ -------------- -----------
37              2002-02-18 00:00:00.000  37.2300        1

The result is actually quite a bit wider. I'd suggest using the Results in Grid option of Query Analyzer to view the results of these queries. The sql_variant is actually storing the datatype in it's native format. We can use the SQL_VARIANT_PROPERTY function to see this:

Select	sql_variant_property(@variant1, 'BaseType'),
	sql_variant_property(@variant2, 'BaseType'),
	sql_variant_property(@variant3, 'BaseType'),
	sql_variant_property(@variant4, 'BaseType')

which returns

--------------- --------------- --------------- --------------- 
int             datetime        money           bit

You can also return the precision, scale, totalbytes, collation and maxlength attributes for a sql_variant using the sql_variant_property function.

Practical Applications

So what do we do with sql_variant? I've come up with two ways to use it so far. The first is to use it as a parameter in a stored procedure. You might code something like this:

Create Procedure spTestParm (@parm1 sql_variant)
AS
Select	@parm1, SQL_Variant_Property(@parm1, 'BaseType')
go

Once in the procedure you could use functions like IsNumeric or IsDate to test what type of parameter you had. And you could use the sql_variant_property function for more detailed information. This is very similar to passing in a parameter as a varchar. It's benefit is primarily one of precision. There won't be rounding errors when numeric or float values are passed in. Datetime values can be passed in without losing milliseconds in coversion. You can also use sql_variant's in variables, and return values of user-defined functions.

Another approach is a configuration table or user-defined field's table. Many applications give the user the ability to create user defined fields. In the past I've always built a table that had a field for int, a field for datetime, a field for char/varchar, etc. Then I only populated the field I needed. Now we can create a table like this:

Create Table UserFields (MyPK int NOT NULL, 
	DataField char(10) NOT NULL,
	DataValue sql_variant NULL)

Notice that we didn't have to define the size of the sql_variant. It's like varchar in that it will expand or shrink as necessary and it can hold up to 8016 bytes. We can populate it with the following insert statements:

Insert UserFields
Values (1, 'HireDate', convert(datetime, '6/20/00 17:32:19.567') )

Insert UserFields
Values (1, 'Discount', Convert(numeric(5,4), 0.2156 ) )

Notice how specific the datetime value is. Also notice that the values clause generated a datetime value and then a numeric value. We aren't inserting varchar values into the table. We can return these values using the folowing statements:

Declare @UserHireDate datetime, @UserDiscount numeric(5,4)

Select	@UserHireDate = Cast(DataValue as datetime)
From	UserFields
Where	MyPK = 1
AND	DataField = 'HireDate'

Select	@UserDiscount = Cast(DataValue as numeric(5,4) )
From	UserFields
Where	MyPK = 1
AND	DataField = 'Discount'

Select	Discount = @UserDiscount,
	HireDate = @UserHireDate

which will display

Discount HireDate                                               
-------- ------------------------------------------------------ 
.2156    2000-06-20 17:32:19.567

Notice that the same column in the table held different a different datatype for each row. You can also use sql_variants in keys and indexes although I wouldn't suggest it. ODBC doesn't support sql_variant properly and you can get binary results. That's really about it for sql_variant. Books Online has additional information about comparing sql_variants and using them in functions.