SQL语法提示工具SQL Prompt教程：使用SQL_VARIANT数据类型引起的问题（上）

本篇教程说明了SQL_VARIANT数据类型的“怪癖”，以及为什么最好调查SQL Prompt何时提醒您使用它。如果在使用之前将其显式转换为真实类型，那么将数据存储为SQL_VARIANT才是唯一安全的。本文描述教程的上半部分。

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读–当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

SQL Prompt免费版

本教程说明了SQL_VARIANT数据类型的“怪癖”，以及为什么最好调查SQL Prompt何时提醒您使用它。如果在使用之前将其显式转换为真实类型，那么将数据存储为SQL_VARIANT才是唯一安全的。本文描述教程的上半部分。

sql_variant数据类型来自几个不同数据类型的值，并由SQL Server在内部使用。它不是SQL标准的一部分，在关系数据库中的用途有限。需要小心处理它，因为它的误用会导致难以追踪的性能问题和bug。sql_variant不能直接传递给某些SQL运算符和函数，例如LIKE、SUM()或者AVG()，并且在比较或表达式中使用时会产生误导性结果。除二进制数据外，它不能通过ODBC返回到应用程序。

SQL Server是一种强类型语言，这样做是为了确保数据完整性、高效存储和有效检索。由于这个原因，使用sql_variant有点奇怪，因此通过不明智地使用它会无意中造成问题也就不足为奇了。出于这些原因，SQL Prompt强制执行“最佳实践”代码分析规则（BP024），该规则将提醒您使用sql_variant数据类型。

与许多“最佳实践”规则一样，这些建议有时听起来像是告诉人们在拿着剪刀时不要跑。在这种情况下，只有在使用数据sql_variant之前将其显式转换为真实类型，才能将数据存储为安全。

为什么有sql_variant/strong>

sql_variant数据类型是在微软从Sybase开发的SQL Server时首次引入的。他们需要能够从微软首次进入数据库市场的Microsoft Access将数据库导入SQL Server，该市场支持变体数据类型。它仍然在SQL Server内部用于系统存储过程的参数以及扩展属性等数据。

sql_variant倾向于作为用户定义函数返回的列、变量、参数或值的catch-all数据类型。它最多可以容纳8000个字节，并且可以存储基本数据类型，如整数、小数、字符串和日期。它不能存储其他一些数据类型，例如（MAX）数据类型、CLR数据类型或XML。

有时，sql_variant可能是一个有用的工具，例如在处理不一致或未指定的数据类型时，这通常是因为数据库支持允许用户定义数据的应用程序。

它存储所包含的值的基本数据类型，因此当它用作中介时，强制执行数据类型之间的所有转换规则。您可以使用数据类型函数检索此基本数据类型sql_variant_property()：

DECLARE @MyVariant SQL_VARIANT = '2.3657'  SELECT SQL_VARIANT_PROPERTY(@MyVariant,'BaseType')

在这种情况下返回varchar。这里还有一些其他有用的属性：Precision、Scale、TotalBytes、Collation和MaxLength。如果要从sql_variant生成主键，则TotalBytes参数使该函数可用作初步检查，因为主键（或索引）的总大小限制为900字节。

顺便提一句，您可以在任何数据类型上使用此函数。例如：

SELECT SQL_VARIANT_PROPERTY(N'Béoáed mac Ocláin','collation')

聚合

让我们看看如果我们尝试聚合sql_variant列会发生什么。为了简单起见，我们将从派生表中执行此操作。

SELECT Sum(ValueAsVariant)    FROM      (      VALUES (Convert(SQL_VARIANT, 'one'), 1, Convert(SQL_VARIANT, 1)),             ('two', 2, 2),             ('three', 3, 3),             ('four', 4, 4),              ('five', 5, 5)      ) AS f (ValueAsString, ValueAsInt, ValueAsVariant);

我们看到一个错误：

Msg 8117，Level 16，State 1，Line 3操作数数据类型sql_variant对sum运算符无效。

而如果我们先显式地转换为数字（int、numeric等等），它工作正常。

SELECT Sum(Convert(NUMERIC(9,4), ValueAsVariant))  -- try sum, avg, stdev, stdevp, var, varp, or string_agg    FROM      (      VALUES (Convert(SQL_VARIANT, 'one'), 1, Convert(SQL_VARIANT, 1)),             ('two', 2, 2),             ('three', 3, 3),             ('four', 4, 4),              ('five', 5, 5)      ) AS f (ValueAsString, ValueAsInt, ValueAsVariant);

在max()和min()聚合函数似乎很好地工作的sql_variant数据类型，所以不可能有技术问题阻止其他函数工作。

本教程内容尚未完结，后续内容请点击下面的文章~

相关文章：

SQL语法提示工具SQL Prompt教程：使用SQL_VARIANT数据类型引起的问题（下）

SQL语法提示工具SQL Prompt教程：避免使用@@IDENTITY函数的原因

SQL语法提示工具SQL Prompt教程：忽略使用或滥用RETURN关键字（BP016）

SQL语法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的问题（上）

SQL语法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的问题（下）

想要购买SQL Prompt正版授权，或了解更多产品信息请点击“咨询在线客服”

扫描关注慧聚IT微信公众号，及时获取最新动态及最新资讯

标签：

来源：慧都