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

Varchar(max) is invalid basetype for sql_variant:

INSERT INTO tableA values ( cast (46 as varchar(max)), 1689)

Throws Error:

Msg 206, Level 16, State 2, Line 1

Operand type clash: varchar(max) is incompatible with sql_variant

The below data types permitted to store in sql_variant datatype.

SQL Server data type, such as:

bigint
binary
char
date
datetime
datetime2
datetimeoffset
decimal
float
int
money
nchar
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
time
tinyint
uniqueidentifier
varbinary
varchar

Reference Link:

http://msdn.microsoft.com/en-us/library/ms178550

http://msdn.microsoft.com/en-us/library/ms173829