No seriously, don’t use SQL_VARIANT for that

I’d been meaning to write this for a while

I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results.

When I see people using SQL_VARIANT, it’s often in dynamic SQL, when they don’t know what someone will pass in, or what they’ll compare it to. One issue with that is you’ll have to enclose most things in single quotes, in case a string or date is passed in. Ever try to hand SQL those without quotes? Bad news bears. You don’t get very far.

That can cause some issues with integers. Heck, even if you don’t quote them, you run into performance trouble.

Enjoy the free-of-charge code and picture show below.

No words

Right but bad

Typical implicit conversion

Wrong and bad

Binary

Let’s call the whole thing off

Convincing

This is a bad idea and you shouldn’t do it. If you use this in any of your code, you could have a whole lot of wrong results going back to users.

Thanks for reading!

Brent says: O_O

, , ,
Previous Post
Why Cost Threshold For Parallelism Shouldn’t Be Set To 5
Next Post
Indexed Views And Data Modifications

13 Comments. Leave new

  • SQL_VARIANT is wrong. And bad. There should be a new, stronger word for SQL_VARIANT. Like badwrong, or badong. Yes, SQL_VARIANT is badong. From this moment, I will stand for the opposite of SQL_VARIANT: gnodab.

    Reply
  • The query plan I can understand, because what looks like is happening is the IntegerColumn is having to be converted to SQL_VARIANT type before it can be compared, therefore causing the Index Scan instead of an Index Seek.

    I’m in total agreement with your warning… https://msdn.microsoft.com/en-us/library/ms173829.aspx shows that if you let SQL_VARIANT implicitly convert an integer type, the resulting data will be the exact numeric, but if you implicitly convert a char, the resulting data type is unicode. It seems that SQL_VARIANT is broken by design. 🙁

    Reply
  • Steven Howes
    March 16, 2017 9:58 am

    gnodab, that’s how I tune my guitar

    Reply
  • Alex Friedman
    March 20, 2017 11:48 am

    sql_variant ewwww

    Reply
  • I have a function that uses SQL_Variant, and this is called in over 1/3rd of my sprocs. Works great for its purpose.

    But I completely agree with your post, this is the only time I have found a use for it. The function converts the actual datatype being passed in to a properly delimited string value. Calling this function for each input parameter gives me the complete string that I then store in a table that records the overall performance of the call. We use this info in one way to quickly see what the end user actually passed in and to quickly be able to reproduce.

    Reply
  • Aaron N Cutshall
    December 8, 2017 10:18 am

    I have a situation where I will need to store various data items in a single column. The method currently employed is to convert everything to nvarchar(max) then perform explicit conversions. However, I’d like to put an index on the column so that numerics sort numerically and not alphanumerically so I’m considering using a data type of sql_variant. I’ll still need to keep the nvarchar(max) column for data that can’t be loaded in the sql_variant column and to handle the LIKE operations. I also considered creating some base columns such as a numeric value, a datetime value, etc., but the problem is determining a query time what column to base the comparison upon. I’d appreciate some feedback on this.

    Reply
  • Your example shows a failure on doing a CAST or CONVERT prior to a comparison operation. From my understanding, SQL_VARIANT does not implicitly return its data type; it only supports implicit assignment from other data types. So, this is like working with a loosely-typed variable (like Javascript, PHP, etc.). You can’t run operations on a binary variable and expect to get a accurate result without anticipating the operation and validating accordingly (yeah, I know this was just a quick example, but still…). In this case, it’s not a failure on the use of the SQL_VARIANT data type, but more so on the failure of SQL Server to implicitly convert the data prior to the comparison operation. A simple “IntegerColumn >= CAST(@bad AS INT)” would have worked fine.

    So, SQL_VARIANT is not badong (indexing performance aside); running operations on loosely-typed variables and relying on implicit conversion is badong.

    Reply
  • Hey Brent,

    I know this is an older post and get the inaccuracies would never ever…. BUT what about handling explicit conversions in a row security policy function that compares a string to a session_context variable that is a variant?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}