在SQLServer中如何存储数据类型?sql_variant存储数据类型内容分享！

2021-09-18 17:22:40 浏览数 (3208)

这篇文章着眼于一种奇怪的数据类型，它并不是真正的数据类型。相反，sql_variant试图成为所有人的一切。与生活中的大多数事情一样，它也有一些缺点。

如果您想了解其他数据类型的存储，可以在SQLServer这个手册中进行学习，在编程数据处理中很少有人使用sql_variant数据类型，但是这不一定是件坏事。在我们的行业中有一个普遍的理念，我们假设列是一种特定的数据类型。这种可预测性使得针对这些列编写查询和应用程序变得更加容易。处理NULL值已经够难的了，因此在处理数据转换时增加混淆会导致引入错误。

对于此数据类型的官方文档（可从Microsoft Docs 获得）可以了解到：

[A] 定义为sql_variant 的列可以存储int、binary和char值 [最多] 8016 字节的最大长度。[...] 实际基类型值的最大长度为 8,000 字节。

这种数据类型的另一个方面是 ODBC 不完全支持它，因此根据您用于连接到数据库的驱动程序，底层列将以二进制形式返回。

最后一句话为我们提供了有关 SQL Server 和 Azure SQL 数据库存储引擎如何保留sql_variant列的线索。

这些值是如何存储的？

让我们看一个使用三个“基本”类型的简单示例，看看存储引擎在做什么。我们的表将有两列，第二列是我们的sql_variant列。

CREATE TABLE test•(•    col1 VARCHAR(255) NOT NULL,•    col2 SQL_VARIANT NULL•);••INSERT INTO test SELECT 'FirstName', N'Randolph'; -- NVARCHAR•INSERT INTO test SELECT 'LastName', 'West'; -- VARCHAR•INSERT INTO test SELECT 'Age', 25; -- INT•

使用DBCC INDand DBCC PAGE- 正如本系列之前的文章中所展示的 - 我们发现了一些有趣的结果。为清楚起见，从这些结果中合并了以下信息。

熟悉十六进制值的读者会认出上表中三个不同值的十六进制等效值：

  • 0x0052是 Unicode 格式的大写 R，后跟小写的“andolph”（参见存储字符串）
  • 0x57 是大写的 W，然后是常规 ANSI 中的小写“est”
  • 0x00000019是一个四字节整数，反向存储（参见存储整数）

前缀是什么意思？

我们可以立即看到前缀：0xE701401F08C00000以及0xA701401F08C00000看起来相似的字符串值和0x3801整数值。是什么赋予了？

首先，您可以运行SELECT * FROM sys.types以查看所有可能的数据类型及其匹配的system_type_id. 此十进制值转换为十六进制，并表示前缀中的第一个字节。

从整数开始，Martin Smith 在七年前的 StackOverflow回答中为我们做了很多繁重的工作：

  • 0x38( system_type_id=56) 表示一个整数的内部值
  • 0x01表示sql_variant格式的版本，1至少是 SQL Server 2008

对于字符串值，还有更多内容。让我们只看第一个字节：

  • 0xE7( system_type_id= 231) 代表 NVARCHAR
  • 0xA7( system_type_id= 167) 代表VARCHAR

至于其余的，幸运的是，我们不需要知道这些二进制值的含义，因为有扩展属性可以sql_variant使用以下查询为我们分解它，替换我们之前创建的表：

SELECT SQL_VARIANT_PROPERTY(col2, 'BaseType' ) 
AS [BaseType], • SQL_VARIANT_PROPERTY(col2, 'Precision' ) AS [ Precision ], •  SQL_VARIANT_PROPERTY(col2, 'Scale' ) AS [Scale], •  SQL_VARIANT_PROPERTY(col2, 'Byte ) AS [TotalBytes], •  SQL_VARIANT_PROPERTY(col2, 'Collat​​ion' ) AS [Collat​​ion], •  SQL_VARIANT_PROPERTY(col2, 'MaxLength' ) 
AS[最大长度] •从dbo .test ;

以下结果是这些二进制值的编码结果。

概括

我从不使用sql_variant，但了解在升级现有系统或在灾难恢复期间检查数据页时要查找的内容很有用。另请记住，某些内部 SQL Server 系统表和系统存储过程使用此数据类型来混合字符串和数字。数据类型有它的位置，但不利于良好的索引或可预测的代码。每列还有一个存储开销，特别是对于字符串值。