Technique and Simple Utility to Determine the Datatype of a Scalar T-SQL Expression

Note: post revised to take into consideration tips from comments

The other day, I was working with a query where someone had put together an expression that had something along the lines of:

And I thought to myself… “Self, what will the datatype of this be? Will it fit what the programmer wanted it to be? Is it weird I addressed myself as self?”  Instinctively, I wanted to believe it would be a numeric(18,2) because 12.0 should be less precise than the 18,2 numeric… But I had to know. (If you try this yourself, see what happens as you add zeros to the end, like 12.0000!) So, I went and coded a few statements using a sql_variant datatype to hold the expression value and SQL_VARIANT_PROPERTY to interrogate its properties:

Since X <> Y, this returns the 100.20 / 12.0 value, which is output into a numeric(23,6). Interesting:

Now, what would the value of NULL be output into?

This just returns NULLs when evaluating the sql_variant value:

Even if you cast the NULL value

The sql_variant with the NULL output will not register a type, which makes some sense, as a NULL is generally typeless on its own, without a type coming from the container, like a variable or column. If you have ever worked with SSIS, you know that the container of the output will have a datatype, which (as was taught me by Phil Factor in the comments!) can be fetched using the system function sys.dm_exec_describe_first_result_set, by taking your expression and making it a properly delimited string, thusly:

This tells us that the datatype is numeric(23,6), exactly as we have seen earlier.

Now, I wanted to build something to make this easy the next time. So, the following utility is a simple T-SQL batch, which uses SQLCMD variables in Management Studio, will give me the characteristics of an expression from a table expression in context of the table. Thanks to Phil Factor, we can see not only the results of the expression, and the values of additional columns, but we will also get the datatype of the other columns in the output as well.

Basically, put this code in SSMS, turn on SQLCMD mode, and fill in the blanks with the bits of code in the variables (they should be self-explanatory with the comments/example), and you will see the datatype of your expression (note that since the technique requires the ability to create a stored procedure, I left in the SQL_VARIANT_PROPERTY output if you do not have rights to create a procedure (I used a procedure so the setvar values could be basic query text with no need to double up on single quotes, which can get extremely tedious for larger queries/expressions). 

Now, we can see that the expression “CASE WHEN UnitPrice < 10 THEN NULL ELSE CAST(UnitPrice AS numeric(18,2)) / 12 END” will return an expression of type numeric(22,6), along with the fact that the UnitPrice column is a decimal(18,2):

Hope you find this useful, or at least find it informative that you can use SQL_VARIANT_PROPERTY and the sys.dm_exec_describe_first_result_set objects to get more information about the datatype of expressions you are using in your queries. And if you have any sort of math background, you may be slightly surprised by the datatypes you find in the output.

Also, if you were wondering, the expression N’Merry Christmas! Happy Holidays!’ is nvarchar(32), and it requires 72 bytes to store. If you were wondering, which you probably weren’t.