SQL语法提示工具SQL Prompt，帮你认识SQL_VA

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？

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数据类型，所以不可能有技术问题阻止其他函数工作。

比较

您不能用LIKE过滤sql_variant列，因为LIKE它不支持sql_variant参数。

SELECT    f.ValueAsVariant, f.ValueAsInt, f.ValueAsString
    FROM
      (
      VALUES (Convert(SQL_VARIANT,'one'), 1, Convert(VARCHAR(5),1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f(ValueAsVariant, ValueAsInt, ValueAsString)
      WHERE ValueAsVariant like 't%'

错误时候这样的：

Msg 8116，Level 16，State 1，Line 4

参数数据类型sql_variant对于LIKE函数的参数1无效。

实际上，没有任何字符串函数接受sql_variant，并且不会尝试对字符串进行隐式转换。相反，他们只是拒绝参数。相反，如果我们声明它到底是什么类型的数据类型，它的工作原理如下：

SELECT    f.ValueAsVariant, f.ValueAsInt, f.ValueAsString
    FROM
      (
      VALUES (Convert(SQL_VARIANT,'one'), 1, Convert(NVARCHAR(5),1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f(ValueAsVariant, ValueAsInt, ValueAsString)
      WHERE Convert(VARCHAR(20),ValueAsVariant) like 't%'

除非您sql_variant在WHERE子句中显式转换数据类型，否则在隐藏在漫长且曲折的过程中时，可能会得到不正确的结果，其原因很难检测到。例如，这只返回第4行和第5行，这是您所期望的：

DECLARE @ParameterAsINT INT
  SELECT @ParameterAsINT = 3
  SELECT    f.ValueAsString, f.ValueAsInt, f.ValueAsVariant
    FROM
      (
      VALUES ('one', 1, Convert(SQL_VARIANT, 1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f (ValueAsString, ValueAsInt, ValueAsVariant)
      WHERE ValueAsVariant > @ParameterAsInt

但是，如果我们将参数更改为a sql_variant并为其提供字符串值，会发生什么？

DECLARE @ParameterAsVariant sql_variant 
  SELECT @ParameterAsVariant ='3'
  SELECT    f.ValueAsString, f.ValueAsInt, f.ValueAsVariant
    FROM
      (
      VALUES ('one', 1, Convert(SQL_VARIANT, 1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f (ValueAsString, ValueAsInt, ValueAsVariant)
      WHERE ValueAsVariant > @ParameterAsVariant

现在它返回所有你可能不会想到的行。这里的问题是，为了评估表达式，SQL Server检查它的基类型或类型族，并将其与我们的变量类型进行比较。sql_variant的基类型系列可以是Unicode、精确数字、近似数字、日期和时间、二进制或唯一标识符，我们的ValueAsVariant列包含精确数字。

在第一个仅返回第4行和第5行的示例中，我们的参数类型与ValueAsVariant列的类型属于同一族。SQL Server执行隐式转换，代码可以正常工作。但是，在第二个示例中，我们使用sql_variant带有字符串值的参数，其中@ParameterAsVariant包含Unicode。而不是将Unicode类型隐式转换为精确数字（即“高级”数据类型），SQL Server判断高级数据类型为“更大”，因此我们的搜索条件对每一行的计算结果为true。

这显然是sql_variant的一个怪癖。如果我们比较完全相同的基本数据类型的两个sql_variant值，它将“工作”。如果我们将sql_variant与同一系列中的另一种数据类型进行比较，隐式转换将允许它工作。除此之外，一切都不可能了。

ODBC支持

ODBC不完全支持sql_variant。当使用与包含sql_variant类型的表的连接时，您会注意到这一点，因为sql_variant当您使用Microsoft OLE DB Provider for ODBC（MSDASQL）时，列中的数据将作为二进制数据（例如0x32303931）返回。

限制在索引中使用sql_variant

sql_variant仅当索引的总长度小于900字节的最大值时，才可以在索引中包含列。这意味着如果值的长度超过900个字节，则索引sql_variant列上的插入操作将失败。如果我们创建表或表变量：

DECLARE @MyTableVariable TABLE (MyProperty sql_Variant PRIMARY KEY)

我们得到一个警告：

警告！聚簇索引的最大密钥长度为900字节。索引“PK __＃B2961DC__8E45D1198BEEA325”的最大长度为8016字节。对于某些大值组合，插入或更新操作将失败。

如果我们忽略警告......

DECLARE @MyTableVariable TABLE (MyProperty sql_Variant PRIMARY KEY)
  INSERT INTO @MyTableVariable (MyProperty) 
   VALUES (N'Abbán moccu Corbmaic'),
    (N'Abel of Reims'),
    (N'Buíte [Boetius] mac Brónaig'),
    (N'Buriana'),
    (Replicate(N'Caillín [Caillén] mac Niataig  Crom mac Feradaig, Comgall mac Sétnai, Comgán mac Dá Cherda, Commán mac Fáelchon, Mo ChommócCrónán of Balla, see Mo Chua mac Bécáin',3))

我们得到错误......

Ms 1946，Level 16，State 3，Line 45

操作失败。索引“PK __＃B72883F__8E45D1191C112AAE”的长度为980字节的索引条目超过了聚簇索引的最大长度900字节。

结论

sql_variant在用户表中使用数据类型是一种代码味道，因为它将非类型化数据类型引入强类型语言，并且需要进行调查，就像您在家闻到烧焦的味道一样。它可能只是烧烤，但它可能更令人担忧。

sql_variant 具有合法用途，但总有一种风险，即尽管您可能确切知道如何使用它们，但是其他必须维护或调试代码的人可能不知道，并且如果您除了纯粹使用它们之外做任何其他事情，则最有可能导致问题用于存储。

你绝不能依赖sql_variant的隐式转换，因为它经常失败，要么是因为它没有实现，要么是因为它是奇怪的。相反，在进行比较、表达式或聚合之前，将它们显式转换为SQL数据类型。如果你不完全确定你理解了最后一句话，那么最好永远不要使用sql_variant。