How To Create A Partitioned View In SQL Server

In this article, you will learn how to create a partitioned view and its uses in SQL server database.

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).
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Query
  1. CREATE DATABASE PartionedViewsDemo  
  2. --Creating Order tables for Year 2019,2018 and 2017  
  3. USE PartionedViewsDemo   
  4. GO  
  5. CREATE TABLE Orders (  
  6. OrderId INT NOT NULL,  
  7. OrderCountryCode CHAR(3) NOT NULL,  
  8. OrderDate DATETIME NULL,  
  9. OrderYear INT NOT NULL   
  10. CONSTRAINT PK_Order PRIMARY KEY (OrderId,OrderYear));  
  11.    
  12. GO  
  13. CREATE TABLE Orders_2018 (  
  14. OrderId INT NOT NULL,  
  15. OrderCountryCode CHAR(3) NOT NULL,  
  16. OrderDate DATETIME NULL,  
  17. OrderYear INT NOT NULL   
  18. CONSTRAINT PK_Order_2018 PRIMARY KEY (OrderId,OrderYear)  
  19. );  
  20.    
  21. GO  
  22. CREATE TABLE Orders_2017 (  
  23. OrderId INT NOT NULL,  
  24. OrderCountryCode CHAR(3) NOT NULL,  
  25. OrderDate DATETIME NULL,  
  26. OrderYear INT NOT NULL   
  27. CONSTRAINT PK_Order_2017 PRIMARY KEY (OrderId,OrderYear)  
  28. );  
Step 2 - Inserting Sample records
 
Inserting sample records in all 3 tables.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Query
  1. USE [PartionedViewsDemo]  
  2. GO  
  3.   
  4. -- Record for year 2017  
  5. INSERT INTO [dbo].[Orders_2017] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  6.   VALUES    (201701,'IND','2017-01-01',2017 )  
  7.   
  8.   INSERT INTO [dbo].[Orders_2017] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  9.   VALUES    (201702,'IND','2017-01-02',2017 )  
  10.   
  11. -- Record for year 2018  
  12. INSERT INTO [dbo].[Orders_2018] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  13.   VALUES    (201801,'IND','2018-01-01',2018 )  
  14.   
  15.   INSERT INTO [dbo].[Orders_2018] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  16.   VALUES    (201802,'IND','2018-01-02',2018 )  
  17.   
  18.   -- Record for year 2019  
  19. INSERT INTO [dbo].[Orders] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  20.   VALUES    (201901,'IND','2019-01-01',2019 )  
  21.   
  22.   INSERT INTO [dbo].[Orders] ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  23.   VALUES    (201902,'IND','2019-01-02',2019 )  
Now select the table’s data. It showing data based on the year.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Query
  1. USE [PartionedViewsDemo]  
  2. GO  
  3.   
  4. SELECT [OrderId] ,[OrderCountryCode],[OrderDate],[OrderYear] FROM [dbo].[Orders]  
  5. SELECT [OrderId] ,[OrderCountryCode],[OrderDate],[OrderYear] FROM [dbo].[Orders_2018]  
  6. SELECT [OrderId] ,[OrderCountryCode],[OrderDate],[OrderYear] FROM [dbo].[Orders_2017]  
  7.   
  8. 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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Query
  1. USE [PartionedViewsDemo]  
  2. GO  
  3.   
  4. CREATE VIEW VW_Order  
  5. AS  
  6. SELECT OrderId,OrderCountryCode,OrderDate,OrderYear  FROM Orders  
  7. UNION ALL  
  8. SELECT OrderId,OrderCountryCode,OrderDate,OrderYear  FROM Orders_2018  
  9. UNION ALL  
  10. SELECT OrderId,OrderCountryCode,OrderDate,OrderYear  FROM Orders_2017  
  11. GO  
Selecting the value from the View. It will display all the records in the view.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Step 4 - Filtering Data in the View
 
Now we are applying where condition in the view to filter the data based on the year.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
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
  1. USE [PartionedViewsDemo]  
  2. GO  
  3.   
  4. SET STATISTICS IO ON  
  5.   
  6. SELECT * FROM VW_Order WHERE ORDERYEAR=2019  
  7. 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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Query
  1. USE [PartionedViewsDemo]  
  2. GO  
  3.   
  4. --CHECK CONSTRAINT FOR PARTITONED VIEW  
  5.   
  6. ALTER TABLE Orders ADD CONSTRAINT CK_Order CHECK (OrderYear >= 2019)  
  7.   
  8. ALTER TABLE Orders_2018 ADD CONSTRAINT CK_Order_2018 CHECK (OrderYear = 2018)  
  9. 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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
It will get the data from the specific table and avoid table scan on the other table.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
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.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
Query
  1. USE [PartionedViewsDemo]  
  2. GO  
  3.   
  4. --INSERTING VALUES IN RESPECTIVE TABLES USING VIEW   
  5.   
  6. --2019 YEAR RECORD  
  7. INSERT INTO [dbo].[VW_Order]  ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  8.     VALUES  (201903,'IND','2019-03-01',2019 )  
  9.   
  10. --2018 YEAR RECORD  
  11. INSERT INTO [dbo].[VW_Order]  ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  12. VALUES  (201803,'IND','2018-03-02',2018 )  
  13.   
  14. --2017 YEAR RECORD  
  15. INSERT INTO [dbo].[VW_Order]  ([OrderId],[OrderCountryCode],[OrderDate] ,[OrderYear])  
  16. VALUES  (201703,'IND','2017-03-03',2017 )  
Using Check constraint it will insert the record to its respective table.
 
How To Create A Partitioned View And Its Uses In SQL Server Database 
 
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.