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 of the following values.
| Value | Description | Base type of sql_variant returned |
| 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 a sql_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_variant value. | sysname Invalid input = NULL |
| MaxLength | The maximum data type length, in bytes. For example, MaxLength of nvarchar(50) is 100, MaxLength of int is 4. | int Invalid input = NULL |
Return Types
sql_variant
Examples
This example retrieves SQL_VARIANT_PROPERTY information on 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'),
SQL_VARIANT_PROPERTY(colA,'Precision'),
SQL_VARIANT_PROPERTY(colA,'Scale')
FROM tableA
WHERE colB = 1689
Here is the result set. (Note that each of these three values is a sql_variant.)
See Also
sql_variant
Using sql_variant_Data
