Thursday, July 31, 2014

Partitioned View in SQL Server

We have already understood about the standard Views and Indexed Views in my previous post. If you want to know about Standard Views please Click Here, if you want to know about Indexed Views please Click Here.

In this article I will talk about Partitioned Views and what are the types of Partitioned View?

Partitioned View allows a large table to be split horizontally into many smaller member tables. The data is distributed among member tables based on range of data values in one of the columns from our Main table for example My Sales table has millions of records for 2013, This sales table stores monthly sales transaction records. I can distribute 12 months data into 12 different member tables (one for each month). These 12 tables will be called as member tables. The data range for reach member table is defined with a CHECK constraint on the Month column (Partitioned Column). You have to make sure structure of all member tables is same.


Now partitioned view will be created by using UNION ALL on all member tables and it appears as a single Sales Table to the end users. When we execute Select statements against our Partitioned view with a search condition using where clause on partition column (Month name is our case), the query optimizer use the CHECK constraint definitions to find which member tables contains the searched data.

Partitioned Views are of two types
1-      Local Partitioned View
2-      Distributes Partitioned View

Local Partitioned View- View that joins member tables from same instance of SQL Server will be called as local Partitioned view

Distributed Partitioned View- View that joins member tables from multiple different SQL Servers will be called as Distributed Partitioned View

Let’s understand the concept using a simple example

/*CREATING A NEW DATABASE*/
USE MASTER
IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')
DROP DATABASE TEACHMESQLSERVER
GO
CREATE DATABASE TEACHMESQLSERVER

/*CREATING THREE NEW TABLES AND POPLUTAING DATA*/
GO
USE TEACHMESQLSERVER
GO
CREATE TABLE DEP (DEPID INT PRIMARY KEY,DEPNAME VARCHAR(MAX))
GO
CREATE TABLE EMP (EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX),DEPID INT FOREIGN KEY REFERENCES DEP(DEPID))
GO
CREATE TABLE SALES(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT, SALESMONTH VARCHAR(10),EMPID INT )
GO
INSERT INTO DEP VALUES(10,'HR'),(11,'SALES'),(12,'IT')
GO
INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)
GO
INSERT INTO SALES VALUES(1,100,2013,'JAN',1),(2,1000,2013,'JAN',5),(3,1050,2013,'JAN',1),(4,13400,2013,'FEB',3),(5,1010,2013,'FEB',1),(6,10230,2013,'MAR',2),
(7,89998,2013,'MAR',3),(8,102320,2013,'MAR',1),(9,11100,2013,'MAR',2),(10,10430,2013,'APR',1),(11,10,2013,'APR',4),(12,1908700,2013,'MAY',3),(13,10320,2013,'JUN',1)
,(14,100,2013,'JUN',1),(15,109980,2013,'JUL',4),(16,1590,2013,'AUG',5),(17,90000,2013,'AUG',1),(18,9100,2013,'SEP',2),(19,1000,2013,'OCT',1),(20,1009,2013,'NOV',5),(21,100,2013,'DEC',4)

/*PARTITIONING SALES TABLE DATA INTO 12 MEMBER TABLES ON MONTH PARTITIONED COLUMN*/

CREATE TABLE SALESJAN(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='JAN'),EMPID INT )
CREATE TABLE SALESFEB(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='FEB'),EMPID INT )
CREATE TABLE SALESMAR(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='MAR'),EMPID INT )
CREATE TABLE SALESAPR(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='APR'),EMPID INT )
CREATE TABLE SALESMAY(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='MAY'),EMPID INT )
CREATE TABLE SALESJUN(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='JUN'),EMPID INT )
CREATE TABLE SALESJUL(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='JUL'),EMPID INT )
CREATE TABLE SALESAUG(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='AUG'),EMPID INT )
CREATE TABLE SALESSEP(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='SEP'),EMPID INT )
CREATE TABLE SALESOCT(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='OCT'),EMPID INT )
CREATE TABLE SALESNOV(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='NOV'),EMPID INT )
CREATE TABLE SALESDEC(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013), SALESMONTH VARCHAR(10) CHECK(SALESMONTH='DEC'),EMPID INT )
/*POPULATINF DATA INTO MEMBER TABLES*/
GO
INSERT INTO SALESJAN SELECT *FROM SALES WHERE SALESMONTH='JAN'
INSERT INTO SALESFEB SELECT *FROM SALES WHERE SALESMONTH='FEB'
INSERT INTO SALESMAR SELECT *FROM SALES WHERE SALESMONTH='MAR'
INSERT INTO SALESAPR SELECT *FROM SALES WHERE SALESMONTH='APR'
INSERT INTO SALESMAY SELECT *FROM SALES WHERE SALESMONTH='MAY'
INSERT INTO SALESJUN SELECT *FROM SALES WHERE SALESMONTH='JUN'
INSERT INTO SALESJUL SELECT *FROM SALES WHERE SALESMONTH='JUL'
INSERT INTO SALESAUG SELECT *FROM SALES WHERE SALESMONTH='AUG'
INSERT INTO SALESSEP SELECT *FROM SALES WHERE SALESMONTH='SEP'
INSERT INTO SALESOCT SELECT *FROM SALES WHERE SALESMONTH='OCT'
INSERT INTO SALESNOV SELECT *FROM SALES WHERE SALESMONTH='NOV'
INSERT INTO SALESDEC SELECT *FROM SALES WHERE SALESMONTH='DEC'
GO
/*CREATING PARTITIONED VIEW*/
GO
IF EXISTS(SELECT NAME FROM SYS.VIEWS WHERE NAME='PARTITIONEDVIEW')
DROP VIEW PARTITIONEDVIEW
GO
CREATE VIEW PARTITIONEDVIEW
AS
SELECT * FROM SALESJAN
UNION ALL
SELECT * FROM SALESFEB
UNION ALL
SELECT * FROM SALESMAR
UNION ALL
SELECT * FROM SALESAPR
UNION ALL
SELECT * FROM SALESMAY
UNION ALL
SELECT * FROM SALESJUN
UNION ALL
SELECT * FROM SALESJUL
UNION ALL
SELECT * FROM SALESAUG
UNION ALL
SELECT * FROM SALESSEP
UNION ALL
SELECT * FROM SALESOCT
UNION ALL
SELECT * FROM SALESNOV
UNION ALL
SELECT * FROM SALESDEC

/*REFERENCEING PARTITIONED VIEW, PLEASE PRESS CTRL+M BEFORE RUNNING THE BELOW QUERY, IT WILL SHOW THE RESULTSET WITH THE EXECUTION PLAN*/
GO
SELECT * FROM PARTITIONEDVIEW WHERE SALESMONTH ='JAN'

Execution plan for above query:

The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the SALESJAN table. Therefore, it limits its search to SALESJAN table.

Don’t forget to drop the database, I hope you enjoyed the article.


No comments: