發行項
09/24/2010

在定義索引檢視時進行資料分割切換

分割資料可讓您快速和有效率地管理和存取資料子集，同時又可維護整個資料集合的完整性。在資料分割資料上定義索引檢視，可進一步增加您的查詢的速度與效率。這些定義的檢視會呼叫「資料分割 - 對齊索引檢視」 (partition-aligned indexed views)。

索引檢視指的是當下列條件為真時，與其參考資料表進行資料分割對齊：

索引檢視與資料表的索引的資料分割函數：
- 定義相同數目的資料分割。
- 為資料分割定義相同的界限值。
- 資料分割函數的引數必須是相同的資料行。
檢視定義的規劃清單，包含資料分割資料表的資料分割資料行 (而非包含資料分割資料行的運算式)。
在檢視定義進行分組的地方，資料分割資料行是包含在檢視定義中的群組資料行的其中之一。
在檢視使用聯結、子查詢、函數等項目參考數個資料表的地方，索引檢視只會其中一個資料分割資料表進行資料分割對齊。

在 SQL Server 2008 中，資料分割對齊索引檢視可與檢視中定義的資料分割資料表一起進行切換。在資料的子集或資料分割上執行的維護作業現在也更有效率了，因為這些作業只處理所需的資料，而非整個資料表。具索引檢視的資料分割資料還具有下列其他好處：

自動維護。只要執行 INSERT、UPDATE 或 DELETE 陳述式，SQL Server 2008 就會自動維護索引檢視。
彙總改進。如果索引檢視能夠在彙總中減少足夠的資料列，便能大幅改進彙總查詢效能。在 SQL Server 2008 Enterprise 中，當查詢最佳化工具使用 NOEXPAND 提示，自動比對彙總查詢與適當的索引檢視表或直接參考索引檢視表的查詢時，查詢效能會比查詢存取整個資料表時要來得有效率許多。

當使用具索引檢視的 ALTER TABLE...SWITCH 陳述式時的條件約束

當使用 Transact-SQL ALTER TABLE...SWITCH 陳述式，在索引檢視所參考的來源與目標資料表中轉換資料子集時，資料表與索引都必須符合使用資料分割切換有效傳送資料中所列出的條件。請參閱該主題的資料表與索引需求章節。

如需有關移動具有已定義索引檢視的資料分割的詳細資訊，請參閱使用資料分割切換有效傳送資料的「移動資料分割的額外需求」一節。

使用資料分割對齊的索引檢視的典型案例，通常都是用在大型資料倉儲中。在大型資料倉儲中，一個大型的事實資料表通常會以日期進行資料分割，而彙總查詢則通常針對聯結多個維度資料表的事實資料表來執行。使用 ALTER TABLE … SWITCH 陳述式切換資料分割的移入或移出，可以提供下列好處：

迅速將淘汰的資料從事實資料表最舊的資料分割中封存移出。
迅速將新的資料從大型事實資料表的最新資料分割中移入。

下圖顯示當新的資料移入事實資料表中最新的資料分割時，資料分割對齊索引檢視的運作方式。切換到事實資料表中的新的資料分割，會攜帶在新的資料分割中所建立的彙總。

資料分割對齊的索引檢視切換

範例

下列範例顯示如何使用 ALTER TABLE … SWITCH 陳述式，將資料表中舊的資料分割切換移出，並同時帶入資料分割對齊索引檢視的一部分。

在此範例中，銷售事實資料表 [f_sales] 包含了 2006 一整年的銷售資料。這份銷售資料根據季度進行資料分割。常用查詢依日期以及依商店針對銷售金額執行彙總，而索引查詢就是用來加速在事實資料表中定義的彙總查詢的速度。現在，2006 第一季的資料已封存至封存資料表，而事實資料表中的索引查詢則維持正確以及不可用的狀態。

USE MASTER
GO

DROP DATABASE test_partition
GO

--Create database with the file groups that will be used by the partition schemes.
CREATE DATABASE test_partition
ON PRIMARY (NAME = 'paiv_Dat0', FILENAME='C:\temp\test_partition_DataFile0.mdf'),
FILEGROUP FG1 (NAME = 'paiv_Dat1', FILENAME = 'C:\temp\test_partition_DataFile1.ndf'),
FILEGROUP FG2 (NAME = 'paiv_Dat2', FILENAME = 'C:\temp\test_partition_DataFile2.ndf'),
FILEGROUP FG3 (NAME = 'paiv_Dat3', FILENAME = 'C:\temp\test_partition_DataFile3.ndf'),
FILEGROUP FG4 (NAME = 'paiv_Dat4', FILENAME = 'C:\temp\test_partition_DataFile4.ndf'),
FILEGROUP FG5 (NAME = 'paiv_Dat5', FILENAME = 'C:\temp\test_partition_DataFile5.ndf')
LOG ON (NAME = 'paiv_log', filename='C:\temp\test_partition_log.ldf')
GO
USE test_partition
GO

-- Create partition function and partition scheme.
CREATE PARTITION FUNCTION [PF1] (int)
AS RANGE LEFT FOR VALUES (20060331, 20060630, 20060930, 20061231);
GO
CREATE PARTITION SCHEME [PS1]
AS PARTITION [PF1] 
TO ([FG1], [FG2], [FG3], [FG4], [FG5]
, [PRIMARY]);
GO

-- Create fact table.
CREATE TABLE dbo.f_sales (date_key INT NOT NULL, cust_key INT, store_key INT, amt MONEY) ON PS1(date_key);
GO

-- Populate data into table f_sales. 
SET NOCOUNT ON
GO
DECLARE @d INT, @ds INT, @cs INT, @s INT
SET @d = 20060101
SET @ds = 7  -- date_key increment step

WHILE (@d <= 20061231) 
BEGIN
WHILE @d%100 > 0 AND @d%100 < 29
BEGIN
SET @cs = 10  -- # of records for customer/store for that date
SET @s = CAST ( RAND() * 1000 as INT )
WHILE (@cs > 0)
BEGIN
INSERT dbo.f_sales (date_key, cust_key, store_key, amt)
VALUES (@d, CAST ( RAND() * 1000 as INT ), @s, CAST ( RAND() * 1000 as MONEY ) )
SET @cs = @cs - 1
END
SET @d = @d + @ds
END
SET @d = @d + @ds
END
GO


-- The table with clustered index is partitioned using the partition scheme specified.
CREATE CLUSTERED INDEX UCIdx_f_sales on dbo.f_sales (date_key, cust_key, store_key) ON PS1(date_key)
GO

--Create indexed view, which aggregates on the date and store.
CREATE VIEW dbo.v_f_sales_sumamt WITH SCHEMABINDING AS
(
SELECT date_key, store_key, sum(ISNULL(amt,0)) AS amt, count_big(*) AS cnt
FROM dbo.f_sales AS sales
GROUP BY date_key, store_key
)
GO

-- Materialize the view. The indexed view is now partition-aligned with table f_sales.
CREATE UNIQUE CLUSTERED INDEX ucidx_v_sales_sum
ON dbo.v_f_sales_sumamt (date_key) ON PS1(date_key)
GO

-- Check data distribution in various partitions of the table & the indexed view.
SELECT OBJECT_NAME(p.object_id) as obj_name, p.index_id, p.partition_number, p.rows, a.type, a.filegroup_id 
FROM sys.system_internals_allocation_units a
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.f_sales'), OBJECT_ID(N'dbo.v_f_sales_sumamt '))
ORDER BY obj_name, p.index_id, p.partition_number

-- Create archive table to receive the partition that will be switched out of table f_sales. 
CREATE TABLE dbo.sales_archive (date_key INT NOT NULL, cust_key INT, store_key INT, amt MONEY) ON FG1
GO
CREATE CLUSTERED INDEX UCIdx_sales_archive on dbo.sales_archive (date_key, cust_key, store_key) ON FG1
GO
--Create indexed view with view definition matching v_f_sales_sumamt on table f_sales.
CREATE VIEW dbo.v_sales_archive_sumamt WITH SCHEMABINDING AS
(
SELECT date_key, store_key, sum(ISNULL(amt,0)) AS amt, count_big(*) AS cnt
FROM dbo.sales_archive AS sales
GROUP BY date_key, store_key
)
GO

-- Materialize the view. The indexed view is partition-aligned with table sales_archive.
CREATE UNIQUE CLUSTERED INDEX ucidx_v_sales_sum
ON dbo.v_sales_archive_sumamt(date_key) ON FG1
GO

-- Check data distribution in various partitions of the table and the indexed view. 
SELECT OBJECT_NAME(p.object_id) as obj_name, p.index_id, p.partition_number, p.rows, a.type, a.filegroup_id 
FROM sys.system_internals_allocation_units a
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.sales_archive'), OBJECT_ID(N'dbo.v_sales_archive_sumamt '))
ORDER BY obj_name, p.index_id, p.partition_number

-- Data associated with the old partition of the source table - [f_sales] and the indexed view [v_f_sales_sumamt] - 
-- is switched out to archive table [sales_archive] and the indexed view [v_sales_archive_sumamt].
ALTER TABLE dbo.f_sales SWITCH PARTITION 1 TO dbo.sales_archive

-- Data distribution in various partitions shows that 
-- partition 1 of [f_sales] and the indexed view [v_f_sales_sumamt] are now empty 
-- and these rows are now in [sales_archive] and [v_sales_archive_sumamt], respectively.
SELECT OBJECT_NAME(p.object_id) as obj_name, p.index_id, p.partition_number, p.rows, a.type, a.filegroup_id 
FROM sys.system_internals_allocation_units a
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.sales_archive'), OBJECT_ID(N'dbo.v_sales_archive_sumamt '), 
OBJECT_ID(N'dbo.f_sales'), OBJECT_ID(N'dbo.v_f_sales_sumamt '))
ORDER BY obj_name, p.index_id, p.partition_number

如先前範例中所顯示，在進行資料分割切換後，所有在資料表 [f_sales] 的資料分割 1 以及索引檢視 [v_f_sales_sumamt] 中的資料，都已移動至對應的資料表 [sales_archive] 以及索引檢視 [v_sales_archive_sumamt] 中。

若要取得 2006 年 7 月依日期及依商店分類的銷售金額，您可以查詢資料表 [f_sales] 或索引檢視[v_f_sales_sumamt] (如下列兩個查詢所示)。兩個範例中的結果是相同的，但是執行索引檢視可大幅改進查詢效能，因為索引檢視會將預先計算的彙總具體化，並以 10 為因數減少資料列的數量，如下列範例所示。

-- This query runs against the table [f_sales]
SELECT date_key, store_key AS [Store Number], sum(ISNULL(amt,0)) AS Sales_Amount
FROM dbo.f_sales
WHERE date_key >= '20060701' AND date_key < '20060801'
GROUP BY date_key, store_key
ORDER BY date_key, store_key
OPTION (EXPAND VIEWS)

-- This query runs against the indexed view [v_f_sales_sumamt]
-- the result of this query is the same as the one against the table
-- the indexed view materializes the pre-calculated aggregate, resulting in significant improvements in query performance   
SELECT date_key, store_key AS [Store Number], sum(ISNULL(amt,0)) AS Sales_Amount
FROM dbo.v_f_sales_sumamt WITH (NOEXPAND)
WHERE date_key >= '20060701' AND date_key < '20060801'
GROUP BY date_key, store_key

使用 ALTER TABLE…SWITCH 陳述式進行資料分割切換，是一項迅速、僅處理中繼資料的作業。當以來源與目標資料表將索引檢視進行資料分割對齊時，資料分割切換可讓您將資料子集從來源資料表中帶入目標資料表，以及對齊的索引檢視的關聯部分。如此便能夠維持資料收集的完整性，因為所有與來源資料表相關聯的物件 (如資料表、索引，以及索引檢視) 都會包含在資料分割的切換當中。透過在資料分割資料表上使用索引檢視，您可以大幅增加參考該資料表的彙總查詢的效能。

請參閱

概念

使用資料分割切換有效傳送資料

共用方式為

在定義索引檢視時進行資料分割切換

當使用具索引檢視的 ALTER TABLE...SWITCH 陳述式時的條件約束

範例

請參閱

概念

其他資源