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 scans we can use a Partitioned view.

Definition

A partitioned view is a view defined by a UNION ALL of the 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.

The 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 a check constraint. We need to define the check constraint in every table based on the column used in the partitioned view. The partitioned view uses union all to select all the participating tables as one result. When we apply where condition in the check constraint column in the view, It will directly fetch the data on the specific table using the 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 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 a 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),
       (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),
       (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),
       (201902,'IND','2019-01-02',2019)

Now select the table’s data. It shows 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 a view without check constraint.

Now we are creating a 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 the 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 the order year column. We have filtered the record using the 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 tables. It searches records 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 a check constraint in the Order year table. The check constraint must be a part of the primary key constraint. We are adding the year as a check constraint. So that the query will get the specific year record from the respective table and avoid table scans 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 a Partitioned view.

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

It will get the data from the specific table and avoid table scans 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 scans on other tables.

Step 7. Inserting values using a partitioned view

Using the Partitioned view we can insert the values on specific tables based on check conditions. Consider we are inserting 3 different years of 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 the Check constraint it will insert the record into 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 partitioned. 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 the Microsoft.com documents section.