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