Partition elimination in partition views

Partition elimination in partition views

AdventureWorks2008R2 database is the best test bed for demonstrating partition elimination in partition views. Following are few alterations that we need to do on the database before demonstrating partition elimination.

1)   Add file groups to database

2)   Add files to file groups

3)   Create new tables on this new partition (file groups).

4)   Pump in data to these new tables.

5)   Create view

Let’s start with altering the database, adding new file groups and adding new files to these file groups.

USE AdventureWorksDW2008R2; GO -- file group 1 ALTER DATABASE AdventureWorksDW2008R2 ADD FILEGROUP PVFG1 ; GO ALTER DATABASE AdventureWorksDW2008R2 ADD FILE (       NAME = N'PVFG1',       FILENAME = N'K:\LiveDatabase\PVFG1.ndf',       SIZE = 50, FILEGROWTH = 10% ) TO FILEGROUP PVFG1 ; GO -- file group 2 ALTER DATABASE AdventureWorksDW2008R2 ADD FILEGROUP PVFG2 ; GO ALTER DATABASE AdventureWorksDW2008R2 ADD FILE (       NAME = N'PVFG2',       FILENAME = N'I:\LiveDatabase\PVFG2.ndf',       SIZE = 50, FileGrowth = 10% ) TO FILEGROUP PVFG2 ; GO -- file group 3 ALTER DATABASE AdventureWorksDW2008R2 ADD FILEGROUP PVFG3 ; GO ALTER DATABASE AdventureWorksDW2008R2 ADD FILE (       NAME = N'PVFG3',       FILENAME = N'J:\LiveDatabase\PVFG3.ndf',       SIZE = 50, FileGrowth = 10% ) TO FILEGROUP PVFG3 ; GO

Here we are adding 3 new File Groups to the database; this is in addition to Primary file group. For best result all these should be on different disk. In order to simulate different disk especially on a Laptop environment, you can use USB drives.  

Second step is to create tables FIS2005, FIS2006, FIS2007 and FIS2008 respectively for data for the year 2005 and earlier, for the year 2006, for the year 2006 and for the year 2008 and later.

The create scripts are available on the following blog http://sqlnfr.blogspot.com/2012/01/create-table-script-for-partition.html

As we are using AdventureWorksDW2008 database, our main data resides in FactInternetSales table and now as we have our tables created on different file group, it’s time to fill in our tables with respective years data.

-- 2008 data INSERT INTO       FIS2008 SELECT       * FROM       FactInternetSales WHERE        OrderDateKey >= 20080101       AND        OrderDateKey < 20090101; -- 2007 data INSERT INTO       FIS2007 SELECT       * FROM       FactInternetSales WHERE        OrderDateKey >= 20070101       AND        OrderDateKey < 20080101; -- 2006 data INSERT INTO       FIS2006 SELECT       * FROM       FactInternetSales WHERE        OrderDateKey >= 20060101       AND        OrderDateKey < 20070101; -- 2005 data INSERT INTO       FIS2005 SELECT       * FROM       factInternetSales WHERE        OrderDateKey < 20060101 ; GO

Now the view, this is a simple view, which performs a union all on all of our tables.

CREATE VIEW       FIS AS       SELECT * FROM FIS2005       UNION ALL       SELECT * FROM FIS2006       UNION ALL       SELECT * FROM FIS2007       UNION ALL       SELECT * FROM FIS2008 ; GO

Now the following query selects data for a particular date, which is 1^st July 2007, from the view.

SET STATISTICS IO ON; GO SELECT * FROM FIS WHERE       OrderDateKey = 20070701

When we have a look at the execution plan, we can see Clustered index seek on PK_FIS2007 index on FIS2007 table.

Now let’s drop the constrains on the table and let’s see what does SQL server do to the portioned view

ALTER TABLE FIS2005 DROP CONSTRAINT  FIS2005ChkDT GO ALTER TABLE FIS2006 DROP CONSTRAINT  FIS2006ChkDT GO ALTER TABLE FIS2007 DROP CONSTRAINT  FIS2007ChkDT GO ALTER TABLE FIS2008 DROP CONSTRAINT  FIS2008ChkDT GO

Now execute the same select query on the view and the resulting execution plan.

SELECT       * FROM       FIS WHERE       OrderDateKey = 20070701;