SQL_VARIANT_PROPERTY (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

Transact-SQL syntax conventions

Syntax

SQL_VARIANT_PROPERTY ( expression , property )  

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

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

bit

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:

date = 10

datetime = 23

datetime2 = 27

datetime2 (s) = 19 when s = 0, else s + 20

datetimeoffset = 34

datetimeoffset (s) = 26 when s = 0, else s + 27

smalldatetime = 16

time = 16

time (s) = 8 when s = 0, else s + 9

float = 53

real = 24

decimal and numeric = 18

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 and numeric = 0

decimal (p,s) and numeric (p,s) = s

money and smallmoney = 4

datetime = 3

datetime2 = 7

datetime2 (s) = s (0 - 7)

datetimeoffset = 7

datetimeoffset (s) = s (0 - 7)

time = 7

time (s) = s (0 - 7)

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 fails. 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

A. Using a sql_variant in a table

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)  

B. Using a sql_variant as a variable

The following example retrieves SQL_VARIANT_PROPERTY information about a variable named @v1.

DECLARE @v1 sql_variant;  
SET @v1 = 'ABC';  
SELECT @v1;  
SELECT SQL_VARIANT_PROPERTY(@v1, 'BaseType');  
SELECT SQL_VARIANT_PROPERTY(@v1, 'MaxLength');  

See Also

sql_variant (Transact-SQL)