SQL_VARIANT_PROPERTY Function
SQL_VARIANT_PROPERTY (Transact-SQL)
This functions returns the base data type and other information about a sql_variant value.
Syntax
SQL_VARIANT_PROPERTY ( expression
, property )
Arguments
expression
Is an expression of
type sql_variant.
property
Contains the name
of the sql_variant property for which
information is to be provided. property is varchar(128), and can be any
one of the following values.
|
Value
|
Description
|
Base type of sql_variant returned
|
|
BaseType
|
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
|
sysname
NULL = Input is not valid.
|
|
Precision
|
Number of digits of the numeric base data type:
datetime = 23
smalldatetime = 16
float = 53
real = 24
decimal (p,s) and numeric (p,s) = p
money = 19
smallmoney = 10
bigint = 19
int = 10
smallint = 5
tinyint = 3
bit = 1
All other types = 0
|
int
NULL = Input is not valid.
|
|
Scale
|
Number of digits to the right of the decimal point of the numeric base
data type:
decimal (p,s) and numeric (p,s) = s
money and smallmoney = 4
datetime = 3
all other types = 0
|
int
NULL = Input is not valid.
|
|
TotalBytes
|
Number of bytes required to hold both the metadata and data of the
value. This information would be useful in checking the maximum side of data
in a sql_variant column. If the value is larger than 900,
index creation will fail.
|
int
NULL = Input is not valid.
|
|
Collation
|
Represents the collation of the particular sql_variant value.
|
sysname
NULL = Input is not valid.
|
|
MaxLength
|
Maximum data type length, in bytes. For example, MaxLength of nvarchar(50) is
100, MaxLength ofint is 4.
|
int
NULL = Input is not valid.
|
Return Types
sql_variant
Examples
The following example retrieves SQL_VARIANT_PROPERTY information about the colA value 46279.1 where colB =1689, given that tableA hascolA that is of type sql_variant and colB.
CREATE
TABLE tableA(colA sql_variant, colB int)
INSERT INTO tableA values ( cast
(46279.1 as decimal(8,2)), 1689)
SELECT
SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',
SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'
FROM tableA
WHERE colB = 1689
Here is the result set. Note that each of these three values is a sql_variant.
Base
Type Precision Scale
--------- --------- -----
decimal 8 2
(1 row(s) affected)
