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

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

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:

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:

Value         DataType       NumericPrecision   NumericScale
------------- -------------- ------------------ -------------
8.350000      numeric        23                 6

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

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:

Value         DataType       NumericPrecision   NumericScale
------------- -------------- ------------------ --------------
NULL          NULL           NULL               NULL

Even if you cast the NULL value

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.

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

--expression to check the resultant datatype of, Can't reference
--variables, but can be any valid expression
:setvar expressionToTest "case when invoiceId = 1 then 2.0 else 1 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);

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):

EvaluatedExpressionValue   DataType        NumericPrecision                
19.166666                       numeric         22                      

… NumericScale  MaxLength
… 6             NULL

… StringCollation       TotalBytesOfStorageInclOverhead --      UnitPrice       InvoiceId
… NULL                  9                               --      230.00          1

Hope you find this useful.  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 I wasn’t.

 

The post Technique and Simple Utility to Determine the Datatype of a Scalar T-SQL Expression appeared first on Simple Talk.

from Simple Talk https://ift.tt/2RKpAZn
via