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:
Post a Comment