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:
|
1 2 3 |
CASE WHEN Value = 'X' THEN NULL ELSE CAST(SomeOtherValue as numeric(18,2) / 12.0 END AS AnnualisedSomeOtherValue |
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:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @switch char(1) = 'Y'; DECLARE @value sql_variant = CASE WHEN @switch = 'X' THEN NULL ELSE CAST('100.20' AS numeric(18, 2)) / 12.0 END; SELECT @value, SQL_VARIANT_PROPERTY(@value, 'BaseType') AS DataType, SQL_VARIANT_PROPERTY(@value, 'Precision') AS NumericPrecision, SQL_VARIANT_PROPERTY(@value, 'Scale') AS NumericScale; |
Since X <> Y, this returns the 100.20 / 12.0 value, which is output into a numeric(23,6). Interesting:
|
1 2 3 |
Value DataType NumericPrecision NumericScale ------------- -------------- ------------------ ------------- 8.350000 numeric 23 6 |
Now, what would the value of NULL be output into?
|
1 2 3 4 |
DECLARE @switch char(1) = 'X'; DECLARE @value sql_variant = CASE WHEN @switch = 'X' THEN NULL ELSE CAST('100.20' AS numeric(18,2)) / 12.0 END |
This just returns NULLs when evaluating the sql_variant value:
|
1 2 3 |
Value DataType NumericPrecision NumericScale ------------- -------------- ------------------ -------------- NULL NULL NULL NULL |
Even if you cast the NULL value
|
1 2 3 4 5 |
DECLARE @switch char(1) = 'X'; DECLARE @value sql_variant = CASE WHEN @switch = 'X' THEN CAST(NULL as numeric(18,2)) ELSE CAST('100.20' AS numeric(18,2)) / 12.0 END |
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:
|
1 2 3 4 |
SELECT system_type_name FROM sys.dm_exec_describe_first_result_set(' DECLARE @switch char(1) = ''X''; SELECT CASE WHEN @switch = ''X'' THEN CAST(NULL as numeric(18,2)) ELSE CAST(''100.20'' AS numeric(18,2)) / 12.0 END', NULL, 1) |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
--expression to check the resultant datatype of, Can't reference --variables, but can be any valid expression :setvar expressionToTest "CASE WHEN UnitPrice < 10 THEN NULL ELSE CAST(UnitPrice AS numeric(18,2)) / 12 END" --comma delimited list of additional columns or expressions :setvar additionalColumns "UnitPrice,InvoiceId" --everything starting with FROM in a query. No need for anything --but a blank if the expression doesn't reference a table. (can be --multiline, will turn off syntax coloring in SSMS) :setvar expressionFROM "FROM Sales.InvoiceLines ORDER BY InvoiceLineId" --all rows will have the same datatype, but you may want to see --that for yourself :setvar numRowsToTest 1 SELECT TOP($(numRowsToTest)) $(expressionToTest) AS EvaluatedExpressionValue, SQL_VARIANT_PROPERTY(CAST($(expressionToTest) AS sql_variant), 'BaseType') AS DataType, --Numeric data SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'Precision') AS NumericPrecision, SQL_VARIANT_PROPERTY(CAST($(expressionToTest) AS sql_variant), 'Scale') AS NumericScale, --Note: Unicode sizes may vary now, and will change in 2019 --with UTF-8, but 2 bytes is generally true for most collations CASE WHEN SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'BaseType') IN ('nchar', 'nvarchar') THEN CAST(SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'MaxLength') AS int) / 2 WHEN SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'BaseType') IN ('varbinary', 'varchar', 'char' ) THEN CAST(SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'MaxLength') AS int) END AS MaxLength, SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'Collation') AS StringCollation, SQL_VARIANT_PROPERTY( CAST($(expressionToTest) AS sql_variant), 'TotalBytes') AS TotalBytesOfStorageInclOverhead, '--' AS [--], $(additionalColumns) $(expressionFROM); GO DROP PROCEDURE IF EXISTS dbo.[###DropMe###] --most heinous name possible GO CREATE PROCEDURE [###DropMe###] AS SELECT TOP($(numRowsToTest)) $(expressionToTest) AS EvaluatedExpressionValue, $(additionalColumns) $(expressionFROM); GO --get the system metadata for the columns... There is a LOT more detail in the result --set, if you want to know more about the output of columns/expressions SELECT name, dm_exec_describe_first_result_set.is_nullable, system_type_name FROM sys.dm_exec_describe_first_result_set('###DropMe###',NULL,1); GO --cleanup DROP PROCEDURE IF EXISTS dbo.[###DropMe###] |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
EvaluatedExpressionValue DataType NumericPrecision 19.166666 numeric 22 … NumericScale MaxLength … 6 NULL … StringCollation TotalBytesOfStorageInclOverhead -- UnitPrice InvoiceId … NULL 9 -- 230.00 1 name is_nullable system_type_name EvaluatedExpressionValue 1 numeric(22,6) UnitPrice 1 decimal(18,2) InvoiceId 0 int InvoiceLineID 0 int |
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.
Load comments