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.

Indexed View in SQL Server

We have already understood about the standard Views and types of Standard View in my previous post. If you want to know about Standard Views please Click here to know about Standard Views

In this article I will talk about Indexed Views. What is Indexed View?

When we create Index on Simple or Complex view, we call that view as Indexed View. If we create Unique Clustered index on any view that View will be called as Index View. Indexed Views are also known as Materialized view, this means result set of indexed view is computed and stored in the database just like a table with a clustered Index is stored.

 If our View is created from multiple table joins and has aggregation that process many records in that case Query performance will be slow. Indexed views can improve query performance drastically.

Indexed view can’t improve query performance for the those queries which are running against a database which has multiple Writes and Updates ,Queries which don’t has join and aggregations.

If you want to create a Clustered Index on your view then you have to make sure that your view was created with Schemabinding option. I have already discussed about the Schemabinding in my previous post Click here to know about Schemabinding option on Views. If your view was not created with Schemabinding option then you can’t create Index on non Schemabinding views.

Let’s take one example to explain the concept.

/*CREATING A NEW DATABASE*/

USE MASTER

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')

DROP DATABASE TEACHMESQLSERVER

GO

CREATE DATABASE TEACHMESQLSERVER

/*CREATING TWO 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

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)

/*CREATING A NEW COMLEX VIEW*/

GO

IF EXISTS(SELECT NAME FROM SYS.VIEWS WHERE NAME='COMPLEXVIEW')

DROP VIEW COMPLEXVIEW

GO

CREATE VIEW COMPLEXVIEW

WITH SCHEMABINDING

AS

SELECT DBO.EMP.EMPID,EMPNAME,DEPNAME FROM DBO.DEP INNER JOIN DBO.EMP ON DBO.DEP.DEPID= DBO.EMP.DEPID

/*REFERENCEING COMPLEX VIEW*/

GO

SELECT * FROM COMPLEXVIEW

/*CREATING UNIQUE CLUSTERED INDEX*/

GO

CREATE UNIQUE CLUSTERED INDEX INDEXEDCOMPLEXVIEWIN ON COMPLEXVIEW  (EMPID ASC)

Congratulations you have successfully created a Indexed view. If you want to check this you can check from below queries

GO

SELECT * FROM SYS.all_views WHERE NAME='COMPLEXVIEW'

/*COPY OBJECT_ID FROM PREVIOUS QUERY AND PASS IT INTO BELOW QUERY*/

GO

SELECT * FROM SYS.indexes WHERE OBJECT_ID=101575400

You can find it using below query also

GO

SELECT * FROM SYS.indexes WHERE name='INDEXEDCOMPLEXVIEWIN'

I hope you enjoyed the tutorial; 

There are couple of topics which are related to Views don't forget to visit them.

Types of Views in SQL Server, How to Perform DML Operations on Views?

Indexed Views in SQL Server

Partitioned View in SQL Server

Types of Views in SQL Server

I have already discussed about View in my previous post, In this article I will discuss about different types of view.

In SQL Server Views has been divided into three categories

1-                  Standard Views

2-                  Indexed Views

3-                  Partitioned Views

In this article we will explore Standard Views, To know more about Index Views and Partitioned view click on above links.

Standard Views

We can create view on single table or combination on multiple tables. When we create a view on a single table without using any aggregated function then we call it as Simple View, when we create a view on combination of multiple tables or if any aggregated function (i.e GROUP BY clause, HAVING Clause, Distinct Functions, Group Functions, Functions call) is used on single table then we call it as Complex View.

We can perform DML Operation on Simple view but all Not Null columns must be present in DML command and in View. This means Simple Views are Updatable Views; we will explore this later with a simple example.

We can’t perform DML Operation on Complex view by default; this means Complex Views are Non Updatable Views. We can make complex view updatable with the help of Instead of Trigger. We will explore this with a simple example in this article.

Now we can say Standard Views are of two types.

1-                  Simple View (Updatable View)

2-                  Complex View (Non Updatable View)

Simple View (Updatable View) explanation with example

Let’s create a simple view and perform any DML operation

/*I AM USING MY OLD DATABASE, YOU CAN USE YOUR DATABASE OTHERWISE YOU CAN CREATE NEW DATABASE NAME AS TEACHMESQLSERVER USING THE BELOW COMMAND*/

Create DATABASE TEACHMESQLSERVER

GO

Use TEACHMESQLSERVER

Go

/*CREATING TWO TABLES */

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

/*INSERTING DATA INTO BOTH TABLES */

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

/*CREATING A SIMPLE VIEW*/

CREATE VIEW SIMPLEVIEW

AS

SELECT DEPID,DEPNAME FROM DEP

GO

/*REFERENCING VIEW TO FETCH DATA */

SELECT * FROM SIMPLEVIEW

You have successfully created a simple view now let’s perform any DML Operation on SimpleView.

/*DML OPERATION PERFORMING ON SIMPLE VIEW*/

GO

INSERT INTO SIMPLEVIEW VALUES(13,'MARKETING') --INSERTING A NEW RECORD IN SIMPLEVIEW

GO

SELECT * FROM SIMPLEVIEW -- NEW RECORD HAS BEEN ADDED THROUGH VIEW, THIS RECORD WAS ADDED INTO BASE TABLE THAT YOU CAN FIND USING BELOW QUERY

GO

SELECT * FROM DEP -- ANY DML OPERATION PERFORMED ON VIEW MAKES IMPACT ON REFERENCED TABLES(BASE TABLES)

GO

DELETE FROM SIMPLEVIEW WHERE DEPID=13 -- DELETING NEWLY INSERTED RECORD IN PREVIOUS STEP FROM SIMPLEVIEW

GO

SELECT * FROM SIMPLEVIEW

We have successfully inserted and deleted records in simple view in our previous examples but there are couple of point we need to make sure if you want to perform DML operation on simple view.

1- Our view must have all NOT NULL columns and primary key column, otherwise INSERT Command will fail.

2- The columns being modified in the view must not be calculated column or must not have aggregated function.

3- The columns being modified can’t be affected by GROUP BY, HAVING or DISTINCT Clause.

Let’s go through with one example to understand the concept

GO

TRUNCATE TABLE EMP -- TRUNCATING THE TABLE

GO

DELETE FROM DEP -- WE CAN'T USE TRUNCATE COMMAND ON THIS TABLE BECAUSE IT IS BEING REFERENCED WITH FOREIGN KEY IN EMP TABLE , SO USING DELETE COMMAND

GO

ALTER TABLE DEP ADD DEPFULLNAME VARCHAR(MAX) NOT NULL -- ADDING A NEW COLUMN WITH NOT NULL CONSTRAINT

GO

/*INSERTING DATA INTO BOTH THE TABLES*/

INSERT INTO DEP VALUES(10,'HR','HUMAN RESOURCES'),(11,'SALES','SALES'),(12,'IT','INFORMATION TECHNOLOGY')

GO

INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)

GO

As you know we have already added a new column DEPFULLNAME which has NOT NULL constraint. This column is not present in our previously created View (SimpleView). Let’s try to insert a new record into SimpleView.

INSERT INTO DEP (DEPID) VALUES (13)

GO

The above query failed and gave below error

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'DEPFULLNAME', table 'TEACHMESQLSERVER.dbo.DEP'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Because behind the picture our view was trying to insert this record in the base table DEP, our base table has three columns DEPID which is primary key, DEPNAME (NULL is allowed) and DEPFULLNAME (NULL is not allowed). Our query was trying to insert NULL records for one of the NOT NULL column (DEPFULLNAME) that is why it gave error.

Let’s ALTER the DEPFULLNAME column to accept NULL and try to run the same query again.

ALTER TABLE DEP ALTER COLUMN DEPFULLNAME VARCHAR(MAX)

GO

INSERT INTO DEP (DEPID) VALUES (13)

GO

SELECT * FROM SIMPLEVIEW

GO

SELECT * FROM DEP

We were able to successfully insert a new record in the base table through view.

Complex View (Non Updatable View) explanation with example

Let’s create a complex view and try to perform any DML operation

CREATE VIEW COMPLEXVIEW

AS

SELECT DEPNAME,EMPNAME FROM DEP INNER JOIN EMP ON DEP.DEPID= EMP.DEPID

GO

SELECT * FROM COMPLEXVIEW

GO

We have successfully created a complex view and you can clearly see data is coming from two different tables in complex view.

As I have already told Complex Views are Non Updatable Views means you can’t perform any DML Operation on complex view. If you don’t trust me you can try the below query to perform insert operation.

INSERT INTO COMPLEXVIEW VALUES(13) -- Failed with error

GO

INSERT INTO COMPLEXVIEW VALUES('MARKETING','RAKESH') -- Failed with error

Error Message

Msg 4405, Level 16, State 1, Line 1

View or function 'COMPLEXVIEW' is not updatable because the modification affects multiple base tables.

Why complex views are non-updatable it is because our complex view have columns from multiple tables and when we try to insert new record through view It can’t understand which column belongs to which table. We can make Complex view updatable by using Instead of Trigger.

Our task is to distribute all columns present in DML Command to their base tables.

Let’s make complex view updatable using Instead of Trigger

We are creating Instead of Trigger (Click here to know about Triggers in SQL Server) on COMPLEXVIEW which takes data from Insert Command and insert into their Base Tables.

GO

CREATE TRIGGER COMPLEWVIEWUPDATABLE ON COMPLEXVIEW

INSTEAD OF INSERT

AS

DECLARE @DEPNAME VARCHAR(MAX),@EMPNAME VARCHAR(MAX)

SELECT @DEPNAME=DEPNAME, @EMPNAME=EMPNAME FROM INSERTED

INSERT INTO DEP (DEPID,DEPNAME)VALUES((SELECT MAX(DEPID)+1 FROM DEP),@DEPNAME)

INSERT INTO EMP (EMPID,EMPNAME) VALUES((SELECT MAX(EMPID)+1 FROM EMP),@EMPNAME)

GO

INSERT INTO COMPLEXVIEW VALUES('MARKETING',’RAKESH’)

GO

This time our same Insert Statement was successful, and it inserted MARKETING into DEP table and RAKESH into EMP Table.

Let’s check

SELECT * FROM EMP

GO

SELECT * FROM DEP

New records were added successfully 

We can't use Delete Command on complex view; you have to create Instead of Trigger if you want to perform Delete Operation. 

You can Use UPDATE Command on complex view. Let’s take one example to explain this

GO

UPDATE COMPLEXVIEW SET DEPNAME='IT' WHERE DEPNAME='FINANCE'

GO

SELECT * FROM DEP