http://www.sqlservercentral.com/blogs/aschenbrenner/2012/09/27/nested-partitioned-views/

Printed 2014/09/21 12:10AM

Nested Partitioned Views

By Klaus Aschenbrenner, 2012/09/27

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.

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

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 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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.