• 项目

使用 sql_variant 数据

sql_variant 数据类型使用方式与 Microsoft Visual Basic 中的 variant 数据类型相似。sql_variant 使单列、参数或变量存储不同数据类型的数据值。例如，一个 sql_variant 列可以容纳 int、decimal、char、binary 和 nchar 值。每个 sql_variant 列实例都记录数据值和元数据信息。其中包括基本数据类型、最大大小、小数位数、精度和排序规则。

sql_variant 的使用规则

以下规则应用于使用 sql_variant 数据类型。

常规赋值

  • sql_variant 对象可以容纳 SQL Server 所有数据类型（下列类型除外：text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml、timestamp 和 Microsoft .NET Framework 公共语言运行时 [CLR] 用户定义类型）的数据。另外，sql_variant 数据实例还不能将 sql_variant 作为其基础的基本数据类型。

  • 所有类型的常量都可以在引用 sql_variant 列的谓词或赋值中指定。

  • 如果 sql_variant 值为 NULL，则认为其没有基础的基本数据类型。即使 Null 值来自具有特殊数据类型的变量或列，这项规则也适用。

    在下面的示例中，VariantCol 的值被设置为无关联数据类型的 NULL，尽管 Null 值来自一个 int 变量：

    DECLARE @IntVar int

    SET @IntVar = NULL

    UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123

  • 在将 sql_variant 对象的值赋给具有其他数据类型的对象时，必须将 sql_variant 值显式转换为目标数据类型。不支持在将 sql_variant 值赋给具有其他数据类型的对象时执行任何隐式转换。

  • 为了与其他数据类型兼容，报告 sql_variant 对象长度的目录对象（例如 DATALENGTH 函数）会报告数据的长度。但不返回 sql_variant 对象中包含的元数据的长度。

  • 如果要使用 sql_variant 列，ANSI_PADDING 必须为 ON。如果从 ANSI_PADDING 为 OFF 的源对 char、nchar、varchar、nvarchar 或 varbinary 值进行赋值，则不填充这些值。

  • 如果更新订阅服务器中的某个列，将导致更改另一个 sql_variant 列中的基类型。下面的过程说明了这一概念：

    1. 创建一个合并发布/订阅。发布的表应包含 sql_variant 和 c1 列。在 sql_variant 列中添加一些数据。数据的基类型为 datetime。

    2. 在进行初始同步后，订阅服务器上的基类型仍然为 datetime。

    3. 更新订阅服务器中的 c1 列。

    4. 发布服务器上 sql_variant 列中的数据已更改为 datetime2。

表中的 sql_variant

  • 只要键列中的数据长度不超过 900 字节，就可以在索引和唯一键中使用 sql_variant 列。

  • sql_variant 列不支持 IDENTITY 属性，但允许 sql_variant 列作为主键或外键的一部分。

  • 在计算列中不能使用 sql_variant 列。

  • 可以使用 ALTER TABLE 将任何数据类型（text、ntext、image、timestamp 或 sql_variant 除外）的列更改为 sql_variant。所有现有值都将转换成 sql_variant 值，这些值的基本数据类型与执行 ALTER TABLE 语句之前列的数据类型相同。由于不支持从 sql_variant 到其他数据类型的隐式转换，因此不能使用 ALTER TABLE 将 sql_variant 列的数据类型更改为任何其他数据类型。

排序规则

  • COLLATE 子句不能用于将列排序规则分配给 sql_variant 列。sql_variant 列中基于字符的值（char、nchar、varchar 和 nvarchar）可以应用任何排序规则，单个 sql_variant 列可以容纳应用混合排序规则的基于字符的值。

  • 当将一个值赋给 sql_variant 实例时，会将源的数据值和基本数据类型都赋给它。如果源值具有排序规则，则排序规则也会赋给它。如果源值具有用户定义数据类型，则会把用户定义数据类型的基本数据类型而不是用户定义数据类型赋给它。sql_variant 实例不继承任何用户定义数据类型的规则和默认值。如果将具有 IDENTITY 属性的列的值赋给 sql_variant 实例，则 sql_variant 将继承源列的基本数据类型，而不继承 IDENTITY 属性。不应将 text、ntext 或 image 值赋给 sql_variant 实例。支持在将具有其他数据类型的对象的值赋给 sql_variant 对象时执行隐式转换。

sql_variant 比较

sql_variant 列可以包含具有几种基本数据类型和排序规则的值，所以当比较 sql_variant 操作数时，要应用一些特殊规则。这些规则适用于各种比较操作，例如：

  • Transact-SQL 比较运算符

  • ORDER BY、GROUP BY

  • 索引

  • MAX 和 MIN 聚合函数

  • UNION（不包括 ALL）

  • CASE 表达式

为了进行 sql_variant 比较，SQL Server 数据类型层次结构顺序划分为数据类型系列。sql_variant 系列的优先级最高。

数据类型层次结构

数据类型系列

sql_variant

sql_variant

datetime

日期和时间

smalldatetime

日期和时间

Float

近似数值

Real

近似数值

decimal

精确数值

money

精确数值

smallmoney

精确数值

bigint

精确数值

int

精确数值

smallint

精确数值

tinyint

精确数值

bit

精确数值

nvarchar

Unicode

nchar

Unicode

varchar

Unicode

char

Unicode

varbinary

Binary

binary

Binary

uniqueidentifier

Uniqueidentifier

以下规则适用于 sql_variant 比较：

  • 比较具有不同基本数据类型的 sql_variant 值，而且基本数据类型属于不同的数据类型系列时，认为在层次结构图中数据类型系列较高的值为两值中的较大值。

  • 比较具有不同基本数据类型的 sql_variant 值，而且基本数据类型属于相同的数据类型系列时，层次结构图中基本数据类型较低的值先隐式转换成其他数据类型，然后再进行比较。

  • 在比较 char、varchar、nchar 或 varchar 数据类型的 sql_variant 值时，将基于以下条件进行计算：LCID、LCID 版本、比较标志和排序 ID。各个条件按所列出的顺序作为整数值进行比较。

应用这些规则在 sql_variant 值之间进行比较与在具有相同基本数据类型的值之间进行比较，它们会产生不同的结果。

操作数 A

操作数 B

非变量比较结果

sql_variant 比较结果

'123' char

111 int

A > B

B > A

50000 int

5E1 float

A > B

B > A

因为不同数据类型系列的值在比较谓词中引用前必须显式转换，所以只有当对 sql_variant 列上的结果集进行排序时才能看出这些规则的效果。下表中的值是有关数据类型优先级规则的示例。

PriKey

VariantCol

1

50.0（基类型 float）

2

5000（基类型 int）

3

'124000'（基类型 char(6)）

下表显示以下语句的结果：SELECT * FROM VariantTest ORDER BY VariantCol ASC。

PriKey

VariantCol

3

'124000'（基类型 char(6)）

2

5000（基类型 int）

1

50.0（基类型 float）

下表中的值是使用不同的排序规则时排序规则优先级规则的示例。

IntKey

VariantCol

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

4

17.5 (decimal)

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

7

1.2 (decimal)

下表显示了 SELECT * FROM CollateTest ORDER BY VariantCol 语句的结果。下表显示的是组合在一起的确切数字数据类型系列的值和按各自的排序规则中组合的 varchar 值。

IntKey

VariantCol

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

7

1.2 (decimal)

4

17.5 (decimal)

函数和 sql_variant 数据

下列 Transact-SQL 函数支持 sql_variant 参数，并在指定了 sql_variant 参数时返回 sql_variant 值：

COALESCE

MIN

MAX

NULLIF

下列函数支持引用 sql_variant 列或变量，但不使用 sql_variant 作为它们返回值的数据类型：

COL_LENGTH

DATALENGTH

TYPEPROPERTY

COLUMNPROPERTY

ISNULL

 

下列 Transact-SQL 函数不支持 sql_variant 参数：

AVG

RADIANS

STDEV[P]

IDENTITY

ROUND

SUM

ISNUMERIC

SIGN

VAR[P]

POWER

 

 

CAST 函数和 CONVERT 函数都支持 sql_variant。

新的 SQL_VARIANT_PROPERTY() 函数可以用来获取有关 sql_variant 值的属性信息，例如数据类型、精度或小数位数。

其他 Transact-SQL 元素和 sql_variant 数据

在 LIKE 谓词中不支持 sql_variant 列。

全文索引中不支持 sql_variant 列。不能在全文函数（例如 CONTAINSTABLE 和 FREETEXTTABLE）中指定 sql_variant 列。

下列 Transact-SQL 语句支持在与指定其他整数数据类型相同的语法位置上指定 sql_variant：

  • ALTER PROCEDURE

  • ALTER TABLE

  • CREATE PROCEDURE

  • CREATE TABLE

  • DECLARE variable

SQL Server 目录组件可以报告有关 sql_variant 列的信息。

如果任何输入表达式或结果表达式属于 sql_variant，则 CASE 表达式的结果就是 sql_variant。结果的基础的基本数据类型是运行时所求表达式的基础数据类型。

数值或字符串串联运算符的操作数不能是 sql_variant。例如，以下代码将生成错误：

SELECT VariantCol + @CharacterVar

FROM MyTable

但是，通过转换 sql_variant 操作数，可以执行以下操作：

SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar

FROM MyTable

应用程序和 sql_variant 数据

如果应用程序要求一个结果集，其中一个特定列返回具有单个基础的基本数据类型的 sql_variant 数据，则该应用程序可以在 Transact-SQL 语句中使用 CAST 函数或 CONVERT 函数来通过使用基础的基本数据类型返回 sql_variant 数据。在这种情况下，应用程序处理数据的方式就像其处理基础的基本数据类型的结果集列的方式。

SQL Server Native Client OLE DB Provider for SQL Server 引入了特定于访问接口的 OLE DB 类型 DBTYPE_SQLVARIANT，用于 sql_variant 列和参数。

SQL Server SQL Server Native Client ODBC 驱动程序引入了特定于访问接口的 ODBC 数据库数据类型 SQL_SS_VARIANT，用于 sql_variant 列和参数。

使用通过以下接口连接的应用程序时，SQL Server 将 sql_variant 值转换为 nvarchar(4000)：

  • 用于 SQL Server 7.0 版的 OLE DB 访问接口。

  • SQL Server 7.0 提供的 SQL Server ODBC 驱动程序。

如果结果字符串超过 4,000 个字符，则 SQL Server 返回前 4,000 个字符。

在使用通过以下接口连接的应用程序时，SQL Server 会将 sql_variant 值转换 varchar(255)：

  • SQL Server 6.5 或更低版本提供的 SQL Server ODBC 驱动程序。

如果结果字符串超过 255 个字符，则 SQL Server 返回前 255 个字符。