Practical usage of SQL_VARIANT_PROPERTY in SQL SERVER

SQL_VARIANT_PROPERTY

Returns the base data type and other information about a sql_variant value.

1. We can Store different data like int,string,float in the single column.
2. Mutiples data types in the single column.
3. Helps to identify the base type of stored value.
4. Exactly works as arraylist in c#.

--Create Table

CREATE TABLE tableA(colA sql_variant, colB int)

INSERT INTO tableA values ( cast (46279.1 as decimal(8,2)), 1689)

INSERT INTO tableA values ( cast ('ARUN' as NVARCHAR), 1689)

INSERT INTO tableA values ( cast ('ARUN' as VARCHAR), 1689)

INSERT INTO tableA values (22, cast(244 as tinyint))

INSERT INTO tableA values (22, 1698)--int

INSERT INTO tableA values ('Arun', 1698) -- varchar

-- Select SQL_VARIANT_PROPERTY

SELECT

colA,

SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',

SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',

SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'

FROM tableA

OUTPUT:

colA Base Type Precision Scale

46279.10 decimal 8 2

ARUN nvarchar 0 0

ARUN varchar 0 0

22 int 10 0

22 int 10 0

Arun varchar 0 0