A few weeks ago I had a customer engagement, where the customer wanted to implement Nested Partitioned Views. Before we go into the details of this approach and if it could work, I want to give you an overview about the requirements, and why the customer wanted to have Nested Partitioned Views.

The main idea was that the customer had to implement their SQL Server database for both Standard and Enterprise Edition. With the Enterprise Edition the customer used a combination of Partitioned Views and Partitioned Tables, as you can see it from the following picture.

Some of you might ask, why the heck you want to implement such a scenario. The answer is easy: Partitioned Views itself have some disadvantages, and on the other hand Partitioned Tables also have some disadvantages. But when you combine then you will get the benefits from BOTH worlds. The following list describes the pros and cons of Partitioned Views in SQL Server.

• Pros
  • Available on any Edition of SQL Server
  • Each table has its own better Statistics
  • Index Rebuilds of any table is an ONLINE operation (when done on Enterprise Edition)
  • Each table can be indexed independently
    • Operational Data
    • Historical Data
• Cons
  • Lots of tables to administer
  • Indexes must be created on individual tables
  • Check Constraints are needed for Table Elimination
  • Gaps and overlapping values are possible

The following list describes the pros and cons of Partitioned Tables in SQL Server.

• Pros
  • Only one table to administer
  • Gaps and overlapping values are NOT possible
  • Completely transparent
• Cons
  • Available only on the Enterprise Edition of SQL Server
  • Table-Level Statistics
    • Less accurate on larger Partitioned Tables
    • Filtered Statistics can help here…
  • Partition Level Index Rebuilds are OFFLINE operations
    • Only the whole Partitioned Table can be rebuild ONLINE
  • Supports Partitioning only over a single column
    • Persisted Computed Columns are needed

As you can see from this list, there are a lot of disadvantages of Partitioned Tables, especially in the area of maintenance (less accurate Statistics, Partition Level Index Rebuilds). As you have seen in the previous figure, you can achieve a very powerful solution, when you combine Partitioned Views with Partitioned Tables.

Because the customer also had to support the database on the Standard Edition of SQL Server (where Partitioned Tables are not available), they just wanted to substitute the Partitioned Tables with Partitioned Views – so in the final solution you just have Partitioned Views inside Partitioned Views – just Nested Partitioned Views. The following picture illustrates this concept.

So the question was now, if this could be done with SQL Server? Of course, you can make it this way, but what’s about performance? SQL Server can use with a single Partitioned View Table Elimination when each participating table has a corresponding CHECK constraint defined. Therefore SQL Server only has to query the necessary tables – it’s almost the same concept as Partition Elimination with Partitioned Tables. But would Table Elimination work with Nested Partitioned Views? Because there is no chance to define a CHECK constraint on the inner Partitioned Views…

Before I give you the ultimate answer on that question, I want to walk through a complete scenario, how you can setup and implement Nested Partitioned Views as illustrated in the last picture. In the first step we are creating a new database, and adding 2 new file groups to it, where the data of 2007 and 2008 is stored.

USE master
GO

-- Create a new database
CREATE DATABASE VLDB ON PRIMARY 
(
	NAME = N'VLDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VLDB.mdf',
	SIZE = 5072KB, 
	FILEGROWTH = 1024KB
)
LOG ON 
(
	NAME = N'VLDB_log',
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VLDB_log.ldf',
	SIZE = 2048000KB, -- Initialize the log with 2GB, this gives us 16 VLFs
	FILEGROWTH = 10%
)
GO

-- Create a new file group for the 2007 sales data
ALTER DATABASE VLDB
ADD FILEGROUP Sales2007FG
GO

-- Add a new file to the previous created file group
ALTER DATABASE VLDB
ADD FILE
(
	NAME = 'Sales2007_Data',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sales2007_Data.ndf',
	SIZE = 300,
	FILEGROWTH = 10%
)
TO FILEGROUP Sales2007FG
GO

-- Create a new file group for the 2008 sales data
ALTER DATABASE VLDB
ADD FILEGROUP Sales2008FG
GO

-- Add a new file to the previous created file group
ALTER DATABASE VLDB
ADD FILE
(
	NAME = 'Sales2008_Data',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sales2008_Data.ndf',
	SIZE = 300,
	FILEGROWTH = 10%
)
TO FILEGROUP Sales2008FG
GO

In each file group we create a Partitioned View which subdivides the data into 4 separate tables for each quarter (Q1, Q2, Q3, Q4). The following listing shows how to setup the Partitioned View for the 2007 sales data.

-- Table for 2007 Q1
CREATE TABLE [Sales2007_Q1]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2007Q1 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20070101' AND DateKey < '20070401'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2007_Q1]
ADD CONSTRAINT PK_Sales2007Q1 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2007/Q1
INSERT INTO [Sales2007_Q1] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20070101' AND DateKey < '20070401'
GO

-- Table for 2007 Q2
CREATE TABLE [Sales2007_Q2]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2007Q2 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20070401' AND DateKey < '20070701'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2007_Q2]
ADD CONSTRAINT PK_Sales2007Q2 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2007/Q2
INSERT INTO [Sales2007_Q2] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20070401' AND DateKey < '20070701'
GO

-- Table for 2007 Q3
CREATE TABLE [Sales2007_Q3]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2007Q3 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20070701' AND DateKey < '20071001'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2007_Q3]
ADD CONSTRAINT PK_Sales2007Q3 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2007/Q3
INSERT INTO [Sales2007_Q3] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20070701' AND DateKey < '20071001'
GO

-- Table for 2007 Q4
CREATE TABLE [Sales2007_Q4]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2007Q4 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20071001' AND DateKey < '20080101'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2007_Q4]
ADD CONSTRAINT PK_Sales2007Q4 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2007/Q4
INSERT INTO [Sales2007_Q4] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20071001' AND DateKey < '20080101'
GO

-- Create the Partitioned View for 2007
CREATE VIEW Sales2007
AS
	SELECT * FROM Sales2007_Q1
	UNION ALL
	SELECT * FROM Sales2007_Q2
	UNION ALL
	SELECT * FROM Sales2007_Q3
	UNION ALL
	SELECT * FROM Sales2007_Q4
GO

As you can see from the listing, each individual table has a corresponding CHECK constraint, so that SQL Server can eliminate the tables which don’t have to be accessed, when we query our Partitioned View. The next listing shows how to setup the Partitioned View for the 2008 sales data.

-- Table for 2008 Q1
CREATE TABLE [Sales2008_Q1]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2008Q1 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20080101' AND DateKey < '20080401'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2008_Q1]
ADD CONSTRAINT PK_Sales2008Q1 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2007/Q1
INSERT INTO [Sales2008_Q1] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20080101' AND DateKey < '20080401'
GO

-- Table for 2008 Q2
CREATE TABLE [Sales2008_Q2]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2008Q2 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20080401' AND DateKey < '20080701'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2008_Q2]
ADD CONSTRAINT PK_Sales2008Q2 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2008/Q2
INSERT INTO [Sales2008_Q2] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20080401' AND DateKey < '20080701'
GO

-- Table for 2008 Q3
CREATE TABLE [Sales2008_Q3]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2008Q3 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20080701' AND DateKey < '20081001'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2008_Q3]
ADD CONSTRAINT PK_Sales2008Q3 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2008/Q3
INSERT INTO [Sales2008_Q3] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20080701' AND DateKey < '20081001'
GO

-- Table for 2008 Q4
CREATE TABLE [Sales2008_Q4]
(
	DateKey DATETIME NOT NULL
		CONSTRAINT Sales2008Q4 -- The CHECK constraint is still needed to do Partition Elimination for the Partitioned View
			CHECK (DateKey >= '20081001' AND DateKey < '20090101'),
	OnlineSalesKey INT NOT NULL,
	SalesOrderNumber NVARCHAR(20) NOT NULL,
	SalesAmount MONEY NOT NULL
)
GO

-- Add a primary key clustered
ALTER TABLE [Sales2008_Q4]
ADD CONSTRAINT PK_Sales2008Q4 PRIMARY KEY CLUSTERED 
(
	DateKey,
	OnlineSalesKey
)
GO

-- Load data into the table for the year 2008/Q4
INSERT INTO [Sales2008_Q4] (DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount)
SELECT DateKey, OnlineSalesKey, SalesOrderNumber, SalesAmount FROM ContosoRetailDW.dbo.FactOnlineSales
WHERE DateKey >= '20081001' AND DateKey < '20090101'
GO

-- Create the Partitioned View for 2008
CREATE VIEW Sales2008
AS
	SELECT * FROM Sales2008_Q1
	UNION ALL
	SELECT * FROM Sales2008_Q2
	UNION ALL
	SELECT * FROM Sales2008_Q3
	UNION ALL
	SELECT * FROM Sales2008_Q4
GO

After we have now setup the Partitioned Views for 2007 and 2008, we finally create another Partitioned View which just unions the data of the other 2 Partitioned Views together:

-- Create a Partitioned View on top of the 2 Partitioned Views
CREATE VIEW Sales
AS
	SELECT * FROM Sales2007
	UNION ALL
	SELECT * FROM Sales2008
GO

So now we have created our Nested Partitioned View: Partitioned Views within Partitioned Views. The final thing that we now have to check is, if SQL Server is able to do Table Elimination with our Nested Partitioned View. When we talk about Table Elimination (or Partition Elimination with Partitioned Table) we have to differentiate between 2 types of elimination:

• Static Elimination
• Dynamic Elimination

Static Elimination means that you provide within your query a static value. So during the compilation of the Execution Plan the Query Optimizer already knows which data we are accessing. In that case SQL Server generates an Execution Plan, which only references the relevant data. Look at the following query:

-- Shows static Table Elimination across the Partitioned View.
-- This SELECT accesses the following table:
-- => Sales2007_Q3
SELECT * FROM Sales
WHERE DateKey >= '20070721' AND DateKey <= '20070725'
GO

We are providing here hard coded values, so the Query Optimizer can sniff them, and generates an Execution Plan, which only accesses the Sales2007_Q3 table. So Static Elimination works without any problems with Nested Partitioned Views, as you can see it in the following Execution Plan:

Static Elimination is fine, if you are doing demos etc., but in a production database when you are calling your query with different values through parameters, SQL Server has to do Dynamic Elimination. Dynamic Elimination means that the Query Optimizer has to generate an Execution Plan, which can’t take into account during the compilation which tables you are accessing, because those tables are just determined during runtime – therefore the name Dynamic Elimination. So let’s imagine we are executing the following parameterized query:

DECLARE @from DATETIME = '20070721'
DECLARE @to DATETIME = '20070725'

SELECT * FROM Sales
WHERE DateKey >= @from AND DateKey <= @to
GO

In that case SQL Server can’t determine which data is accessed, because the referenced variables are runtime constructs, they are not available during the compilation phase of our query. The above query produces the following Execution Plan:

When you look at the first step on that plan, you might think that SQL Server has to seek into each table to get the corresponding records. The Query Optimizer estimates each Clustered Index Seek with around 12%. But that’s only the half-truth of that Execution Plan! The real power lies in the Filter Operator which comes before the Clustered Index Seek. When you look at the Tooltip Window of the Filter Operator inside SQL Server Management Studio, you can see that the operator has a so-called Startup Expression Predicate:

As you can see from the picture, the Startup Expression Predicate has the same value as the CHECK constraint on the table itself. So that Filter Operator only calls the Clustered Index Seek operator on the underlying table when the Startup Expression Predicate is evaluated to true! You can also cross-check this with the property Actual Executions on the Clustered Index Seek Operators:

In our case SQL Server only executed the Clustered Index Seek Operator on the Sales2007_Q3 table. On all other tables the Filter Operator prevented through the Startup Expression Predicate the execution of the Clustered Index Seeks! That’s a really powerful concept for Dynamic Table Elimination inside Partitioned Views.

As you can see from this example, Nested Partitioned Views are working without any problems in SQL Server, and on the other hand SQL Server uses a Startup Expression Predicate inside a Filter Operator to drive Dynamic Table Elimination when you work with Partitioned Views – it doesn’t matter here if those Views are nested or not. In that case it is really important that you check the whole Execution Plan to get a better understanding what SQL Server is actually doing for your query. When you just look on that plan (without going into the details), you might think that SQL Server has to access every table, but that’s not really true!

A big Thank You goes to Paul White (Weblog, Twitter) who helped me to understand that behavior and implementation strategy of SQL Server. You can find the whole sample of Nested Partitioned Views here for download.

Thanks for reading

-Klaus