sql-server - 使用 sql_variant 有哪些陷阱？

Question

我多次读到和听到应该避免sql_variant。我想我有一个很好的用例。我过去曾使用 varchar(max) 来在同一列中存储不同的类型，但当有一个内置类型完全符合我的要求时，避免反/序列化开销似乎是明智的想要。

那么，使用sql_variant到底有哪些陷阱？它们与性能相关，还是容易犯编程错误，还是其他原因？顺便说一句，如果需要考虑的话，我将通过客户端代码和 CLR 函数与本专栏进行交互。

Answer 1

通过 SQL_VARIANT 在同一列中存储不同类型与将所有内容转换到 Object 几乎是一样的在.NET中。有时使用这种类型是有充分理由的，因为它肯定可以允许更通用的编程结构。

但是，正如您所预料的，使用 SQL_VARIANT 存在一些陷阱您应该注意这一点，尤其是其中之一可能会破坏交易:

1. 就像将所有内容转换到 Object 一样在 .NET 中(并且可能需要装箱/拆箱，具体取决于基本类型)，使用 SQL_VARIANT 时会明显影响性能。 。根据使用案例，如果功能确实需要它和/或使用不是很频繁(即每秒多次)，那么降低性能可能是可以接受的。

2. 与将所有内容转换到 Object 不同在.NET中，SQL_VARIANT datatype 对它可以包含的基本数据类型有限制。以下数据类型不能存储为 SQL_VARIANT :

   • VARCHAR(MAX)
   • NVARCHAR(MAX)
   • VARBINARY(MAX)
   • XML
   • TIMESTAMP/ROWVERSION
   • TEXT (从 SQL Server 2005 开始，您无论如何都不应该使用此类型)
   • NTEXT (从 SQL Server 2005 开始，您无论如何都不应该使用此类型)
   • IMAGE (从 SQL Server 2005 开始，您无论如何都不应该使用此类型)

   此限制可以轻松阻止使用 SQL_VARIANT 的可能性如果需要存储任何这些数据类型。请注意，这里的问题是基本数据类型，而不是数据的大小，如以下测试所示:

   DECLARE @tmp1 TABLE (col1 SQL_VARIANT NOT NULL);
   INSERT INTO @tmp1 (col1) VALUES (CONVERT(VARCHAR(MAX), 'g'));
   

   返回:

   Msg 206, Level 16, State 2, Line 2
   Operand type clash: varchar(max) is incompatible with sql_variant
   

公平地说，使用 SQL_VARIANT 的一个好处是将所有内容都转换到 NVARCHAR是SQL_VARIANT保留底层类型信息并强制其使用，以便您不能轻易地在完全不适当的上下文中误用值。

DECLARE @tmp2 TABLE (col1 SQL_VARIANT NOT NULL);
INSERT INTO @tmp2 (col1) VALUES (1);

SELECT CONVERT(DATETIME, col1) FROM @tmp2;

SELECT CONVERT(TIME, col1) FROM @tmp2;

返回:

1900-01-02 00:00:00.000

Msg 529, Level 16, State 3, Line 6
Explicit conversion from data type int to time is not allowed.

关于无法使用SQL_VARIANT作为 PK:这实际上不是问题，因为通用数据类型的本质几乎排除了它在这种用途上的可取性。

关于无法使用SQL_VARIANT与 LIKE运算符:这基本上不是问题，因为能够将其转换为适用于 LIKE 的适当类型，如:

WHERE CONVERT(NVARCHAR(50), [sql_variant_field]) LIKE '%something%'

以上当然不是最有效的，但它是功能性的，并且如上所述，在决定使用 SQL_VARIANT 时，效率已经被排除，因为它是为了换取功能而牺牲的。数据类型。