A SQL _VARIANT Use Case (No Pun Intended)
I was recently at SQL Bits XII and I attended Itzik Ben-Gan's session, "T-SQL Tips and Tricks." I arrived late, but within about three minutes, I was quickly reminded that no matter how advanced you are in your career, there is always more to learn from fellow members in our great #SQLFamily.
Itzik was talking about conditional
ORDER BY (something I have talked about over on SQLPerformance.com), and how it can be problematic because of different data types a user might choose to sort by. So you can't just say:
ORDER BY CASE @OrderBy WHEN N'DateColumn' THEN DateColumn WHEN N'IntColumn' THEN IntColumn WHEN N'StringColumn' THEN StringColumn END
The problem is that
CASE is an expression that returns a single data type; all branches in the case must be compatible with the type of highest precedence (in this case
date). No matter what order you assemble the above expression, you'll get this error:
Operand type clash: int is incompatible with date
Typical solutions include explicit conversions to a base type (e.g. converting all values to strings), branching the case out exponentially (one expression for each set of compatible types), and using dynamic SQL. A lot of these solutions are prone to various plan-related problems, such as plan cache bloat and parameter sniffing, which in turn have their own set of solutions. This can become quite complex, and the path you go down depends on many factors.
One workaround that hadn't crossed my mind before attending Itzik's session is to add an expression that can accommodate *all* data types:
SQL_VARIANT. So the above becomes:
ORDER BY CASE @OrderBy WHEN N'DateColumn' THEN DateColumn WHEN N'IntColumn' THEN IntColumn WHEN N'StringColumn' THEN StringColumn ELSE CONVERT(sql_variant, NULL) END
Now the expression works, and each data type is implicitly converted to the one with the highest data type precedence. Whether this optimizes well in your scenario, I can't be sure; at the very least you'll *probably* want
OPTION (RECOMPILE) on there in the simplest cases. I'm only presenting you the option, not assuring you that this is a wise thing to do performance-wise. :-) SQL Sentry Plan Explorer tries to make it obvious why there might be an issue here, even in the case of an empty table:
In spite of that, this is the first use case for
SQL_VARIANT that I would actually consider using (though for a variety of reasons I still think I'd prefer dynamic SQL to solve this specific problem in my own code). I've blogged about my issues with
SQL_VARIANT before, and if you are using this type for other reasons (such as EAV) you should also see this post by Jonathan Kehayias and this post by Bob Ward.
Anyway, the point of this post was not to get you to use
SQL_VARIANT to "fix" your
ORDER BY clauses, but rather to illustrate how helpful #SQLFamily can be to each other - often without even knowing it. It's certainly not a resource I take for granted, and you shouldn't either.
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.