在SQLServer中如何存儲數(shù)據(jù)類型?sql_variant存儲數(shù)據(jù)類型內(nèi)容分享！

這篇文章著眼于一種奇怪的數(shù)據(jù)類型，它并不是真正的數(shù)據(jù)類型。相反，sql_variant試圖成為所有人的一切。與生活中的大多數(shù)事情一樣，它也有一些缺點。

如果您想了解其他數(shù)據(jù)類型的存儲，可以在SQLServer這個手冊中進行學(xué)習(xí)，在編程數(shù)據(jù)處理中很少有人使用sql_variant數(shù)據(jù)類型，但是這不一定是件壞事。在我們的行業(yè)中有一個普遍的理念，我們假設(shè)列是一種特定的數(shù)據(jù)類型。這種可預(yù)測性使得針對這些列編寫查詢和應(yīng)用程序變得更加容易。處理NULL值已經(jīng)夠難的了，因此在處理數(shù)據(jù)轉(zhuǎn)換時增加混淆會導(dǎo)致引入錯誤。

對于此數(shù)據(jù)類型的官方文檔（可從Microsoft Docs 獲得）可以了解到：

[A] 定義為sql_variant 的列可以存儲int、binary和char值 [最多] 8016 字節(jié)的最大長度。[...] 實際基類型值的最大長度為 8,000 字節(jié)。

這種數(shù)據(jù)類型的另一個方面是 ODBC 不完全支持它，因此根據(jù)您用于連接到數(shù)據(jù)庫的驅(qū)動程序，底層列將以二進制形式返回。

最后一句話為我們提供了有關(guān) SQL Server 和 Azure SQL 數(shù)據(jù)庫存儲引擎如何保留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- 正如本系列之前的文章中所展示的 - 我們發(fā)現(xiàn)了一些有趣的結(jié)果。為清楚起見，從這些結(jié)果中合并了以下信息。

熟悉十六進制值的讀者會認出上表中三個不同值的十六進制等效值：

• 0x0052是 Unicode 格式的大寫 R，后跟小寫的“andolph”（參見存儲字符串）
• 0x57 是大寫的 W，然后是常規(guī) ANSI 中的小寫“est”
• 0x00000019是一個四字節(jié)整數(shù)，反向存儲（參見存儲整數(shù)）

前綴是什么意思？

我們可以立即看到前綴：0xE701401F08C00000以及0xA701401F08C00000看起來相似的字符串值和0x3801整數(shù)值。是什么賦予了？

首先，您可以運行SELECT * FROM sys.types以查看所有可能的數(shù)據(jù)類型及其匹配的system_type_id. 此十進制值轉(zhuǎn)換為十六進制，并表示前綴中的第一個字節(jié)。

從整數(shù)開始，Martin Smith 在七年前的 StackOverflow回答中為我們做了很多繁重的工作：

• 0x38( system_type_id=56) 表示一個整數(shù)的內(nèi)部值
• 0x01表示sql_variant格式的版本，1至少是 SQL Server 2008

對于字符串值，還有更多內(nèi)容。讓我們只看第一個字節(jié)：

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

至于其余的，幸運的是，我們不需要知道這些二進制值的含義，因為有擴展屬性可以sql_variant使用以下查詢?yōu)槲覀兎纸馑鎿Q我們之前創(chuàng)建的表：

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 ;

以下結(jié)果是這些二進制值的編碼結(jié)果。

概括

我從不使用sql_variant，但了解在升級現(xiàn)有系統(tǒng)或在災(zāi)難恢復(fù)期間檢查數(shù)據(jù)頁時要查找的內(nèi)容很有用。另請記住，某些內(nèi)部 SQL Server 系統(tǒng)表和系統(tǒng)存儲過程使用此數(shù)據(jù)類型來混合字符串和數(shù)字。數(shù)據(jù)類型有它的位置，但不利于良好的索引或可預(yù)測的代碼。每列還有一個存儲開銷，特別是對于字符串值。