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:

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:

Warnings shown in Plan Explorer for implicit conversions to SQL_VARIANT

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 Bertrand

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.

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.