| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SQL_VARIANT_PROPERTY (Transact-SQL) |
SQL_VARIANT_PROPERTY (Transact-SQL) |
MikeRayMSFT |
mikeray |
02/25/2020 |
sql |
t-sql |
reference |
|
|
|
|
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]
Returns the base data type and other information about a sql_variant value.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
SQL_VARIANT_PROPERTY ( expression , property )
Note
[!INCLUDEsynapse-analytics-od-unsupported-syntax]
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 | [!INCLUDEssNoVersion] 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. |
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 [!INCLUDEssResult] 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');