logo SQL Prompt教程 我也要发布文档

SQL语法提示工具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数据类型。

word-image-42.png

与许多“最佳实践”规则一样,这些建议有时听起来像是告诉人们在拿着剪刀时不要跑。在这种情况下,只有在使用数据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数据类型,所以不可能有技术问题阻止其他函数工作。

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

相关文章:

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

1563778777.jpg