SQL_VARIANT_PROPERTY (Transact-SQL)
Returns the base data type and other information about a sql_variant value.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
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 2005 data type, such as:
bigint
binary
char
decimal
float
int
money
nchar
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
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 of int 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 has colA 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)