sql_variant (Transact-SQL)sql_variant (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

一种数据类型,用于存储 SQL ServerSQL Server 支持的各种数据类型的值。A data type that stores values of various SQL ServerSQL Server-supported data types.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

sql_variant  

RemarksRemarks

sql_variant 可以用在列、参数、变量和用户定义函数的返回值中。sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions. 借助 sql_variant,这些数据库对象可以支持其他数据类型的值。sql_variant enables these database objects to support values of other data types.

类型为 sql_variant 的列可能包含不同数据类型的行。A column of type sql_variant may contain rows of different data types. 例如,定义为 sql_variant 的列可以存储 int、binary 和 char 类型的值。For example, a column defined as sql_variant can store int, binary, and char values.

sql_variant 的最大长度可以是 8016 个字节。sql_variant can have a maximum length of 8016 bytes. 这包括基类型信息和基类型值。This includes both the base type information and the base type value. 实际基类型值的最大长度是 8,000 个字节。The maximum length of the actual base type value is 8,000 bytes.

对于 sql_variant 数据类型,必须先将它转换为其基本数据类型值,然后才能参与诸如加减这类运算。A sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction.

可以为 sql_variant 分配默认值。sql_variant can be assigned a default value. 该数据类型还可以将 NULL 作为其基础值,但是 NULL 值没有关联的基类型。This data type can also have NULL as its underlying value, but the NULL values will not have an associated base type. 此外,sql_variant 不能使用其他 sql_variant 作为其基础类型。Also, sql_variant cannot have another sql_variant as its base type.

唯一键、主键或外键可能包含类型为 sql_variant 的列,但是,组成指定行的键的数据值的总长度不应大于索引的最大长度。A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values that make up the key of a specific row should not be more than the maximum length of an index. 该最大长度是 900 个字节。This is 900 bytes.

一个表可以包含任意多个 sql_variant 列。A table can have any number of sql_variant columns.

不能在 CONTAINSTABLE 和 FREETEXTTABLE 中使用 sql_variant。sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.

ODBC 不完全支持 sql_variant。ODBC does not fully support sql_variant. 因此,使用 Microsoft OLE DB Provider for ODBC (MSDASQL) 时,sql_variant 列的查询将作为二进制数据返回。Therefore, queries of sql_variant columns are returned as binary data when you use Microsoft OLE DB Provider for ODBC (MSDASQL). 例如,包含字符串数据 'PS2091' 的 sql_variant 列将作为 0x505332303931 返回。For example, a sql_variant column that contains the character string data 'PS2091' is returned as 0x505332303931.

比较 sql_variant 值Comparing sql_variant Values

sql_variant 数据类型在用于转换的数据类型层次结构列表中位于顶部。The sql_variant data type belongs to the top of the data type hierarchy list for conversion. 为了进行 sql_variant 比较,SQL ServerSQL Server 数据类型层次结构顺序划分为多个数据类型系列。For sql_variant comparisons, the SQL ServerSQL Server data type hierarchy order is grouped into data type families.

数据类型层次结构Data type hierarchy 数据类型系列Data type family
sql_variantsql_variant sql_variantsql_variant
datetime2datetime2 日期和时间Date and time
datetimeoffsetdatetimeoffset 日期和时间Date and time
datetimedatetime 日期和时间Date and time
smalldatetimesmalldatetime 日期和时间Date and time
datedate 日期和时间Date and time
timetime 日期和时间Date and time
floatfloat 近似数值Approximate numeric
realreal 近似数值Approximate numeric
decimaldecimal 精确数值Exact numeric
moneymoney 精确数值Exact numeric
smallmoneysmallmoney 精确数值Exact numeric
bigintbigint 精确数值Exact numeric
intint 精确数值Exact numeric
smallintsmallint 精确数值Exact numeric
tinyinttinyint 精确数值Exact numeric
bitbit 精确数值Exact numeric
nvarcharnvarchar UnicodeUnicode
ncharnchar UnicodeUnicode
varcharvarchar UnicodeUnicode
charchar UnicodeUnicode
varbinaryvarbinary 二进制Binary
binarybinary 二进制Binary
uniqueidentifieruniqueidentifier UniqueidentifierUniqueidentifier

下列规则适用于 sql_variant 比较:The following rules apply to sql_variant comparisons:

  • 当不同基本数据类型的 sql_variant 值进行比较,而且基本数据类型属于不同的数据类型系列时,则在层次结构图中数据类型系列较高的值被认为在两个值中较大。When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values.
  • 当不同基本数据类型的sql_variant 值进行比较,而且基本数据类型属于相同的数据类型系列时,则在层次结构图中基本数据类型较低的值先隐式转换为其他数据类型,然后再进行比较。When sql_variant values of different base data types are compared and the base data types are in the same data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.
  • 在比较“char”、“varchar”、“nchar”或“nvarchar”数据类型的“sql_variant”值时,将首先基于以下条件来比较这些值的排序规则:LCID、LCID 版本、比较标识和排序 ID。When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. 其中的每个条件都按所列出的顺序作为整数值进行比较。Each of these criteria are compared as integer values, and in the order listed. 如果所有这些条件都相等,则将按照排序规则来比较实际的字符串值。If all of these criteria are equal, then the actual string values are compared according to the collation.

转换 sql_variant 数据Converting sql_variant Data

当处理 sql_variant 数据类型时,SQL ServerSQL Server 支持将其他数据类型的对象隐式转换为 sql_variant 类型。When handling the sql_variant data type, SQL ServerSQL Server supports implicit conversions of objects with other data types to the sql_variant type. 但是,SQL ServerSQL Server 不支持从 sql_variant 数据隐式转换为其他数据类型的对象。However, SQL ServerSQL Server does not support implicit conversions from sql_variant data to an object with another data type.

限制Restrictions

下表列出了无法使用 sql_variant 存储的值的类型:The following table lists the types of values that cannot be stored by using sql_variant:

varchar(max)varchar(max) varbinary(max)varbinary(max)
nvarchar(max)nvarchar(max) xmlxml
texttext ntextntext
图像image rowversion (timestamp)rowversion (timestamp)
sql_variantsql_variant 地理geography
hierarchyidhierarchyid geometrygeometry
用户定义类型User-defined types datetimeoffset1datetimeoffset1

1 SQL Server 2012 及更高版本均不限制 datetimeoffset。1 SQL Server 2012 and greater do not restrict datetimeoffset.

示例Examples

A.A. 在表中使用 sql_variantUsing a sql_variant in a table

下面的示例创建一个 sql_variant 数据类型的表。The following example, creates a table with a sql_variant data type. 然后该示例检索有关 colA46279.1SQL_VARIANT_PROPERTY 信息,其中,colB =1689,并假设 tableA 有类型为 sql_variantcolBcolAThen the 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.请注意,这三个值中的每一个都是 sql_variant。Note that each of these three values is a sql_variant.

Base Type    Precision    Scale  
---------    ---------    -----  
decimal      8           2  
  
(1 row(s) affected)  

B.B. 使用 sql_variant 作为变量Using a sql_variant as a variable

下面的示例使用 sql_variant 数据类型创建了一个变量,然后检索名为 @v1 的变量的 SQL_VARIANT_PROPERTY 信息。The following example, creates a variable using the sql_variant data type, and then 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

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)SQL_VARIANT_PROPERTY (Transact-SQL)