What is the datatype SQL_VARIANT

The SQL_VARIANT data type is an interesting beast. It is a data type that can store most types of data. So a date, decimal, int, varchar etc. can be stored in this single data type. This sounds great right? Well there are a few issues here.

What data type is being stored?

First of all, because you can have so many different types of data, you don’t necessarily know what type data you have in there. Fortunately there is an easy solution. You can use the function sql_variant_property. Like the other properties functions there are variety of properties you can look at but the one we need here is BaseType. Interestingly enough you can also use sql_variant_property to get information on most variables. (Not table variables for example.)

DECLARE @MyVariant sql_variant = 4;
SELECT sql_variant_property(@MyVariant ,'basetype');
-- int
DECLARE @MyInt INT = 4;
SELECT sql_variant_property(@MyInt,'basetype');
-- int

Implicit conversions

While you can still implicitly convert other data types into sql_variant

-- This works because while @MyVariant is a varchar initially the
-- int value of @MyInt gets implicitly converted to sql_variant
-- and overwrites not just the value but the current base type
-- of @MyVariant.
DECLARE @MyVariant sql_variant = 'test';
DECLARE @MyInt int = 4;
SET @MyVariant = @MyInt;

you can’t convert it the other way around.

-- And this fails because even though the base type of both
-- variables is varchar the actual data type of @MyVariant
-- is sql_variant which won't implicitly convert to any
-- other data type.
DECLARE @MyVariant sql_variant = 'test';
DECLARE @MyInt int = 4;
SET @MyInt = @MyVariant;

That means that if you are being at all careful then you need to explicitly convert your sql_variant variable, field, etc into whatever data type you are expecting. Which also means that (since you are being careful) you need to make use of try_convert or try_cast as well.

I should also point out that this can have some other interesting effects. For example the PRINT command expects a string (char, nchar, varchar, and nvarchar) so fails when it tries to implicitly convert the sql_variant.

DECLARE @MyVariant sql_variant = 'test';
PRINT @MyVariant;

Msg 257, Level 16, State 3, Line 2

Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.

And some things we don’t really expect to be implicit conversions really are.

DECLARE @MyVariant sql_variant = '01/01/2020 12:31 AM';
SELECT sql_variant_property (@MyVariant, 'basetype');
-- varchar

Summary

Regardless, like I said, sql_variant is a fun thing to play with, and even has it’s place in your code. It’s not going to be all that common though and when you use it you need to take the above into account.