在此练习中,您将创建分区对齐索引视图。索引可以包含表中所有行的数据,不过,它消除了分区的某些优势。但是,可以创建分区对齐视图,在分区对齐视图上又可以创建索引。然后,无论查询是否显式使用分区对齐视图,都可以使用这些分区对齐索引。
注意: 您可以复制此练习中所用的脚本,这些脚本位于 C:\SQLHOLS\Partitioning\Solution\ Partition Processing 文件夹中的 Partition Processing.ssmssln 解决方案中。
1.创建分区对齐视图
(1)在解决方案资源管理器中,右键单击该连接,然后单击新建查询。
(2)右键单击 SQLQuery1.sql,然后单击重命名。
(3)键入 Create Views.sql,然后按 Enter。
(4)键入下面的代码。
USE AdventureWorksDW
GO
CREATE VIEW vw_InternetSales2001
WITH SCHEMA">BINDING
AS
SELECT
[InternetSalesID],
[ProductKey],
[FullDateAlternateKey] as OrderDate,
[fisp].[OrderDateKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSalesPartitioned] AS fisp
INNER JOIN [dbo].[DimTime] AS dt ON [fisp].[OrderDateKey]=[dt].[TimeKey]
WHERE [DueDateKey]<185
GO
CREATE VIEW vw_InternetSales2002
WITH SCHEMABINDING
AS
SELECT
[InternetSalesID],
[ProductKey],
[FullDateAlternateKey] as OrderDate,
[fisp].[OrderDateKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSalesPartitioned] AS fisp
INNER JOIN [dbo].[DimTime] AS dt ON [fisp].[OrderDateKey]=[dt].[TimeKey]
WHERE [DueDateKey] BETWEEN 185 AND 549
GO
CREATE VIEW vw_InternetSales2003
WITH SCHEMABINDING
AS
SELECT
[InternetSalesID],
[ProductKey],
[FullDateAlternateKey] as OrderDate,
[fisp].[OrderDateKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSalesPartitioned] AS fisp
INNER JOIN [dbo].[DimTime] AS dt ON [fisp].[OrderDateKey]=[dt].[TimeKey]
WHERE [DueDateKey]>549
GO