[SQL] Compare Operator - Indexed View

• "Indexed View" 的使用方式
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Sales.SalesOrderHeader 及 Sales.SalesOrderDetail 產生 1 個 View 包含 "Clustered" 
• 創建指令
1. "With Schemabinding" 建立的 VIEW ，才可以建立索引
2. 需遵守 "兩部份名稱" (SchemaName.TableName 格式)
3. "Indexed View" 不可參考其他檢視表
4. 第一個建立的索引，必須是 "Unique Clustered Index"，之後可建立其他 "NonClustered Index"
5. 使用 "Group By"時，必須輸出 COUNT_BIG(*) 規定，返回指定組中的項目數量

use AdventureWorks2012;

if OBJECT_ID('vwSalesOrderAmt', 'view') is not null
drop view vwSalesOrderAmt;
GO

create view vwSalesOrderAmt With Schemabinding as
select h.SalesOrderID,
       h.OrderDate,
       h.DueDate,
       h.ShipDate,
       sum(d.OrderQty * d.UnitPrice * (1 - d.UnitPriceDiscount)) AmtTotal,
       COUNT_BIG(*) CB
from Sales.SalesOrderHeader h
join Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderDetailID
group by h.SalesOrderID,
         h.OrderDate,
         h.DueDate,
         h.ShipDate 
GO

create Unique Clustered index clIndex ON vwSalesOrderAmt (SalesOrderID) 
GO

• 查看 View

SELECT h.SalesOrderID,
       h.OrderDate,
       h.DueDate,
       h.ShipDate,
       SUM((d.OrderQty * d.UnitPrice) * (1 - d.UnitPriceDiscount)) AS AmtTotal,
       COUNT_BIG(*) AS CB
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderDetailID
GROUP BY h.SalesOrderID,
         h.OrderDate,
         h.DueDate,
         h.ShipDate

• 問題：發生下列情況，需把記憶體設定加大

• 解決：下列設定會將此問題解決

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'index create memory (KB)', 2048 ;  
GO  
RECONFIGURE;  
GO  

• CASE 1：
• 查詢

select h.SalesOrderID,
       h.OrderDate,
       h.DueDate,
       h.ShipDate,
       sum(d.OrderQty * d.UnitPrice * (1 - d.UnitPriceDiscount)) AmtTotal
from Sales.SalesOrderHeader h
join Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderDetailID
where h.SalesOrderID = 43659
group by h.SalesOrderID,
         h.OrderDate,
         h.DueDate,
         h.ShipDate

• 查詢結果

• CASE 2：
• 查詢
1. 若不是使用 SQL Server 2008 企業版或開發版，必須加上 WITH(NOEXPAND)
2. "Indexed View" 只使用 "單一資料表" 時，必須加上 table hints 的 "WITH(NOEXPAND)"，告訴 Query Optimizer 此檢視表是獨立於底層資料表，否則 Query Optimizer 還是會採用原有的底層資料表，而不使用 "WITH(NOEXPAND)"

select h.SalesOrderID,
       h.OrderDate,
       h.DueDate,
       h.ShipDate,
       sum(d.OrderQty * d.UnitPrice * (1 - d.UnitPriceDiscount)) AmtTotal
from Sales.SalesOrderHeader h
join Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderDetailID
where h.SalesOrderID = 43659
group by h.SalesOrderID,
         h.OrderDate,
         h.DueDate,
         h.ShipDate

select * from vwSalesOrderAmt WITH(NOEXPAND) where SalesOrderID = 43659

• 查詢結果
• 查詢 2：所使用 "Indexed View" 查詢成本很低

• Result：
1. 查詢的所有條件符合 "Indexed View"的條件，Database Engine 就會使用 "Indexed View"
2. "Indexed View" 不會有額外的 "查閱" 成本，"Indexed View" 本身含有資料行與資料列，不像一般的檢視表
3. 如 "Indexed View" 參考資料有異動，"Indexed View" 會自動同步更新，不用擔心使用過期的資料 
• REF：
• 建立索引檢視
• SQL Server 效能調校
• 效能調校(1)-善用各種索引