使用 sql_variant 有哪些陷阱？

发布于 2024-12-11 10:21:52 字数 230 浏览 0 评论 0 原文

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

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

表情可笑 2024-12-18 10:21:52

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

然而，正如您所预料的，使用 SQL_VARIANT 存在一些您应该注意的陷阱，特别是其中一个可能会破坏交易：

就像将所有内容都转换为 .NET 中的对象（并且可能需要装箱/拆箱，具体取决于基本类型），使用 SQL_VARIANT 时会明显影响性能。根据用例，如果功能确实需要它和/或使用不是很频繁（即每秒多次），那么降低性能可能是可以接受的。
与 .NET 中将所有内容转换为 Object 不同，SQL_VARIANT 数据类型对其可以包含的基本数据类型有限制。以下数据类型不能存储为 SQL_VARIANT：
- VARCHAR(MAX)
- NVARCHAR(MAX)
- VARBINARY(MAX)
- XML
- 时间戳 / ROWVERSION
- TEXT（从 SQL Server 2005 开始，您不应该使用此类型）
- NTEXT（从 SQL Server 2005 开始，您不应该使用此类型）
- IMAGE（从 SQL Server 2005 开始，您不应该使用此类型）
如果需要存储任何这些数据类型，此限制可以轻松阻止使用SQL_VARIANT。请注意，这里的问题是基本数据类型，而不是数据的大小，如以下测试所示：
```
声明 @tmp1 TABLE (col1 SQL_VARIANT NOT NULL);
INSERT INTO @tmp1 (col1) VALUES (CONVERT(VARCHAR(MAX), 'g'));
```
返回：
```
消息 206，级别 16，状态 2，第 2 行
操作数类型冲突：varchar(max) 与 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 数据类型。

Storing different types in the same column via SQL_VARIANT is almost the same thing as casting everything to Object in .NET. And sometimes there are valid reasons for using this type as it can certainly allow for a more generic programmatic structure.

However, as you were anticipating, there are some pitfalls to using SQL_VARIANT that you should be aware of, especially as one of them might be a deal-breaker:

Just like casting everything to Object in .NET (and possibly requiring boxing / unboxing depending on the base type), there is a definite performance hit when using SQL_VARIANT. Depending on the use case, it might be acceptable to have reduced performance if the functionality really needs it and/or the usage is not very frequent (i.e. many times per second).
Unlike casting everything to Object in .NET, the SQL_VARIANT datatype has limitations on what base datatypes it can contain. The following datatypes cannot be stored as SQL_VARIANT:
- VARCHAR(MAX)
- NVARCHAR(MAX)
- VARBINARY(MAX)
- XML
- TIMESTAMP / ROWVERSION
- TEXT (you shouldn't be using this type anyway as of SQL Server 2005)
- NTEXT (you shouldn't be using this type anyway as of SQL Server 2005)
- IMAGE (you shouldn't be using this type anyway as of SQL Server 2005)
This limitation can easily prevent the possibility of using SQL_VARIANT if there is a requirement to store any of these datatypes. Please note that the issue here is the base datatype and not the size of the data, as the following test shows:
```
DECLARE @tmp1 TABLE (col1 SQL_VARIANT NOT NULL);
INSERT INTO @tmp1 (col1) VALUES (CONVERT(VARCHAR(MAX), 'g'));
```
Returns:
```
Msg 206, Level 16, State 2, Line 2
Operand type clash: varchar(max) is incompatible with sql_variant
```

To be fair, one benefit to using SQL_VARIANT over casting everything to NVARCHAR is that SQL_VARIANT retains the underlying type info and enforces its usage so that you cannot easily misuse values in completely inappropriate contexts.

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;

Returns:

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.

Regarding not being able to use SQL_VARIANT as a PK: this is really a non-issue since the very nature of a generic datatype pretty much excludes it from being desirable in the first place for such a use.

Regarding not being able to use SQL_VARIANT with a LIKE operator: this is mostly a non-issue due to being able to convert it to an appropriate type that does work with LIKE, as in:

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

The above is certainly not the most efficient, but it is functional, and as mentioned above, efficiency was already ruled out as it was sacrificed in return for functionality when deciding to use the SQL_VARIANT datatype.

回复收藏 0 原文

世俗缘 2024-12-18 10:21:52

我见过性能问题和代码质量相关问题：

大多数时候您访问此字段，您必须检查类型（使用 sql_variant_property）。这会使您的查询更加复杂，从而可能导致您列出的两个问题。

您还必须在每次使用该字段时投射该字段，从而导致进一步的性能损失。

此外，sql_variant 列不能作为主键的一部分，它们不能作为计算列的一部分，并且不能与 WHERE 子句中的 LIKE 一起使用。

回复收藏 0 原文

你是年少的欢喜 2024-12-18 10:21:52

这也使得编程错误更容易发生。 DBA/程序员查看一列，它看起来像一个整数，因此他在其中放入一个整数，但在更远的地方，进程希望它是一个字符串。我见过这种情况，因为 sql_variant 列的导入写得不好。

回复收藏 0 原文

清晨说晚安 2024-12-18 10:21:52

想到的唯一明显的陷阱是在您想要推入 sql_variant 字段的值超过其最大长度（8016 字节，根据此网页：http://msdn.microsoft.com/en-us/library/ms173829.aspx）。如果您的值永远不会达到该限制，那么 sql_variant 可能是一个非常好的方法。否则，您仍然可以使用 sql_variant，但提供一个单独的“isBlob”位字段，该字段指向带有 varbinary(max) 值的单独表（例如）。