App下載

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

流蘇書包 2021-09-18 17:22:40 瀏覽數(shù) (3358)
反饋

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

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

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

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

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

最后一句話為我們提供了有關(guān) SQL Server 和 Azure SQL 數(shù)據(jù)庫(kù)存儲(chǔ)引擎如何保留sql_variant列的線索。

這些值是如何存儲(chǔ)的？

讓我們看一個(gè)使用三個(gè)“基本”類型的簡(jiǎn)單示例，看看存儲(chǔ)引擎在做什么。我們的表將有兩列，第二列是我們的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àn)，從這些結(jié)果中合并了以下信息。

熟悉十六進(jìn)制值的讀者會(huì)認(rèn)出上表中三個(gè)不同值的十六進(jìn)制等效值：

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

前綴是什么意思？

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

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

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

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

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

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

至于其余的，幸運(yùn)的是，我們不需要知道這些二進(jìn)制值的含義，因?yàn)橛袛U(kuò)展屬性可以sql_variant使用以下查詢?yōu)槲覀兎纸馑?，替換我們之前創(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[最大長(zhǎng)度] ?從dbo .test ;

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

概括

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


SQL 大數(shù)據(jù)

0 人點(diǎn)贊