One of my client of Comprehensive Database Performance Health Check sent me the following email about SQL_VARIANT_PROPERTY Function. I really love my job as SQL Server consultant as I get to learn most of the new stuff or discover the hidden knowledge nuggets which I have discovered. Today’s problem is one such problem which I had never come across till I was asked this question by my client.
I have the following select statement
SELECT 17*@amount/100 as percentage
How to find out datatype of the result?“
Well. There can be several methods to find out this. But one simplest method is to make use of SQL_VARIANT_PROPERTY system function
Let us consider the following simple example
DECLARE @AMOUNT INT SET @AMOUNT=2300 SELECT 17*@AMOUNT/100 AS PERCENTAGE
The result is 391 and it is an INTEGER
Use the above expression as the first parameter to SQL_VARIANT_PROPERTY function
DECLARE @AMOUNT INT SET @AMOUNT=2300 SELECT SQL_VARIANT_PROPERTY(17*@AMOUNT/100,'basetype') as datatype
The result is
Now see what happens when you use 17.0*@AMOUNT/100
DECLARE @AMOUNT INT SET @AMOUNT=2300 SELECT SQL_VARIANT_PROPERTY(17.0*@AMOUNT/100,'basetype') as datatype
The result is
It is because 17.0 is of numeric datatype and the end result will be converted to numeric as well.
Have any of you used SQL_VARIANT_PROPERTY Function before to identify data type? Please leave a comment and I will be happy to know your story. Earlier I had written on this subject over here: SQL SERVER – How to Identify Datatypes and Properties of Variable.
Reference: Pinal Dave (https://blog.sqlauthority.com)