As a SQL Server consultant, I often come across interesting problems and discover hidden knowledge nuggets. Recently, one of my clients asked me about the SQL_VARIANT_PROPERTY function and how it can be used to determine the data type of a result in SQL Server.

Let’s consider a scenario where we have a select statement:

SELECT 17*@amount/100 as percentage

The client wanted to know how to find out the data type of the result. There are several methods to achieve this, but one of the simplest methods is to use the SQL_VARIANT_PROPERTY system function.

Here’s an example:

DECLARE @AMOUNT INT
SET @AMOUNT=2300
SELECT 17*@AMOUNT/100 AS PERCENTAGE

In this case, the result is 391 and it is an INTEGER. To determine the data type, we can use the SQL_VARIANT_PROPERTY function with the above expression as the first parameter:

DECLARE @AMOUNT INT
SET @AMOUNT=2300
SELECT SQL_VARIANT_PROPERTY(17*@AMOUNT/100,'basetype') as datatype

The result of this query is “datatype ——- int”.

Now, let’s see what happens when we use 17.0 instead of 17:

DECLARE @AMOUNT INT
SET @AMOUNT=2300
SELECT SQL_VARIANT_PROPERTY(17.0*@AMOUNT/100,'basetype') as datatype

The result is “datatype ——- numeric”. This is because 17.0 is of numeric data type, and the end result will be converted to numeric as well.

The SQL_VARIANT_PROPERTY function is a useful tool for identifying data types in SQL Server. It can be particularly helpful when dealing with complex calculations or expressions where the data type may not be immediately obvious.

Have you ever used the SQL_VARIANT_PROPERTY function to identify data types? I would love to hear your story. Please leave a comment below.

For more information on this topic, you can refer to my previous article: SQL SERVER – How to Identify Datatypes and Properties of Variable.