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
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για