How To Create A Partitioned View In SQL Server

Introduction

 

This article explains the creation and use of partitioned view in SQL Server. In an enterprise application the transactional data are archived based on condition (such as month, year etc.). However for analytical and reporting purposes we need to get all data including live transactional and archival data.

 

To get the record effectively and avoid unnecessary table scan we can use Partitioned view.

 

Definition

 

A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

 

Partitioned view enables us to logically split huge amounts of data into smaller pieces of data ranges using specific column values. To achieve this, we used check constraint. We need to define the check constraint in every table based on column used in the partitioned view. The partitioned view uses union all to select all the participating tables as one result. When we applying where condition in the check constraint column in the view, It will directly fetch the data on the specific table using check constraint and avoid scanning records in other tables. So it is improving the query performance. If the CHECK constraint is not defined in the participating tables, the SQL Server Query Optimizer will search in all participating tables within the view to return the result.

 

Steps to create a partitioned view

 

Step 1 - Creating tables

 

In this example, we use sample order table to fetch the orders. We are creating 3 tables for orders based on the years. We are using orderyear as a partitioned column. In the example we used orders as a primary table, Orders_2018and Orders_2017as an archival table based on year (2018, 2017).

 

 

 

Query

CREATE DATABASE PartionedViewsDemo
--Creating Order tables for Year 2019,2018 and 2017
USE PartionedViewsDemo
GO
CREATE TABLE Orders (
OrderId INT NOT NULL,
OrderCountryCode CHAR(3) NOT NULL,
OrderDate DATETIME NULL,
OrderYear INT NOT NULL
CONSTRAINT PK_Order PRIMARY KEY (OrderId,OrderYear));

GO
CREATE TABLE Orders_2018 (
OrderId INT NOT NULL,
OrderCountryCode CHAR(3) NOT NULL,
OrderDate DATETIME NULL,
OrderYear INT NOT NULL
CONSTRAINT PK_Order_2018 PRIMARY KEY (OrderId,OrderYear)
);

GO
CREATE TABLE Orders_2017 (
OrderId INT NOT NULL,
OrderCountryCode CHAR(3) NOT NULL,
OrderDate DATETIME NULL,
OrderYear INT NOT NULL
CONSTRAINT PK_Order_2017 PRIMARY KEY (OrderId,OrderYear)
);

Step 2 - Inserting Sample records

 

Inserting sample records in all 3 tables.

 

 

 

Query

USE [PartionedViewsDemo]
GO

-- Record for year 2017
INSERT INTO [dbo].[Orders_2017] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
  VALUES    (201701,'IND','2017-01-01',2017 )

  INSERT INTO [dbo].[Orders_2017] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
  VALUES    (201702,'IND','2017-01-02',2017 )

-- Record for year 2018
INSERT INTO [dbo].[Orders_2018] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
  VALUES    (201801,'IND','2018-01-01',2018 )

  INSERT INTO [dbo].[Orders_2018] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
  VALUES    (201802,'IND','2018-01-02',2018 )

  -- Record for year 2019
INSERT INTO [dbo].[Orders] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
  VALUES    (201901,'IND','2019-01-01',2019 )

  INSERT INTO [dbo].[Orders] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
  VALUES    (201902,'IND','2019-01-02',2019 )

Now select the table’s data. It showing data based on the year.

 

 

 

Query

USE [PartionedViewsDemo]
GO

SELECT [OrderId] ,[OrderCountryCode],[OrderDate],[OrderYear] FROM [dbo].[Orders]
SELECT [OrderId] ,[OrderCountryCode],[OrderDate],[OrderYear] FROM [dbo].[Orders_2018]
SELECT [OrderId] ,[OrderCountryCode],[OrderDate],[OrderYear] FROM [dbo].[Orders_2017]

GO

Step 3 - Creating view without check constraint

 

Now we are creating view without check constraint. It will display all the records in the participating tables.

 

 

 

Query

USE [PartionedViewsDemo]
GO

CREATE VIEW VW_Order
AS
SELECT OrderId,OrderCountryCode,OrderDate,OrderYear  FROM Orders
UNION ALL
SELECT OrderId,OrderCountryCode,OrderDate,OrderYear  FROM Orders_2018
UNION ALL
SELECT OrderId,OrderCountryCode,OrderDate,OrderYear  FROM Orders_2017
GO

Selecting the value from the View. It will display all the records in the view.

 

 

 

Step 4 - Filtering Data in the View

 

Now we are applying where condition in the view to filter the data based on the year.

 

 

 

Check the Table seeks

 

To test the performance of the partitioned view, use STATISTICS IO. It will show exactly which tables are being accessed. Run this query and check the Messages tab.

 

 

 

We have filtered the value based on orderyear column. We have filtered the record using year 2019. Even though the data returned by the query came from the Order table, SQL Server also searched the data in Order_2018 and Order_2017 table. It searches record in all the tables.

 

 

 

To fix this issue, we must add check constraints to the tables which will allow SQL Server to ignore tables that are not needed in a particular query.

 

Query

USE [PartionedViewsDemo]
GO

SET STATISTICS IO ON

SELECT * FROM VW_Order WHERE ORDERYEAR=2019
SET STATISTICS IO OFF

Step 5 - Adding Check Constraint in the table

 

Now we are adding check constraint in the Order year table. The check constraint must be a part of primary key constraint. We are adding year as a check constraint. So that the query will get the specific year record from the respective table and avoid table scan on other tables.

 

 

 

Query

USE [PartionedViewsDemo]
GO

--CHECK CONSTRAINT FOR PARTITONED VIEW

ALTER TABLE Orders ADD CONSTRAINT CK_Order CHECK (OrderYear >= 2019)

ALTER TABLE Orders_2018 ADD CONSTRAINT CK_Order_2018 CHECK (OrderYear = 2018)
ALTER TABLE Orders_2017 ADD CONSTRAINT CK_Order_2017 CHECK (OrderYear = 2017)

Step 6 - Creating Partitioned view

 

Now we have created Check constraint in the tables. We filter the data in the view based on year.

 

 

 

It will get the data from the specific table and avoid table scan on the other table.

 

 

 

So now we have created the partitioned view. Whenever we apply the filter. It will directly fetch the data on the specific table and avoid table scan on other tables.

 

 

 

Step 7 - Inserting values using partitioned view

 

Using Partitioned view we can insert the values on specific table based on check condition. Consider we are inserting 3 different year data using the partitioned view. It will insert the row based on the year to the respective table.

 

 

 

Query

USE [PartionedViewsDemo]
GO

--INSERTING VALUES IN RESPECTIVE TABLES USING VIEW

--2019 YEAR RECORD
INSERT INTO [dbo].[VW_Order]  ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
    VALUES  (201903,'IND','2019-03-01',2019 )

--2018 YEAR RECORD
INSERT INTO [dbo].[VW_Order]  ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
VALUES  (201803,'IND','2018-03-02',2018 )

--2017 YEAR RECORD
INSERT INTO [dbo].[VW_Order]  ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])
VALUES  (201703,'IND','2017-03-03',2017 )

Using Check constraint it will insert the record to its respective table.

 

 

 

Important notes on partitioned view

• The partitioning column is a part of the PRIMARY KEY of the table.
• It cannot be a computed, identity, default, or timestamp column.
• If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view. To meet the conditions of the partitioned view, ensure that there is only one partitioning constraint on the partitioning column.
• There are no restrictions on the updatability of the partitioning column.

Reference

 

For a detailed reference on partitioned view, please visit Microsoft.com documents section.