SQL SERVER – Identify Datatype Using SQL_VARIANT_PROPERTY Function

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.

“Hi Pinal

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

datatype
-------
int

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

datatype
-------
numeric

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.