SQL Server 2008中的資料表壓縮功能詳細介紹_mssql2008

SQL Server 2005 SP2為我們帶來了vardecimal功能，當時針對decimail和numeric資料類型推出了新的儲存格式--vardecimal。vardecimal儲存格式允許 decimal和numeric資料類型的儲存作為一個可變長度列。 這項功能使得原來定長的decimal資料在資料檔案中以可變長的格式儲存，據稱這項功能可以為典型的資料倉儲節省30%的空間，而SQL Server 2008在這一基礎上又進一步增強了資料壓縮功能。SQL Server 2008現在支援行壓縮和頁面壓縮兩種選項，資料壓縮選項可以在以下對象上啟用：

1.未建立聚簇索引的表
2.建立聚簇索引的表
3.非聚簇索引(對錶設定壓縮選項不會影響到該表上的非聚簇索引，因此聚簇索引的壓縮需要單獨設定)
4.索引檢視表
5.分區表和分區索引中的單個分區

使用資料壓縮的意義：

首先讓我們來看看，為什麼在儲存成本不斷降低的今天，微軟還要煞費苦心地在SQL Server中實現並且不斷改進資料壓縮技術呢？

儘管儲存成本已經不再是傳統意義上的首要考慮因素，但是這並不代表資料庫尺寸不是一個問題，因為資料庫尺寸除了會影響到儲存成本之外，還極大地關聯到管理成本和效能問題。

致於為什麼會有管理成本的問題？因為資料庫需要備份，資料庫的尺寸越大，那麼備份時間就會越長，當然另外一點就是消耗的備份硬體成本也會隨之提高(包括需要的備份介質成本和為了滿足備份視窗而需要更進階的備份裝置帶來的採購成本)，還有一種管理成本就是資料庫的維護成本，例如我們經常需要完成的 DBCC任務，資料庫尺寸越大，我們就需要更多的時間來完成這些任務。

接著我們再看看效能問題。SQL Server在掃描磁碟讀取資料的時候都是按照資料頁為單位進行讀取的，因此如果一張資料頁中包含的資料行數越多，SQL Server在一次資料頁IO中獲得的資料就會越多，這樣也就帶來了效能的提升。

最後考慮儲存的成本，按照原先SQL Server 2005 SP2中vardecimal的壓縮資料為例，30%的空間節省也就意味著30%的儲存成本，而按照SQL Server 2008當前放出的測試資料，採用新的資料壓縮技術可以達到2X-7X的儲存率，再加上如果企業要考慮容災而增加的儲存空間，這樣節省的儲存硬體成本也將是想當可觀的。

使用資料壓縮方法：

SQL Server 2008中的壓縮選項可以在建立表或索引時通過Option進行設定，例如：

複製代碼 代碼如下:

CREATE TABLE TestTable (col1 int, col2 varchar(200)) WITH (DATA_COMPRESSION = ROW);

如果需要改變一個分區的壓縮選項，則可以用以下語句：

複製代碼 代碼如下:

ALTER TABLE TestTable REBUILD PARTITION = 1 WITH (DATA COMPRESSION = PAGE);

如果需要為分區表的各個分區設定不同的壓縮選項，可以使用以下的語句：(SQL Server 2008可以對不同的分區使用不同的壓縮選項，這一點對於資料倉儲應用是非常重要的，因為資料倉儲的事實表通常都會有一個或數個熱分區，這些分區中的資料經常需要更新，為了避免資料壓縮給這些分區上的資料更新帶來額外的處理載荷，可以對這些分區關閉壓縮選項)

複製代碼 代碼如下:

CREATE TABLE PartitionedTable (col1 int, col2 varchar(200))
ON PS1 (col1)
WITH (
DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITION(2 TO 4));

如果是為某個索引設定壓縮選項的話，可以使用：

複製代碼 代碼如下:

CREATE INDEX IX_TestTable_Col1 ON TestTable (Col1) WITH (DATA_COMPRESSION = ROW);

如果是修改某個索引的壓縮選項，可以使用：

複製代碼 代碼如下:

ALTER INDEX IX_TestTable_Col1 ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);

SQL Server 2008同時還提供了一個名為sp_estimate_data_compression_savings預存程序協助DBA估計啟用壓縮選項後對象尺寸。

使用資料壓縮的原理：

對於行壓縮，SQL Server 2008採用以下三種方法來節省儲存空間：
減少了與記錄相關聯的中繼資料開銷。此中繼資料為有關列、列長度和位移量的資訊。在某些情況下，中繼資料開銷可能大於舊的儲存格式。

它對於數實值型別（例如，integer、decimal和float）和基於數值的類型（例如，datetime和money）使用可變長度儲存格式。

它通過使用不儲存Null 字元的可變長度格式來儲存定長字串。
對於頁面壓縮，SQL Server 2008則是在一張資料頁面上依次採用：

1.行壓縮
2.首碼壓縮
3.字典壓縮

使用資料壓縮注意事項：

儘管SQL Server 2008的資料壓縮功能非常有價值，但是仍然需要注意一些問題：
資料壓縮功能僅在企業版和開發版中可用

資料壓縮可以讓一張資料頁儲存更多的資料行，但是並不能改變單行資料最長8060位元組這一限制。

在一張已經設定了資料壓縮的表上建立聚簇索引時，聚簇索引預設繼承原表上的壓縮選項

在未設定聚簇索引的表上設定頁面壓縮時，只有以下情況才會獲得頁面壓縮的實際效果：

1.資料使用BULK INSERT文法添加到表中
2.資料使用INSERT INTO ... WITH (TABLOCK)文法添加到表中
3.執行帶有頁面壓縮選項的ALTER TABLE ... REBUILD命令

在未設定聚簇索引的表上更改壓縮選項，會導致該表上所有非聚簇索引都需要重建，因為這些非聚簇索引指向的資料行地址已經都發生了改變。

在改變壓縮選項時所需要的臨時空間大小與建立索引是所需要的空間是一樣的，因此對於分區表，我們可以逐個分區設定壓縮選項來減少臨時空間的需求壓力。

由於SQL Server 2008中資料壓縮技術其實是SQL Server 2005 SP2中vardecimal技術的一個超集，因此設定了資料壓縮後就沒有必要保留vardecimal了。當然SQL Server 2008為了保持向後相容性，在目前的版本中仍然保留了vardecimal，但是SQL Server 2008的下一個版本及可能就會棄用vardecimal選項，因此做了這些設定的資料庫應該儘早改變到資料壓縮設定下。

SQL Server 2008的壓縮選項是工作在儲存引擎層的，對於SQL Server的其他組件來說這一特性是透明的，因此當我們用BULK LOAD的方式將外面的資料匯入SQL Server時，會顯著的增加CPU的工作載荷，同時將以壓縮的資料表匯出到外部檔案時，可能會消耗比原來多很多的空間。

相信到了這裡，朋友們對於SQL Server 2008中的資料壓縮技術有了一個較為全面的瞭解。