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
|
1 2 3 4 5 6 7 8 9 10 11 |
USE tempdb CREATE TABLE #t (IntegerColumn INT NOT NULL PRIMARY KEY CLUSTERED); INSERT #t ( IntegerColumn ) SELECT x.n FROM ( SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.messages AS m CROSS JOIN sys.messages AS m2 ) x (n) |
Right but bad
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @good INT = 1 DECLARE @bad SQL_VARIANT = 1 SELECT * FROM #t WHERE IntegerColumn = @good ORDER BY IntegerColumn SELECT * FROM #t WHERE IntegerColumn = @bad ORDER BY IntegerColumn GO |
Wrong and bad
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
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.
Bolbi Stroganovsky IS THAT YOU?
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. 🙁
gnodab, that’s how I tune my guitar
sql_variant ewwww
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.
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.
Your best bet there is to head to https://dba.stackexchange.com.
I wonder if graph could be used for this case. I am not 100% certain Microsoft Graph Database ( SQL Server 2017 ) supports it, but if it does, it would be very nice.
The OrientDB certainly does. You could use an edge to identify particular class of records and index over an edge. https://github.com/orientechnologies/orientdb. I will try to follow up on this question, if this is still of interest.
At any rate, Graph introduces to databases true polymorphism, where you could use data annotations for alternate access path and indexing.
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.
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?
Hi Frank,
Not sure. What happens when you try it?
Thanks!
Howdy sir! I’m not a big fan of using session context for security since folks can set it whenever they want manually.