What is the datatype SQL_VARIANT

3

April 16, 2020 by Kenneth Fisher

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.

3 thoughts on “What is the datatype SQL_VARIANT

  1. […] Kenneth Fisher explains what the SQL_VARIANT data type is used for: […]

  2. Joe Celko says:

    One of my favorite T-shirts reads “on a scale from 1 to 10, what color is your favorite letter of the alphabet?” The reason I like it is that It demonstrates what’s wrong with things like the SQL variant data type; it’s not really a data type.

    We designed SQL to be a strongly typed language, so that would follow the laws of logic and not be as ridiculous as that T-shirt. Essentially, it violates the Law of Identity (to be is to be something in particular, to be nothing in particular or anything in general, is to be nothing at all).

    Picking the correct data type or something is really important. Brent Ozar has a wonderful principle that you should store data the way it is used and use it the way it is stored.. But when you have no idea what the datatype is so you don’t know if it’s appropriate for the data element, you can have a valid data model. Is that column really a date? Is it a name on a nominal scale? Is it a numeric value on an absolute, ordinal, interval, or ratio scale? The answer is it everything in general and nothing in particular.

  3. […] (in this case CONVID-19) currently stored for this session. Be warned, SESSION_CONTEXT returns a sql_variant. Basically, like I said above, it’s a variable scoped for the session (i.e. it will last as […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,142 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: