Syntax of SQL_VARIANT_PROPERTY Function :
SQL_VARIANT_PROPERTY( expression, property )
expression is a valid sql expression of type sql_variant.
property is a sql_variant property name. It is of type varchar(128). It can be of below values.
Value | Description | Base type |
BaseType | The SQL Server data type, such as: char
int
money
nchar
ntext
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
text
timestamp
tinyint
uniqueidentifier
varbinary
varchar | sysname Invalid input = NULL |
Precision | The 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
int = 10
smallint = 5
tinyint = 3
bit = 1
all other types = 0 | int Invalid input = NULL |
Scale | The 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 Invalid input = NULL |
TotalBytes | The number of bytes required to hold both the meta data
and data of the value. This information would be useful in checking the
maximum side of data in asql_variant column.
If the value is greater than 900, index creation will fail. | int Invalid input = NULL |
Collation | Represents the collation of the particular sql_variantvalue. | sysname Invalid input = NULL |
MaxLength | The maximum data type length, in bytes. For example,MaxLength of nvarchar(50) is 100, MaxLength ofint is 4. | int Invalid input = NULL |
Return type of SQL_VARIANT_PROPERTY function is a sql_variant.
Examples of SQL_VARIANT_PROPERTY Function :
Example 1 : Use of SQL_VARIANT_PROPERTY function in select clause
SELECT SQL_VARIANT_PROPERTY (OrderID, 'BaseType')
FROM Orders
WHERE OrderID = 10248
Above example returns data type of OrderID column of Orders table.