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.


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.

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