In SQL Server, you can use the SQL_VARIANT_PROPERTY() function to return base data type information from a sql_variant value.

The function accepts two arguments: the sql_variant value, and the property for which information is to be provided.

Example 1 – Basic Usage

Here’s an example that demonstrates the basic concept and usage.

DECLARE @var sql_variant = 'Hey!';
SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType') AS BaseType;

Result:

+------------+
| BaseType   |
|------------|
| varchar    |
+------------+

In this case, the base type is varchar.

Here’s what happens if I change the value to a different type:

DECLARE @var sql_variant = $1.50;
SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType') AS BaseType;

Result:

+------------+
| BaseType   |
|------------|
| money      |
+------------+

This time the base type is money.

Example 2 – Other Properties

As of this writing, there are six possible arguments for this function. In other words, you can get information about six different properties of the passed in value.

Here’s an example that uses all six:

DECLARE @var sql_variant = SYSDATETIME();
SELECT 
  SQL_VARIANT_PROPERTY(@var, 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY(@var, 'Precision') AS Precision,
  SQL_VARIANT_PROPERTY(@var, 'Scale') AS Scale,
  SQL_VARIANT_PROPERTY(@var, 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY(@var, 'Collation') AS Collation,
  SQL_VARIANT_PROPERTY(@var, 'MaxLength') AS MaxLength;

Result:

+------------+-------------+---------+--------------+-------------+-------------+
| BaseType   | Precision   | Scale   | TotalBytes   | Collation   | MaxLength   |
|------------+-------------+---------+--------------+-------------+-------------|
| datetime2  | 27          | 7       | 11           | NULL        | 8           |
+------------+-------------+---------+--------------+-------------+-------------+