SQL_VARIANT_PROPERTY (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

Topic link icon Transact-SQL Syntax Conventions

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
SQL_VARIANT_PROPERTY ( expression , property )  

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.

ValueDescriptionBase type of sql_variant returned
BaseTypeSQL 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.
PrecisionNumber 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.
ScaleNumber 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.
TotalBytesNumber 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.
CollationRepresents the collation of the particular sql_variant value.sysname

NULL = Input is not valid.
MaxLengthMaximum data type length, in bytes. For example, MaxLength of nvarchar(50) is 100, MaxLength of int is 4.int

NULL = Input is not valid.

sql_variant

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)  

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');  

sql_variant (Transact-SQL)

Community Additions

ADD
Show: