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

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

点击下载SQL Prompt免费版

本篇文章说明了SQL_VARIANT数据类型的“怪癖”，以及为什么最好调查SQL Prompt何时提醒您使用它。如果在使用之前将其显式转换为真实类型，那么将数据存储为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。

本教程内容完结了，相关内容请看下面的文章，后续还会更新内容哦~

彩票走势图相关的文章：

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微信公众号，及时获取最新动态及最新资讯