how to create partition view in SQL Server

 

how to create partition view in SQL Server

 

Partition using views allows the data in large table to distribute to multiple tables and access all these tables using a view. Query optimized will access the partitioned table based on the partitioned column used in where clause.

 

For example, customers table has data from 3 departments (D1,D2,D3) with large volume of records.

Isolate the customers based on the departments into 3 tables and access all the tables through a single view as vw_customers. All insert/update/delete/select can be performed on vw_customers view

 

Create 3 tables as below

 

 

use tempdb

go

 

CREATE table CUSTOMERS_1 ( iid int NOT NULL,cname varchar(90),DEPT VARCHAR(9) NOT NULL)

CREATE table CUSTOMERS_2 ( iid int NOT NULL,cname varchar(90),DEPT VARCHAR(9)NOT NULL)

CREATE table CUSTOMERS_3( iid int  NOT NULL,cname varchar(90),DEPT VARCHAR(9)NOT NULL)

 

 

Insert 1 record in each table for demo

 

INSERT INTO CUSTOMERS_1 SELECT 1,'C1','D1'

INSERT INTO CUSTOMERS_2 SELECT 2,'C2','D2'

INSERT INTO CUSTOMERS_3 SELECT 3,'C3','D3'

 

Check the data inserted on all these tables

 

 

 

SELECT * FROM CUSTOMERS_1

SELECT * FROM CUSTOMERS_2

SELECT * FROM CUSTOMERS_3

 

 

Create a view and include all these tables

 

 

CREATE VIEW VW_CUSTOMERS

AS

SELECT * FROM CUSTOMERS_1

UNION ALL

SELECT * FROM CUSTOMERS_2

UNION ALL

SELECT * FROM CUSTOMERS_3

 

Now, the below query for D1 is on customers_1 , but looking at the execution plan in the below screenshot. Its accessing all the tables instead accessing only customers_1 table

 

 

 

SELECT * FROM VW_CUSTOMERS WHERE DEPT IN ('D1')

 

clip_image001[8]

 

clip_image003[8]

 

 

 


Modify this view as a partitioned view. The basic 3 prerequisite to create a partition view is

1)create check constraint on each table

2)create composite clustered index on the partioned column and the primary key

3)schema bind the view

 

Step : 1 - create check constraint on each table

 

ALTER TABLE CUSTOMERS_1 ADD CONSTRAINT CHK_1  CHECK( DEPT = 'D1')

ALTER TABLE CUSTOMERS_2 ADD CONSTRAINT CHK_2  CHECK( DEPT = 'D2')

ALTER TABLE CUSTOMERS_3 ADD CONSTRAINT CHK_3  CHECK( DEPT = 'D3')

 

 

Step : 2 – create composite index on the partition column and the primary key

 


ALTER TABLE CUSTOMERS_1 ADD CONSTRAINT PK1 PRIMARY KEY ( IID,DEPT)

ALTER TABLE CUSTOMERS_2 ADD CONSTRAINT PK2 PRIMARY KEY ( IID,DEPT)

ALTER TABLE CUSTOMERS_3 ADD CONSTRAINT PK3 PRIMARY KEY ( IID,DEPT)

 

 

Step 3  - Alter the view to bind the schema

 

alter VIEW VW_CUSTOMERS WITH SCHEMABINDING

AS

SELECT IID,CNAME,DEPT FROM DBO.CUSTOMERS_1

UNION ALL

SELECT IID,CNAME,DEPT FROM DBO.CUSTOMERS_2

UNION ALL

SELECT IID,CNAME,DEPT FROM DBO.CUSTOMERS_3

 

 

Now , test the same query and check the results and execution plan

 

SELECT * FROM VW_CUSTOMERS WHERE DEPT IN ('D1')

 

 

clip_image004[8]

 

Test , all insert,update, delete queries through this view

 

To practice more on the partition view , you can create a similar table with date field and partition the tables based on months and test your results.

 

 

 

Post a Comment