索引存儲

對于索引存儲，2008開始有兩種優化方式，都是通過去除重複的資料減少存放的空間，使得原有的存放空間減少。更少的空間意味着更少的頁，更少的頁意味着查詢過程中更少的I/O請求。分别為行壓縮和頁壓縮

1.行壓縮

第一種：降低行的體積。行壓縮通過改變行的存儲形式來達到目的，可以用在堆或者B_Tree上，啟用行壓縮，對應的一下功能會被啟用。

行的原資料該表

定長資料會以變長格式存放

數值型資料類型也會存放成變長格式

--建立兩個測試表作為對比

IF OBJECT_ID('dbo.NoCompression') IS NOT NULL
    DROP TABLE dbo.NoCompression
IF OBJECT_ID('dbo.RowCompression') IS NOT NULL
    DROP TABLE dbo.RowCompression
SELECT  SalesOrderID ,
        SalesOrderDetailID ,
        CarrierTrackingNumber ,
        OrderQty ,
        ProductID ,
        SpecialOfferID ,
        UnitPrice ,
        UnitPriceDiscount ,
        LineTotal ,
        rowguid ,
        ModifiedDate
INTO    dbo.NoCompression
FROM    Sales.SalesOrderDetail
SELECT  SalesOrderID ,
        SalesOrderDetailID ,
        CarrierTrackingNumber ,
        OrderQty ,
        ProductID ,
        SpecialOfferID ,
        UnitPrice ,
        UnitPriceDiscount ,
        LineTotal ,
        rowguid ,
        ModifiedDate
INTO    dbo.RowCompression
FROM    Sales.SalesOrderDetail      

 壓縮功能是在CREATE 或ALTER INDEX語句的DATA_COMPRESSION上實作，壓縮可以用于聚集和非聚集索引上。

下面的語句是行壓縮，大概壓縮了33%的空間

--沒有壓縮
CREATE CLUSTERED INDEX CLIX_NoCompression ON dbo.NoCompression
(SalesOrderID, SalesOrderDetailID);
--行壓縮
CREATE CLUSTERED INDEX CLIX_RowCompression ON dbo.RowCompression
(SalesOrderID, SalesOrderDetailID)
WITH (DATA_COMPRESSION = ROW);
--檢查使用的頁
SELECT  OBJECT_NAME(object_id) AS table_name ,
        in_row_reserved_page_count
FROM    sys.dm_db_partition_stats
WHERE   object_id IN ( OBJECT_ID('dbo.NoCompression'),
                       OBJECT_ID('dbo.RowCompression') )      

 有無行壓縮的頁數：

壓縮并不僅僅帶來存儲空間的減少，還能通過減少資料頁的方式提高查詢性能。

SET STATISTICS IO ON 
             SELECT SalesOrderID,SalesOrderDetailID ,
                     CarrierTrackingNumber  FROM dbo.NoCompression
             WHERE salesorderID BETWEEN 51500 AND 5200

             SELECT SalesOrderID,SalesOrderDetailID ,
                     CarrierTrackingNumber  FROM dbo.RowCompression
             WHERE salesorderID BETWEEN 51500 AND 5200
             SET STATISTICS IO OFF      

 對進行壓縮時，需要考慮以下情況

1.壓縮的前提是應該是對大表進行操作，

2.如果最大的函數超過了8060bytes，則壓縮不能進行

3.非聚集索引不會繼承堆或者聚集索引上的壓縮位置。每個都需要手動進行

4.壓縮時高頻CPU開銷的操作，不能頻繁的進行

二。頁壓縮

頁壓縮也可以在堆和B-Tree結構中進行。頁壓縮通常比行壓縮更有效，因為他包含了行壓縮，字首壓縮和字典壓縮。

在進行頁壓縮前，會先進行行壓縮，然後在對頁中字首相同的資料進行壓縮。

--建立測試表：

IF OBJECT_ID('dbo.PageCompression') IS NOT NULL
    DROP TABLE dbo.PageCompression
SELECT SalesOrderID
    ,SalesOrderDetailID
    ,CarrierTrackingNumber
    ,OrderQty
    ,ProductID
    ,SpecialOfferID
    ,UnitPrice
    ,UnitPriceDiscount
    ,LineTotal
    ,rowguid
    ,ModifiedDate
INTO dbo.PageCompression
FROM Sales.SalesOrderDetail      

 進行壓縮處理：

CREATE CLUSTERED INDEX CLIX_PageCompression ON dbo.PageCompression
(SalesOrderID, SalesOrderDetailID)
WITH (DATA_COMPRESSION = PAGE);
SELECT  OBJECT_NAME(object_id) AS table_name ,
        in_row_reserved_page_count
FROM    sys.dm_db_partition_stats
WHERE   object_id IN ( OBJECT_ID('dbo.NoCompression'),
                       OBJECT_ID('dbo.PageCompression') )      

執行查詢：

SET STATISTICS IO ON 
                       SELECT SalesOrderID,SalesOrderDetailID
    ,CarrierTrackingNumber FROM dbo.PageCompression
                       WHERE SalesOrderID BETWEEN 51500 AND 5200
SET STATISTICS IO OFF      

 索引視圖：

由于權限原因，查詢可能不會傳回很多資料，這時候視圖可能作為一種候選方案，對于僅查詢的靜态資料，建立索引視圖也是一個很好的方案。

不用索引視圖的情況

SET STATISTICS IO ON
             SELECT  
       psc.Name,
       SUM(sod.LineTotal) AS SumLIneTotal,
       SUM(sod.OrderQty) AS SumOrderQty,
       AVG(sod.UnitPrice) AS AvgUnitPrice
        FROM Sales.SalesOrderDetail sod
       INNER JOIN Production.Product p ON sod.ProductID=p.ProductID
       INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID=psc.ProductSubcategoryID
       GROUP BY psc.Name
       ORDER BY psc.Name      

建立索引視圖：

CREATE VIEW dbo.ProductSubcategorySummry
       --用于建立索引視圖
       WITH  SCHEMABINDING
       AS
             SELECT 
       psc.Name,
       SUM(sod.LineTotal) AS SumLIneTotal,
       SUM(sod.OrderQty) AS SumOrderQty,
       AVG(sod.UnitPrice) AS AvgUnitPrice
       FROM Sales.SalesOrderDetail sod
       INNER JOIN Production.Product p ON sod.ProductID =p.ProductID
       INNER JOIN production.ProductSubcategory psc ON p.ProductSubcategoryID=psc.ProductSubcategoryID GROUP BY psc.Name;      

--建立聚集索引
       CREATE UNIQUE CLUSTERED INDEX CLIX_ProductSubcategorySummay ON dbo.ProductSubcategorySummry(Name)

       SET STATISTICS IO  ON 
       SELECT name,SumLineTotal,SumOrderQty,TotalUnitPrice/Occurances AS AvgUnitPrice FROM dbo.ProductSubcategorySummry 
       ORDER BY name      

 在使用索引視圖後，邏輯讀下降了很多

索引視圖在需要将多個表關聯到一個單元中時非常有效，可以降低關聯時的IO請求

索引視圖的限制條件：

1.視圖中所有列必須是确定的

2.索引視圖必須使用SCHEMA_BINDING選項

3.聚集索引必須使用unique選項

4.引用的表必須具有帶有架構名

5.某些彙總函數如AVG(),不能用于索引視圖