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.
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
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
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