SQL Server 2008稀疏列的使用

稀疏列是對 null 值採用優化的儲存方式的普通列。稀疏列減少了 null 值的空間需求，但代價是檢索非 null 值的開銷增加。當至少能夠節省 20% 到 40% 的空間時，才應考慮使用稀疏列。稀疏列和列集是使用 CREATE TABLE 或 ALTER TABLE 語句來定義的。

稀疏列可以與列集和篩選索引一起使用：

列集
INSERT、UPDATE 和 DELETE 語句可以按名稱來引用稀疏列。但是，您也可以檢視並處理表中的所有稀疏列，這些列組合為一個 XML 列，此列稱為列集。有關列集的詳細資訊，請參閱使用列集。

篩選索引
因為稀疏列有許多 null 值行，所以尤其適用於篩選索引。稀疏列的篩選索引可以僅對已填充值的行編制索引。這會建立一個更小更有效的索引。有關詳細資訊，請參閱篩選索引設計準則。

稀疏列和篩選索引使應用程式（如 Windows SharePoint Services）可以有效地通過 SQL Server 2008 來儲存和訪問大量的使用者定義屬性。

 稀疏列的屬性
稀疏列具有以下特徵：

SQL Server 資料庫引擎在列定義中使用 SPARSE 關鍵字來優化該列中的值的儲存。因此，當表中的任意行的列值為 NULL 時，該值將不需要儲存空間。

具有稀疏列的表的目錄檢視與典型表的目錄檢視相同。sys.columns 目錄檢視對於表中的每一列都包含一個對應的行，如果已定義列集，還會包含列集。

COLUMNS_UPDATED 函式返回一個 varbinary 值，指示在 DML 操作期間更新的所有列。COLUMNS_UPDATED 函式返回的位如下：

顯式更新稀疏列後，該稀疏列的對應位將設定為 1，列集的對應位將設定為 1。

顯式更新列集後，列集的對應位將設定為 1，該表中的所有稀疏列的對應位將設定為 1。

對於插入操作，所有位都將設定為 1。

有關列集的詳細資訊，請參閱使用列集。

下面的資料型別不能指定為 SPARSE：

geography
 text
 
geometry
 timestamp
 
image
 user-defined data types
 
ntext
 
 

 按資料型別估算的空間節省量
與未標記為 SPARSE 的相同資料相比，稀疏列在儲存非 null 值時需要的儲存空間更多。下表說明了每種資料型別的空間使用情況。NULL 百分比列指示資料中 NULL 值所佔的百分比必須達到多少才能淨節省 40% 的空間。

固定長度的資料型別

資料型別  非稀疏位元組  稀疏位元組  NULL 百分比 
bit
 0.125
 4.125
 98%
 
tinyint
 1
 5
 86%
 
smallint
 2
 6
 76%
 
int
 4
 8
 64%
 
bigint
 8
 12
 52%
 
real
 4
 8
 64%
 
float
 8
 12
 52%
 
smallmoney
 4
 8
 64%
 
money
 8
 12
 52%
 
smalldatetime
 4
 8
 64%
 
datetime
 8
 12
 52%
 
uniqueidentifier
 16
 20
 43%
 
date
 3
 7
 69%
 

長度依賴於精度的資料型別

資料型別  非稀疏位元組  稀疏位元組  NULL 百分比 
datetime2(0)
 6
 10
 57%
 
datetime2(7)
 8
 12
 52%
 
time(0)
 3
 7
 69%
 
time(7)
 5
 9
 60%
 
datetimetoffset(0)
 8
 12
 52%
 
datetimetoffset (7)
 10
 14
 49%
 
decimal/numeric(1,s)
 5
 9
 60%
 
decimal/numeric(38,s)
 17
 21
 42%
 
vardecimal(p,s)
 使用 decimal 型別作為保守的估計。
 
 
 

長度依賴於資料的資料型別

資料型別  非稀疏位元組  稀疏位元組  NULL 百分比 
sql_variant
 因基礎資料型別而異
 
 
 
varchar 或char
 4+平均資料
 2+平均資料
 60%
 
nvarchar 或nchar
 4+平均資料
 2+平均資料
 60%
 
varbinary 或binary
 4+平均資料
 2+平均資料
 60%
 
xml
 4+平均資料
 2+平均資料
 60%
 
hierarchyId
 4+平均資料
 2+平均資料
 60%
 

 使用稀疏列的限制
稀疏列可以是任何 SQL Server 資料型別，其行為與其他任何列類似，但有以下限制：

稀疏列必須可為 null，並且不能有 ROWGUIDCOL 或 IDENTITY 屬性。稀疏列可以為以下資料型別：text、ntext、image、timestamp、使用者定義的資料型別、geometry 或 geography；或者具有 FILESTREAM 屬性。

稀疏列不能有預設值。

稀疏列不能繫結到規則。

儘管計算列可以包含稀疏列，但計算列不能標記為 SPARSE。

稀疏列不能是聚集索引或唯一主鍵索引的一部分。但是，對稀疏列定義的持久化和非持久化計算列可以是聚集鍵的一部分。

稀疏列不能用作聚集索引或堆的分割槽鍵。但是，稀疏列可以用作非聚集索引的分割槽鍵。

稀疏列不能是使用者定義的表型別的一部分，使用者定義的表型別用在表變數和表值引數中。

使用稀疏列，行大小的最大值從 8,060 位元組減少到 8,018 位元組。

當行大小接近 4,009 位元組時，新增或刪除稀疏列會失敗。當修改現有表以新增第一個稀疏列時，將通過在資料頁上建立行的副本，然後刪除原始行來修改行。當呈現現有行時，大於 4,009 位元組的行無法寫入頁。這會導致新增列失敗。
當從表中刪除最後一個稀疏列時，存在同樣的問題。如果有任意行超過 4,009 位元組，則刪除列將會失敗。此限制不適用於不包含資料的新表。
若要更改每行資料超過 4,009 位元組的表中的行，應建立一個新表，然後將資料轉移到新表中。然後，刪除原始表並重新命名此新表；也可以截斷原始表，修改原始表中的行，然後將資料移回到原始表中。

將非稀疏列改為稀疏列時，稀疏列將佔用更多的空間來儲存非 null 值。當行接近最大行大小的限制時，操作將失敗。

 支援稀疏列的 SQL Server 技術
本部分介紹下列 SQL Server 技術如何支援稀疏列：

事務複製
事務複製支援稀疏列，但它不支援可以與稀疏列一起使用的列集。有關列集的詳細資訊，請參閱使用列集。
SPARSE 屬性的複製由使用 sp_addarticle 或者使用 SQL Server Management Studio 中的“專案屬性”對話方塊指定的架構選項來決定。SQL Server 的早期版本不支援稀疏列。如果必須將資料複製到早期版本，需指定不應複製 SPARSE 屬性。
對於已釋出的表，不能向表中新增任何新的稀疏列，也不能更改現有列的稀疏屬性。如果需要執行此類操作，則應刪除釋出再重新建立。

合併複製
合併複製不支援稀疏列或列集。

更改跟蹤
更改跟蹤支援稀疏列和列集。當在表中更新列集時，更改跟蹤將此視為對整個行的更新。不提供詳細的更改跟蹤，無法準確獲取通過列集更新操作更新的稀疏列的集合。如果通過 DML 語句顯式更新稀疏列，則這些稀疏列上的更改跟蹤將像平常一樣工作，並且可以準確地識別出已更改的列的集合。

變更資料捕獲
變更資料捕獲支援稀疏列，但不支援列集。

 示例
在本示例中，文件表包含具有 DocID 和 Title 列的列集。生產組希望所有生產文件都有一個 ProductionSpecification 列和一個 ProductionLocation 列。市場組希望所有市場文件都有一個 MarketingSurveyGroup 列。本示例中的程式碼建立一個使用稀疏列的表，向該表中插入兩行，然後從該表中選擇資料。

注意：
該表只有五列，以便易於顯示和讀取。如果設定了 ANSI_NULL_DFLT_ON 選項，則將稀疏列宣告為可為 null 是可選的。
 

 複製程式碼
USE AdventureWorks
GO

CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO若要從表中選擇所有列，則應返回普通的結果集。

 複製程式碼
SELECT * FROM DocumentStore ;下面是結果集：

DocID  Title        ProductionSpecification  ProductionLocation  MarketingSurveyGroup

1      Tire Spec 1  AXZZ217                  27                  NULL

2      Survey 2142  NULL                     NULL                Men 25-35

因為生產部門對市場資料不感興趣，所以他們希望使用一個僅返回感興趣的列的列列表，如下面的查詢所示。

 複製程式碼
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStore
WHERE ProductionSpecification IS NOT NULL ;下面是結果集：

DocID  Title        ProductionSpecification  ProductionLocation

1      Tire Spec 1  AXZZ217                  27

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-513657/，如需轉載，請註明出處，否則將追究法律責任。