事件
3月31日 下午11時 - 4月2日 下午11時
最大的 SQL、網狀架構和 Power BI 學習事件。 3 月 31 日 - 4 月 2 日。 使用程式代碼 FABINSIDER 來節省 $400 美元。
立即註冊使用稀疏欄位
適用於：
SQL Server 2016 （13.x）和更新版本，Azure SQL DatabaseAzure SQL 受控實例Microsoft Fabric 中的 SQL 資料庫
稀疏欄位是經過最佳化用來儲存 Null 值的一般欄位。 稀疏欄位減少了 Null 值的空間需求，但增加了擷取非 NULL 值時的額外負擔。 當節省的空間至少達到百分之 20 到 40% 時，請考慮使用稀疏資料行。 疏鬆資料行和資料行集是使用 CREATE TABLE 或 ALTER TABLE 陳述式所定義。
稀疏資料行可以搭配資料行集和篩選的索引使用。
欄位集
INSERT、UPDATE 和 DELETE 陳述式可以依名稱參考過疏欄位。 但是，您也可以檢視及處理資料表中合併為一個 XML 資料行的所有稀疏欄位。 這個欄位稱為欄位集。 如需資料行集的詳細資訊，請參閱 使用資料行集。
篩選的索引
由於稀疏欄位有許多 Null 值的資料列，所以它們特別適合用於篩選索引。 稀疏欄上的篩選索引只能對具有填入值的資料列進行索引。 這樣會建立更小且更有效率的索引。 如需詳細資訊，請參閱 Create Filtered Indexes。
稀疏資料欄和篩選索引讓應用程式（如 Windows SharePoint Services）可以利用 SQL Server 有效率地儲存及存取大量使用者自訂屬性。
稀疏欄位具有下列特性：
SQL Server 資料庫引擎會使用資料行定義中的 SPARSE 關鍵字，最佳化該資料行中值的儲存方式。 因此，當資料表中任何資料列的資料行值是 NULL 時，這些值就不需要儲存位置。
具有稀疏資料行之資料表的檢視角度和一般資料表相同。
sys.columns目錄檢視包含資料表中每一個資料行的資料列，而且也包含資料行集 (如果有定義的話)。疏鬆資料行是儲存層而非邏輯資料表的屬性。 因此，
SELECT ... INTO陳述式不會將疏鬆資料行屬性複製到新資料表中。COLUMNS_UPDATED 函數會傳回 varbinary 值，指示 DML 動作期間已更新所有資料行。 COLUMNS_UPDATED 函數傳回的位元如下：
當明確更新疏鬆資料行時，該疏鬆資料行的對應位元會設定為 1，而資料行集的位元也會設定為 1。
當明確更新資料行集時，此資料行集的位元會設定為 1，而該資料表中所有疏鬆資料行的位元也會設定為 1。
若為插入作業，所有位元都會設定為 1。
如需資料行集的詳細資訊，請參閱 使用資料行集。
下列資料類型無法指定為 SPARSE：
地理位置
幾何
image
ntext
text
timestamp
使用者定義資料類型
與未標示為 SPARSE 的相同資料所需空間相比，稀疏欄需要更多的儲存空間來儲存非 NULL 值。 下表顯示每一個資料類型的空間使用。 NULL 百分比 資料行指出資料必須有多少百分比為 NULL，空間的淨節省才會是百分之 40。
固定長度資料類型
| 資料類型 | 非疏鬆位元組 | 稀疏位元組 | 空 百分比 |
|---|---|---|---|
| bit | 0.125 | 5 | 98% |
| tinyint | 1 | 5 | 86% |
| smallint | 2 | 6 | 76% |
| int | 4 | 8 | 64% |
| bigint | 8 | 12 | 52% |
| 真實 | 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% |
精度決定的長度資料類型
| 資料類型 | 非稀疏位元組 | 稀疏字節 | 無百分比 |
|---|---|---|---|
| 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% |
| 日期時間偏移量 (7) | 10 | 14 | 49% |
| 十進位/數值型(1,s) | 5 | 9 | 60% |
| decimal/numeric(38,s) | 17 | 21 | 42% |
| vardecimal(p,s) | 使用 decimal 類型作為保守估計。 |
與資料相關的長度資料類型
| 資料類型 | 非稀疏位元組 | 稀疏位元組 | NULL 百分比 |
|---|---|---|---|
| sql_variant | 隨著基礎資料類型而不同 | ||
| varchar 或 char | 2* | 4* | 60% |
| nvarchar 或 nchar | 2* | 4*+ | 60% |
| varbinary 或 binary | 2* | 4* | 60% |
| xml | 2* | 4* | 60% |
| hierarchyid | 2* | 4* | 60% |
*長度等於該類型所包含之資料的平均值，加上 2 或 4 個位元組。
設計具有疏鬆資料行的資料表時，請記住，如果要更新資料列，則資料表中的每個非 Null 疏鬆資料行都需要額外 2 個位元組的負擔。 由於額外的記憶體開銷，當資料列大小總計（包括此記憶體開銷）超過8019，且無資料行可從資料列移除時，更新可能會因錯誤576而意外失敗。
假設某個資料表具有 600 個 bigint 類型的稀疏欄位。 如果共有 571 個非 Null 資料行，則磁碟的大小總計就是 571 * 12 = 6852 個位元組。 加入額外資料列負擔和疏鬆資料行標頭之後，這個大小會增加至大約 6895 個位元組。 此頁面在磁碟上仍有大約 1124 個位元組的可用空間。 這可能會讓您以為其他資料行都可順利更新。 不過，在更新期間，記憶體中存在額外負擔，也就是 2*(非 Null 疏鬆資料行的數目)。 在此範例中，新增額外負荷 (2 * 571 = 1142 個位元組) 會將磁碟的資料列大小增加至大約 8037 個位元組。 這個大小超過了允許的大小上限：8019 個位元組。 因為所有資料行都是固定長度的資料類型，所以它們無法超出資料列的限制。 因此，更新就會失敗並發生 576 錯誤。
疏鬆資料行可具有任何 SQL Server 資料類型，而且其行為就像其他任何資料行一樣，但是有下列限制：
稀疏列必須允許為 Null，且不能有 ROWGUIDCOL 或 IDENTITY 屬性。 疏鬆資料行不能是以下資料類型：text、ntext、image、timestamp、使用者定義的資料類型、geometry 或 geography；或具有 FILESTREAM 屬性。
稀疏欄位不能有預設值。
稀疏欄無法繫結至規則。
雖然計算資料行可以包含稀疏資料行，但是計算資料行不能標示為 SPARSE。
可以在稀疏資料行上定義資料遮罩，但不能在屬於資料行集的稀疏資料行上定義資料遮罩。
稀疏欄位不能作為叢集索引或唯一主鍵索引的一部分。 但是，定義於稀疏欄位上的持久及非持久計算欄位都可以是叢集索引鍵的一部分。
稀疏欄位不能用作叢集索引或堆積的分區鍵。 但是，稀疏資料行可以作為非叢集索引的分區鍵使用。
稀疏資料行不能成為使用者定義的資料表類型的一部分，這些資料表類型用於資料表變數和表值參數中。
稀疏欄位與資料壓縮不相容。 因此，您無法將疏鬆資料行加入至壓縮的資料表，也無法壓縮包含疏鬆資料行的任何資料表。
將資料行從疏鬆變更為非疏鬆 (或相反) 需要變更資料行的儲存格式。 SQL Server Database Engine 會使用下列程序來達成這項變更：
以新的儲存大小和格式，將新的資料行加入至資料表。
針對資料表中的每個資料列，將儲存在舊資料行中的值更新並複製到新的資料行。
從資料表結構描述中移除舊的資料行。
重建資料表 (如果沒有叢集索引)，或重建叢集索引以回收舊資料行使用的空間。
注意
當資料列中的資料大小超過允許的資料列大小上限時，步驟 2 可能會失敗。 這個大小包括儲存在舊資料行中的資料大小以及儲存在新資料行中的更新資料大小。 對於不包含任何疏鬆資料行的資料表而言，這個限制為 8060 個位元組。對於包含疏鬆資料行的資料表而言，這個限制為 8018 個位元組。 即使所有適合的資料行都已經被移到資料列外，這項錯誤仍然可能會發生。
當您將非疏鬆資料行變更為疏鬆資料行時，此疏鬆資料行將會耗用更多的空間來儲存非 Null 值。 當資料列很接近資料列大小的上限時，此作業可能會失敗。
本節說明下列 SQL Server 技術如何支援疏鬆資料行：
交易式複寫
事務性複寫支援稀疏欄位，但不支援資料行集，不過資料行集可搭配稀疏欄位使用。 如需資料行集的詳細資訊，請參閱 使用資料行集。
SPARSE 屬性的複寫是由使用 sp_addarticle 或使用 SQL Server Management Studio 中的 [發行項屬性] 對話方塊所指定的結構描述選項決定。 舊版的 SQL Server 不支援疏鬆資料行。 如果您必須將資料複寫到舊版，請指定不應該複寫 SPARSE 屬性。
如果是發行的資料表，您不能將任何新的疏鬆資料行加入資料表，或是變更現有資料行的疏鬆屬性。 如果需要進行這類作業，請卸除發行集再重新建立。
合併複製
合併複寫不支援稀疏資料行或資料行集。
變更追蹤
變更追蹤可支援疏鬆資料行和資料行集。 在資料表中更新資料行集時，變更追蹤會將此動作視為整個資料列的更新。 不會提供任何詳細的變更追蹤來取得透過欄位集更新操作所更新的具體稀疏欄位集合。 如果透過 DML 陳述式明確更新疏鬆資料行，針對這些資料行的變更追蹤功能將會正常運作，而且可以識別確切的變更資料行集合。
變更資料擷取
異動資料擷取可支援稀疏資料行，但是不支援資料行集。
複製資料表時，不會保留欄位的稀疏屬性。
在此範例中，文件資料表包含常用的一組 DocID 和 Title資料行。 生產小組想要所有生產文件的 ProductionSpecification 和 ProductionLocation 欄。 行銷小組希望為行銷文件設置一個 MarketingSurveyGroup 欄。 此範例的程式碼會建立一個使用疏鬆資料行的資料表，並將兩個資料列插入資料表中，然後選取資料表中的資料。
注意
此資料表只有五個資料行，以方便顯示及閱讀。 如果設定了 ANSI_NULL_DFLT_ON 選項，則將稀疏資料行宣告為可為空是選擇性的。 當 SET ANSI_DEFAULTS 是 ON 時，會啟用 SET ANSI_NULL_DFLT_ON。 大部分連線提供者的 ANSI_DEFAULTS 都預設為 ON。 如需詳細資訊，請參閱 SET ANSI_DEFAULTS。
SQL
USE AdventureWorks2022;
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
從資料表中選取所有資料行將會傳回一般的結果集。
SQL
SELECT * FROM DocumentStore ;
結果集如下所示。
DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup
1 Tire Spec 1 AXZZ217 27 NULL
2 Survey 2142 NULL NULL Men 25-35
由於生產部門對行銷資料不感興趣，因此他們想使用一個只會傳回感興趣資料行的清單，如下列查詢中所示。
SQL
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStore
WHERE ProductionSpecification IS NOT NULL ;
結果集如下所示。
DocID Title ProductionSpecification ProductionLocation
1 Tire Spec 1 AXZZ217 27
其他資源
訓練
模組
使用 Azure Data Studio 在 Azure SQL Database 中設計高效能資料模型 - Training
了解如何建立資料模型、資料表、索引、條件約束，以及搭配 Azure Data Studio 使用資料類型。
文件
-
使用資料行集來指定一個資料行集，以傳回資料表中的所有稀疏資料行。 資料行集是不具類型的 XML 表示，可將資料表的所有疏鬆資料行結合到結構化輸出中。
-
在 INSERT 和 UPDATE 語句中停用外鍵約束 - SQL Server
停用 INSERT 和 UPDATE 陳述式的外部索引鍵條件約束
-
CREATE INDEX (Transact-SQL) - SQL Server
CREATE INDEX (Transact-SQL)