Partition Switching When Indexed Views Are Defined
Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. Defining indexed views on partitioned data can further increase the speed and efficiency of your queries. These defined views are called partition-aligned indexed views.
An indexed view is partition-aligned with the table it references if the following conditions are true:
The partition functions of the indexes of the indexed view and table:
Define the same number of partitions.
Define the same boundary values for partitions.
The arguments of the partition functions must be the same column.
The projection list of the view definition includes the partitioning column (as opposed to an expression that includes the partitioning column) of the partitioned table.
Where the view definition performs a grouping, the partitioning column is one of the grouping columns included in the view definition.
Where the view references several tables (using joins, subqueries, functions, and so on), the indexed view is partition-aligned with only one of the partitioned tables.
In SQL Server 2008, partition-aligned indexed views can be switched together with the partitioned tables the views are defined against. Maintenance operations on partitions or subsets of data are now performed more efficiently because these operations target only the data that is required, instead of the entire table. Additional benefits to partitioning data with indexed views are:
Automatic maintenance. SQL Server 2008 automatically maintains indexed views when INSERT, UPDATE, or DELETE statements are run.
Aggregation improvement.Aggregate query performance can be significantly improved if the indexed view provides enough row reduction through aggregation. In SQL Server 2008 Enterprise, when the query optimizer automatically matches the aggregate query to the appropriate indexed view or the query directly refers to the indexed view by using a NOEXPAND hint, query performance can be more efficient than when the query accesses the whole table.
Constraints When Using ALTER TABLE...SWITCH Statement with Indexed Views
When using the Transact-SQL ALTER TABLE...SWITCH statement to transfer subsets of data between source and target tables that are referenced by an indexed view, tables and indexes must meet the conditions listed in Transferring Data Efficiently by Using Partition Switching. See the Table and Index Requirements section of that topic.
For more information about moving partitions with defined index views, see the section "Additional Requirements for Moving Partitions" of Transferring Data Efficiently by Using Partition Switching.
A typical scenario in which partition-aligned indexed views can be used is with a large data warehouse. In a large data warehouse, a large fact table is usually partitioned by date, and aggregate queries are often run against the fact table joining with several dimension tables. Switching a partition in or out by using the ALTER TABLE … SWITCH statement can provide the following benefits:
Quickly archive aged data out of the oldest partition of the fact table.
Quickly bring new data into the newest partition of the large fact table.
The following illustration shows how a partition-aligned indexed view works when new data is brought into the newest partition of the fact table. The new partition that is switched into the fact table carries along the aggregation that is created on the new partition.
.gif "Partition-Aligned Index Views Switching")
Examples
The following example shows how to use the ALTER TABLE … SWITCH statement to switch out an old partition of a table that brings part of a partition-aligned indexed view with it.
In this example, a sales fact table [f_sales] contains one year of sales data for the year 2006.The sales data is partitioned on a quarterly basis. Frequently queries are run that aggregate the sales amount by date and by store, and an indexed view is created to speed up the aggregate query that was defined on the fact table. Now, the first quarter 2006 data is to be archived to an archive table, while the indexed view on the fact table remains correct and usable.
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
As shown in the previous example, after the partition has been switched, all the data in partition 1 of table [f_sales] and indexed view [v_f_sales_sumamt] are moved over to the corresponding table [sales_archive] and indexed view [v_sales_archive_sumamt].
To get the sales amount by date by store for July 2006, you can query against either table [f_sales] or indexed view [v_f_sales_sumamt], as shown in the following two queries. The results are the same in both cases, but running against the indexed view significantly improves query performance because the indexed view materializes the precalculated aggregates and reduces the number of rows by a factor of 10, as shown in the following example.
-- 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
Partition switching by using the ALTER TABLE…SWITCH statement is a fast, metadata-only operation. When indexed views are partition-aligned with the source and target table, partition switching enables you to bring a subset of the data from the source table to the target table, and also associated parts of aligned indexed views. The integrity of the data collection is maintained because all the objects that are associated with the source table (such as tables, indexes, and indexed views) are included in the switch of the partition. By using indexed views on a partitioned table, you can significantly increase the performance of aggregate queries that reference the table.