SQL_VARIANT_PROPERTY
Returns the base data
type and other information about a sql_variant value.
-
We can Store different data like int,string,float in the single column.
-
Mutiples data types in the single column.
-
Helps to identify the base type of stored value.
-
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