SQL_VARIANT_PROPERTY function in SQL Server

SQL_VARIANT_PROPERTY exposes the underlying data type and metadata stored inside a sql_variant value. Since sql_variant preserves its original base type, operations like DISTINCT, GROUP BY, ORDER BY, and indexing rely on this internal metadata rather than the displayed value. This is why visually identical values may behave differently.

It supports below properties

Property	Meaning
BaseType	Actual stored SQL data type
Precision	For numeric types
Scale	For numeric types
TotalBytes	Storage size
Collation	For character types
MaxLength	Length for strings

SQL_VARIANT_PROPERTY() returns metadata about the underlying base data type stored inside a sql_variant, such as its base type, precision, scale, and collation.

Because sql_variant doesn’t store just a value — it stores:

[ Base Data Type ] + [ Actual Value ] + [ Metadata ]

And SQL Server normally hides this. SQL_VARIANT_PROPERTY() exposes it.

 Syntex

SQL_VARIANT_PROPERTY ( expression , property )

 Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE Variant_Properties_Demo (     id int identity(1,1) primary key,               dataVal sql_variant );  insert into Variant_Properties_Demo(dataVal) values (10)                           -- int insert into Variant_Properties_Demo(dataVal) values (CAST(10 AS bigint))           -- bigint insert into Variant_Properties_Demo(dataVal) values (10.25)                        -- decimal/float insert into Variant_Properties_Demo(dataVal) values ('10')                         -- varchar insert into Variant_Properties_Demo(dataVal) values (N'10')                        -- nvarchar insert into Variant_Properties_Demo(dataVal) values (CAST('2024-01-01' AS datetime))--datetime;

Table created and inserted records successfully.

See the data and its property using this function.

select id, dataVal, SQL_VARIANT_PROPERTY(dataVal,'BaseType')  AS [BaseType], SQL_VARIANT_PROPERTY(dataVal,'Precision') AS [Precision], SQL_VARIANT_PROPERTY(dataVal,'Scale')     AS [Scale], SQL_VARIANT_PROPERTY(dataVal,'MaxLength') AS [MaxLength], SQL_VARIANT_PROPERTY(dataVal,'TotalBytes')AS [TotalBytes], SQL_VARIANT_PROPERTY(dataVal,'Collation') AS [Collation] from Variant_Properties_Demo