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')

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')

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.