Sdílet prostřednictvím

  • Článek

SQL_VARIANT_PROPERTY (Transact-SQL)

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

Topic link iconTransact-SQL Syntax Conventions

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 one of the following values.

    Value

    Description

    Base type of sql_variant returned

    BaseType

    SQL 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.

    Precision

    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

    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 (p,s) and numeric (p,s) = s

    money and smallmoney = 4

    datetime = 3

    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 will fail.

    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

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)