A View can be thought as either a virtual table or a stored SQL Query in the database. Like a real table has named columns and rows of data, a view also has set of named columns and rows of data but the only difference is View never stores the data like a real table. The data associated with the view is not stored in the view; it is stored in the base table of view. We can create a new view on view. We can also create trigger and Index on view.
Let’s create a database
--CREATE A NEW DATABASE TEACHMESQLSERVER
USE master
GO
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')
DROP DATABASE TEACHMESQLSERVER
GO
CREATE DATABASE TEACHMESQLSERVER
GO
USE TEACHMESQLSERVER
GO
We have created our database; now will create one table in this database.
-- CREATE A NEW TABLE EMP IN TEACHMESQLSERVER DATABASE
CREATE TABLE EMP(EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX), SALARY MONEY)
GO
INSERT INTO EMP VALUES(1,'A',100),(2,'k',1005),(3,'B',3100),(4,'C',1100),(5,'D',1009),(6,'E',1700),(7,'F',1040),(8,'G',100),(9,'H',1000)
GO
SELECT * FROM SYS.objects WHERE name='EMP'
Now we will create a view on this EMP table.
Syntax to create view:
CREATE VIEW viewname
WITH <View Attributes> -- (This is Optional)
AS
SELECT STATEMENT -- (Your SQL Query, This is mandatory)
WITH CHECK OPTION -- (This is optional)
-- CREATE A VIEW ON THIS EMP TABLE
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP
You can find this view information from system catalog Views. Click here to know about System Catalog Views
What is stored in the database is a SELECT STATEMENT that you can find using the below query.
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172 BUT IT WILL BE DIFFERENT IN YOUR CASE.
A User can use this view by referencing the view name (EMPVIEW) in SQL statements in the same way a table is referenced.
GO
SELECT * FROM EMPVIEW
If you will run the SELECT STATEMENT (SELECT EMPID,EMPNAME FROM EMP) which was used to create EMPVIEW then you will get the same above result set which means the result set of the Select Statement forms the virtual table which is returned by the view.
The rows and columns are produced dynamically from the base tables (used in the select statement) when the view is referenced.
As you can notice in our EMP table we have three columns EMPID, EMPNAME, SALARY. But when you reference View it only gives you EMPID and EMPNAME. It is because we have not included the SALARY column at the time of CREATE VIEW. So you can say A View acts as a filter on the underlying tables referenced in the view. We didn’t want other users to know about the other EMP’s salary so we didn’t include the Salary column in Create View Statement.
A View can be created from one or more tables or views in the current or other database. There are no restrictions on querying through views but there are few restrictions on modifying data through View, Yes we can perform DML Operation on Views. When you perform any DML operation it affects the referenced tables used in view.
Modify View
You can modify View using ALTER COMMAND
ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
GO
Drop VIEW
If you want to delete view then you can delete it using DROP COMMAND
DROP VIEW EMPVIEW
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW'
We have gone through the basics of view, let’s go through WITH <VIEW ATTRIBUTES> and WITH CHECK OPTION
VIEW ATTRIBUTES
This is optional; we have two attributes ENCRYPTION and SCHEMABINDING. Let’s explore the use of these two attributes one by one.
1- ENCRYPTION
When you create any view without ENCRYPTION in that case you can see view definition in Syscomments system table, below example will explain it
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172 BUT IT WILL BE DIFFERENT IN YOUR CASE.
You can see view definition from previous query in text column.
If you want to hide that information from the system tables then you have to create view with ENCRYPTION, below example will explain it. Let’s alter the view with ENCRYPTION option.
ALTER VIEW EMPVIEW
With encryption
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=53575229 -- IN MY CASE IT IS 53575229 BUT IT WILL BE DIFFERENT IN YOUR CASE.
check the text column returned from the above query it is showing NULL
2- SCHEMABINDING
Suppose you have create a view which has columns column1, column2, column3, what if user delete column3 from the referenced table. In that case your view will not work it will give error because when we created the view at that time it saved the view definition with all three columns. Now one of the column has been dropped so when we will reference the view it will search for all three columns but it will not find the column3. How can we handle this kind of problem? Yes we can handle this problem what we have to do is we have to create dependencies between view columns and referenced table columns.
Using schemabinding option we create this dependence. So when we create a view with schemabinding option.
The base table or tables can’t be modified in a way that would affect the view definition.
If you want to modify the table then first either you have to drop the view or alter the view without schemabinding option.
When you use Schemabinding , the select statement must include the two part names (schema.object) of tables that are referenced.
We can’t use Select * when we create a view with schemabinding option, we have to specify each column name.
All referenced objects must be in same database.
Let’s go with example to understand the concept, we will alter our previously created view.
GO
DROP VIEW EMPVIEW --If view is already exists
GO
CREATE VIEW EMPVIEW --View Without Schemabinding
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
We have create a view without schemabinding, now let’s drop salary column from the referenced table.
GO
ALTER TABLE EMP DROP COLUMN SALARY
You successfully dropped the salary column, now let’s query against our EMPVIEW
Go
SELECT * FROM EMPVIEW
Your view didn't return any result set but it gave below error
Msg 207, Level 16, State 1, Procedure EMPVIEW, Line 3
Invalid column name 'SALARY'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'empview' because of binding errors.
User was able to drop Salary Column from the referenced table because our view was not created with schemabinding option. Now let’s modify the view with schemabinding option.
GO
ALTER VIEW EMPVIEW --View Schemabinding
WITH SCHEMABINDING
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
We have create a view schemabinding, now let’s drop EMPNAME column from the referenced table.
GO
ALTER TABLE EMP DROP COLUMN EMPNAME
You can’t drop the column because empname is dependent in view empview, you will get below error
Msg 5074, Level 16, State 1, Line 1
The object 'EMPVIEW' is dependent on column 'EMPNAME'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN EMPNAME failed because one or more objects access this column.
Last time we successfully dropped the salary column because we didn’t use schemabinding for view, but this time we used schemabinding option that is why we were not able to drop the column. If you want to drop the EMPNAME column
Then first you have to either drop the view or alter view with no schemabinding option.
GO
ALTER VIEW EMPVIEW --View Without Schemabinding
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
GO
ALTER TABLE EMP DROP COLUMN EMPNAME
You successfully dropped the EMPNAME column, now you have to modify your view otherwise it will give error i.e. Invalid column name EMPNAME
Refreshing Views
Suppose you are working with non schema bind view if base table has been modified which affected your view, In that case your view will not work it will give an error. You have to refresh your View definition to make it workable.
There are couple of ways to update View definition, one you can use alter command and update the view definition another is using Stored Procedure you can refresh Views definition.
i.e
Execute sp_refreshview 'Your View Name'
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
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'
Partitioned View in SQL Server
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),EMPIDINT )
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.
WITH CHECK OPTION in View
I have already discussed about Views in my previous post. In this article we will know the use of WITH CHECK OPTION on Views.
When we create a view without CHECK Option, in that case we can perform INSERT, UPDATE, DELETE operation on View. If records are not in scope of View and we want to perform DELETE and UPDATE operation on those records then DELETE and UPDATE operation will be succeed without any modification in the base table but INSERT operation will succeed and it will insert new records in base table.
When we create a view WITH CHECK OPTION, in that case we can’t perform INSERT, UPDATE, DELETE operation on those records which are not satisfying the criteria set within the select statement of View. If we try to perform DELETE and UPDATE operation on out of scope records then the operation will be succeed without any modification in the base table.
Let’s go through a simple example
USE TEACHMESQLSERVER
GO
-- Creating a new table and popluating data into the table
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
CREATE TABLE EMP( EMPID INT, EMPNAME VARCHAR(MAX))
GO
INSERT INTO EMP VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')
GO
-- Creating a View without CHECK Option
IF OBJECT_ID('EMPVIEW','V') IS NOT NULL
DROP VIEW EMPVIEW
GO
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
GO
SELECT * FROM EMPVIEW
--Performing DML Operation on VIEW
GO
INSERT INTO EMPVIEW VALUES(6,'F') -- Successfully Inserted new record in Base Table
GO
SELECT * FROM EMP
GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
SELECT * FROM EMP
WITH CHECK OPTION EXAMPLE
-- ALTERING the View WITH CHECK OPTION
GO
ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
WITH CHECK OPTION
--Performing DML Operation on VIEW
GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
INSERT INTO EMPVIEW VALUES(7,'H') -- Failed with below error, because the INSERT statement is not satisfying the Cretria set in Select Statement, VIEW can have all records where EMPID<5 in this case EMPID is greater than 5 so it failed.
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
GO
INSERT INTO EMPVIEW VALUES(0,'I') -- Successfully Inserted new record in Base Table because it satisfied EMPVIEW Criteria.
GO
OVERVIEW
A virtual entity of DB which looks like a table but does not store the records physically.
It is something like window over a table
Complex query can be converted into view and can be used in FROM clause
A table can be dropped even though view is existing which is using this table. However view created with SCHEMABINDING option won’t allow dropping a table
All DML operations are possible on View but again it has some limitations.
It shouldn't violate any constraint like foreign key, unique, identity, not null, etc.
View should point to only 1 table and not multiple tables
View should not have GROUP by clause or any arithmetic functions
Won’t allow SELECT INTO, ORDER BY
We can use ORDER BY but it should have TOP operator then only we can use it
Index can be created on view but with following conditions. If we do this, view will start storing the data physically
View should be schemabound
First we have to create unique clustered index then only we can create other non-clustered indexes
Creating index on view is sort of alternate solution of partition.
SYNTAX
Basic Syntax
CREATE VIEW vwName
AS
SELECT QUERY
With Schemabinding syntax
CREATE VIEW vw
WITH SCHEMABINDING
AS
SELECT eid, ename , salary, dob
FROM dbo.Employee
WHERE Salary > 10000
Common Error in case of Materialized View
Introduction
In this article we are trying to discuss about some common error related to materialize view. To understand the nature of the Error we are going to take an simple example. Hope it will be informative.
Example
Step-1 [ Creating the Base Table ]
IF OBJECT_ID(N'tbl_StudentDetails', N'U') IS NOT NULL
DROP TABLE tbl_StudentDetails;
CREATE TABLE tbl_StudentDetails
(
StdRoll INT NOT NULL IDENTITY PRIMARYKEY,
StdName VARCHAR(50) NOT NULL
);
GO
Step-2 [ Insert some records in the Base Table ]
INSERT INTO tbl_StudentDetails
(StdName)
VALUES ('Joydeep Das'), ('Santinath Mondal'), ('Avijit da');
GO
Step-3 [ Now Create a Simple View from the Base table ]
CREATE VIEW view_StudentDetails
AS
SELECT * FROM tbl_StudentDetails;
We find that the view is Created Successfully and there is no problem in case of simple view creation.
Step-4 [ Now Create a Materialized View with above Example ]
CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT * FROM tbl_StudentDetails;
The error occurred:
Msg 1054, Level 15, State 6, Procedure view_StudentDetails, Line 26
Syntax '*' is not allowed in schema-bound objects.
So we are not providing star (*) in Materialized view and we must provide the column name instead of star (*).
Try to fix it by providing the column name in SELECT statement of materialized view.
CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT StdRoll, StdName FROM tbl_StudentDetails;
Again another Error occurs:
Msg 4512, Level 16, State 3, Procedure view_StudentDetails, Line 33
Cannot schema bind view 'view_StudentDetails' because name 'tbl_StudentDetails' is
invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Now the above error says that we must provide the Schema name with Table Name in case of materialized view.
CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;
Now it’s perfect and complied successfully.
Summary:
Form the above example we find that the in case of materialized view we must provide the column name within the SELECT statement and also provide the schema name in the Table object.
SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;
GO
-- Display the Records of Views
SELECT IDNO, EMPNAME, EMPGRADE
FROM view_emp_data
-- Output from View
IDNO EMPNAME EMPGRADE
1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C
-- Protect DELETION From VIEWS
IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
BEGIN
DROP TRIGGER trg_RestrictDEL
END
GO
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16,2 )
ROLLBACK
END
END
GO
-- Now try to delete records from views
DELETE view_emp_data
WHERE IDNO=1
-- Output generated for DELETE Action
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 8
Rows from Views Named: view_emp_data, CANNOT be DELETED!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Why VIEW takes long time to Execute
Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.
Like stored procedure, the optimizers cache the execution plan in the case for further use.
Let's take an example:
We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)
CREATE TABLE emp_Table
(empid INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empname VARCHAR(50) NOT NULL,
empsal DECIMAL(20,2))
GO
CREATE VIEW vw_empView
AS
SELECT empid, empname, empsal
FROM emp_Table
GO
-- Example-1
SELECT * FROM emp_Table
GO
-- Example-2
SELECT * FROM vw_empView
GO
Here in this example which one is executed faster?
It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.
Both execution plans are same. So, why the execution of views take long time?
This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.
This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.
Can Views take the Input Parameters
One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views.
The answer is NO. It is not possible at any version of the SQL server as the view is not build for that purpose.
But we have others ways to do that, and we can do it very easily by table value functions. Here in this article I am trying to demonstrate it easily by taking an example.
-- My base table
CREATE TABLE my_Emplyee
(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMP_NAME VARCHAR(50) NULL,
SAL DECIMAL(20,2)NOT NULL)
-- Inserting so records on it
INSERT INTO my_Emplyee
(EMP_NAME, SAL)
VALUES('Sukamal jana', 40000),
('Manisankar Year', 30000),
('Tuhin Shina', 40000),
('Sabgram jit', 30000),
('Subrata Kar', 20000),
('Debojit Ganguli', 20000)
-- Display records
SELECT * FROM my_Emplyee
Output
ID EMP_NAME SAL
1 Sukamal jana 40000.00
2 Manisankar Year 30000.00
3 Tuhin Shina 40000.00
4 Sabgram jit 30000.00
5 Subrata Kar 20000.00
6 Debojit Ganguli 20000.00
I am again mentioned that the parameters cannot be passed in views.
Now I am moving to the solution of that by table value function and passing some parameters to get the desired result set.
IF OBJECT_ID (N'fn_EMP_VIEW') IS NOT NULL
DROP FUNCTION dbo.fn_EMP_VIEW
GO
CREATE FUNCTION dbo.fn_EMP_VIEW
(@p_Sal DECIMAL(20,2))
RETURNS TABLE
AS RETURN
(
SELECT *
FROM my_Emplyee
WHERE SAL>=@p_Sal
)
GO
We want to display the employee details that have salary 40,000 or more than that.
-- Execute it
SELECT *
FROM dbo.fn_EMP_VIEW(40000)
Output
ID EMP_NAME SAL
1 Sukamal jana 40000.00
3 Tuhin Shina 40000.00
Materialize view and the performance issue
In this article I am trying to illustrate some points related to materialize view and the performance issue.
Views are the good example to handle the complex query.
Thinks about the situations like this
If a view performs an aggregation of millions of rows, query performance of the view decreases dramatically and this operation is performed many times every day, the database system must access millions of rows repeatedly.
The solutions is the materialized view
A new data structure must be defined to deal with this kind of scenario. A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries.
How I implement it
SET STATISTICS TIME ON
It displays the number of milliseconds required to parse, compile, and execute each statement.
Now execute the Query mentioned bellow:
SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)
FROM Production.TransactionHistory t
INNER JOIN Production.Product p on t.ProductID=p.ProductID
GROUP BY p.ProductID;
It takes 42334 microseconds to execute.
To improve the response time, our strategy is to implement the materialize view.
To implements we must follows this steps.
1. Create a normal view
2. Include WITH SCHEMABINDING Options
3. Make a clustered index on this view
Step-1 and 2
CREATE VIEW view_totCostQtyByProd
WITH SCHEMABINDING
AS
SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)
FROM Production.TransactionHistory t
INNER JOIN Production.Product p ON t.ProductID=p.ProductID
GROUP BY p.ProductID;
Step-3
CREATE UNIQUE CLUSTERED INDEX Indx_TotCostQtyByProd
ON view_totCostQtyByProd(ProductID)
Now execute the view
SELECT *
FROM view_totCostQtyByProd;
It takes just 32 milliseconds, dramatically improve the performance.
Summary
So we understand that the creating materialized views dramatically improve the performance of the executions.
About VIEW
So what is a VIEW?
A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.
Once you have defined a view, you can reference it like any other table in a database.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
Views ensure the security of data by restricting access to the following data:
Specific rows of the tables.
Specific columns of the tables.
Specific rows and columns of the tables.
Rows fetched by using joins.
Statistical summary of data in a given tables.
Subsets of another view or a subset of views and tables.
Some common examples of views are:
A subset of rows or columns of a base table.
A union of two or more tables.
A join of two or more tables.
A statistical summary of base tables.
A subset of another view, or some combination of views and base table
Type of VIEWS
There are 3 type of view that we can create.
Standard View
Combining data from one or more tables through a standard view lets you satisfy most of the benefits of using views. These include focusing on specific data and simplifying data manipulation. Indexed Views
Indexed View
An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.
Partitioned Views
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.
SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued='No'
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL Updating a View
You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name
Special NOTE:
Remember the ORDER BY clause is not directily used in the view. For example if you want to create a view and used order by clause it gives you and error msg.
Msg 1033, Level 15, State 1, Procedure abc, Line 3
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
But you can still use ORDER BY with view by TOP 100 PERCENT like this
CREATE VIEW [ViewName]
AS
SELECT TOP 100 PERCENT itemcode, itemdescr
FROM item_master ORDER BY itemdescr
Indexed View
Views allow you to create a virtual table by defining a query against one or more tables. With a standard view, the result is not stored in the database. Instead, the result set is determined at the time a query utilizing that view is executed.
Creating a unique clustered index on a view changes it to an indexed view. The clustered index is stored in SQL Server and updated like any other clustered index, providing SQL Server with another place to look to potentially optimize a query utilizing the indexed view.
Queries that don't specifically use the indexed view can even benefit from the existence of the clustered index from the view. In the developer and enterprise editions of SQL Server, the optimizer can use the indexes of views to optimize queries that do not specify the indexed view. In the other editions of SQL Server, however, the query must include the indexed view and specify the hint NOEXPAND to get the benefit of the index on the view.
If your queries could benefit from having more than one index on the view, non-clustered indexes can also be created on the view. This would supply the optimizer with more possibilities to speed up the queries referencing the columns included in the view.
Where to Use Them
Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost.
Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.
How to Create Them
A view that is to be indexed has to be created with schema binding. This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. It also means that all the tables referenced in the view must be referenced by their two-part name (schemaname.tablename).
Below is an example of the CREATE statement for an indexed view, MyView, and its underlying table, MyBigTable. The table is first created, then the view that references two of the table's three columns, and finally the unique clustered index on the view making it an indexed view.
CREATE TABLE MyBigTable(
ItemID INT PRIMARY KEY,
ItemDsc VARCHAR(20),
QTY INT)
GO
CREATE VIEW MyView WITH SCHEMABINDING AS
SELECT ItemID, QTY
FROM dbo.MyBigTable
WHERE QTY > 10
GO
CREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(QTY)
Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available (assuming the execution cost is non-trivial). Consider the following query:
SELECT ItemID
FROM MyBigTable
WHERE QTY > 30
Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provided better performance than the clustered or non-clustered indexes on the base table.
If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint NOEXPAND. Conversely, if you'd like to see how a query containing an indexed view would perform utilizing the base tables instead, you can specify the option EXPAND VIEWS, thus saving you the time substituting the base tables yourself.
Constraints
An index cannot be created on just any view. Several constraints exist that a view must meet in order for the index creation to be successful. We discussed WITH SCHEMABINDING and two-part table names above. Here are some other constraints.
The view must have been created with certain SET options, such as QUOTED_IDENTIFIER and CONCAT_NULL_YIELDS_NULL set to ON.
The session creating the index must also have the correct SET options.
Any user-defined functions referenced by the view must have been created using WITH SCHEMABINDING.
The view must be deterministic (consistently providing the same result given the same input).
The base tables must have been created with the proper ANSI_NULLS setting.
The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider.
In addition to this, there are constraints on the contents of the view. For instance, the view may not contain EXISTS or NOT EXISTS, OUTER JOIN, COUNT(*), MIN, MAX, sub queries, table hints, TOP, UNION, and much more. Check the SQL Server Development Center on MSDN for a complete listing.
Conclusion
Indexed Views are a great way to realize performance gains under the right circumstances. Be aware of the costs of creating indexed views in highly transactional environments. If your environment happens to be one of more querying than updating, however, indexed views might be just what the optimizer ordered.
Let’s create a database
--CREATE A NEW DATABASE TEACHMESQLSERVER
USE master
GO
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')
DROP DATABASE TEACHMESQLSERVER
GO
CREATE DATABASE TEACHMESQLSERVER
GO
USE TEACHMESQLSERVER
GO
We have created our database; now will create one table in this database.
-- CREATE A NEW TABLE EMP IN TEACHMESQLSERVER DATABASE
CREATE TABLE EMP(EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX), SALARY MONEY)
GO
INSERT INTO EMP VALUES(1,'A',100),(2,'k',1005),(3,'B',3100),(4,'C',1100),(5,'D',1009),(6,'E',1700),(7,'F',1040),(8,'G',100),(9,'H',1000)
GO
SELECT * FROM SYS.objects WHERE name='EMP'
Now we will create a view on this EMP table.
Syntax to create view:
CREATE VIEW viewname
WITH <View Attributes> -- (This is Optional)
AS
SELECT STATEMENT -- (Your SQL Query, This is mandatory)
WITH CHECK OPTION -- (This is optional)
-- CREATE A VIEW ON THIS EMP TABLE
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP
You can find this view information from system catalog Views. Click here to know about System Catalog Views
What is stored in the database is a SELECT STATEMENT that you can find using the below query.
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172 BUT IT WILL BE DIFFERENT IN YOUR CASE.
A User can use this view by referencing the view name (EMPVIEW) in SQL statements in the same way a table is referenced.
GO
SELECT * FROM EMPVIEW
If you will run the SELECT STATEMENT (SELECT EMPID,EMPNAME FROM EMP) which was used to create EMPVIEW then you will get the same above result set which means the result set of the Select Statement forms the virtual table which is returned by the view.
The rows and columns are produced dynamically from the base tables (used in the select statement) when the view is referenced.
As you can notice in our EMP table we have three columns EMPID, EMPNAME, SALARY. But when you reference View it only gives you EMPID and EMPNAME. It is because we have not included the SALARY column at the time of CREATE VIEW. So you can say A View acts as a filter on the underlying tables referenced in the view. We didn’t want other users to know about the other EMP’s salary so we didn’t include the Salary column in Create View Statement.
A View can be created from one or more tables or views in the current or other database. There are no restrictions on querying through views but there are few restrictions on modifying data through View, Yes we can perform DML Operation on Views. When you perform any DML operation it affects the referenced tables used in view.
Modify View
You can modify View using ALTER COMMAND
ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
GO
Drop VIEW
If you want to delete view then you can delete it using DROP COMMAND
DROP VIEW EMPVIEW
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW'
We have gone through the basics of view, let’s go through WITH <VIEW ATTRIBUTES> and WITH CHECK OPTION
VIEW ATTRIBUTES
This is optional; we have two attributes ENCRYPTION and SCHEMABINDING. Let’s explore the use of these two attributes one by one.
1- ENCRYPTION
When you create any view without ENCRYPTION in that case you can see view definition in Syscomments system table, below example will explain it
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172 BUT IT WILL BE DIFFERENT IN YOUR CASE.
You can see view definition from previous query in text column.
If you want to hide that information from the system tables then you have to create view with ENCRYPTION, below example will explain it. Let’s alter the view with ENCRYPTION option.
ALTER VIEW EMPVIEW
With encryption
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=53575229 -- IN MY CASE IT IS 53575229 BUT IT WILL BE DIFFERENT IN YOUR CASE.
check the text column returned from the above query it is showing NULL
2- SCHEMABINDING
Suppose you have create a view which has columns column1, column2, column3, what if user delete column3 from the referenced table. In that case your view will not work it will give error because when we created the view at that time it saved the view definition with all three columns. Now one of the column has been dropped so when we will reference the view it will search for all three columns but it will not find the column3. How can we handle this kind of problem? Yes we can handle this problem what we have to do is we have to create dependencies between view columns and referenced table columns.
Using schemabinding option we create this dependence. So when we create a view with schemabinding option.
The base table or tables can’t be modified in a way that would affect the view definition.
If you want to modify the table then first either you have to drop the view or alter the view without schemabinding option.
When you use Schemabinding , the select statement must include the two part names (schema.object) of tables that are referenced.
We can’t use Select * when we create a view with schemabinding option, we have to specify each column name.
All referenced objects must be in same database.
Let’s go with example to understand the concept, we will alter our previously created view.
GO
DROP VIEW EMPVIEW --If view is already exists
GO
CREATE VIEW EMPVIEW --View Without Schemabinding
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
We have create a view without schemabinding, now let’s drop salary column from the referenced table.
GO
ALTER TABLE EMP DROP COLUMN SALARY
You successfully dropped the salary column, now let’s query against our EMPVIEW
Go
SELECT * FROM EMPVIEW
Your view didn't return any result set but it gave below error
Msg 207, Level 16, State 1, Procedure EMPVIEW, Line 3
Invalid column name 'SALARY'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'empview' because of binding errors.
User was able to drop Salary Column from the referenced table because our view was not created with schemabinding option. Now let’s modify the view with schemabinding option.
GO
ALTER VIEW EMPVIEW --View Schemabinding
WITH SCHEMABINDING
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
We have create a view schemabinding, now let’s drop EMPNAME column from the referenced table.
GO
ALTER TABLE EMP DROP COLUMN EMPNAME
You can’t drop the column because empname is dependent in view empview, you will get below error
Msg 5074, Level 16, State 1, Line 1
The object 'EMPVIEW' is dependent on column 'EMPNAME'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN EMPNAME failed because one or more objects access this column.
Last time we successfully dropped the salary column because we didn’t use schemabinding for view, but this time we used schemabinding option that is why we were not able to drop the column. If you want to drop the EMPNAME column
Then first you have to either drop the view or alter view with no schemabinding option.
GO
ALTER VIEW EMPVIEW --View Without Schemabinding
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
GO
ALTER TABLE EMP DROP COLUMN EMPNAME
You successfully dropped the EMPNAME column, now you have to modify your view otherwise it will give error i.e. Invalid column name EMPNAME
Refreshing Views
Suppose you are working with non schema bind view if base table has been modified which affected your view, In that case your view will not work it will give an error. You have to refresh your View definition to make it workable.
There are couple of ways to update View definition, one you can use alter command and update the view definition another is using Stored Procedure you can refresh Views definition.
i.e
Execute sp_refreshview 'Your View Name'
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
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'
Partitioned View in SQL Server
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),EMPIDINT )
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.
WITH CHECK OPTION in View
I have already discussed about Views in my previous post. In this article we will know the use of WITH CHECK OPTION on Views.
When we create a view without CHECK Option, in that case we can perform INSERT, UPDATE, DELETE operation on View. If records are not in scope of View and we want to perform DELETE and UPDATE operation on those records then DELETE and UPDATE operation will be succeed without any modification in the base table but INSERT operation will succeed and it will insert new records in base table.
When we create a view WITH CHECK OPTION, in that case we can’t perform INSERT, UPDATE, DELETE operation on those records which are not satisfying the criteria set within the select statement of View. If we try to perform DELETE and UPDATE operation on out of scope records then the operation will be succeed without any modification in the base table.
Let’s go through a simple example
USE TEACHMESQLSERVER
GO
-- Creating a new table and popluating data into the table
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
CREATE TABLE EMP( EMPID INT, EMPNAME VARCHAR(MAX))
GO
INSERT INTO EMP VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')
GO
-- Creating a View without CHECK Option
IF OBJECT_ID('EMPVIEW','V') IS NOT NULL
DROP VIEW EMPVIEW
GO
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
GO
SELECT * FROM EMPVIEW
--Performing DML Operation on VIEW
GO
INSERT INTO EMPVIEW VALUES(6,'F') -- Successfully Inserted new record in Base Table
GO
SELECT * FROM EMP
GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
SELECT * FROM EMP
WITH CHECK OPTION EXAMPLE
-- ALTERING the View WITH CHECK OPTION
GO
ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
WITH CHECK OPTION
--Performing DML Operation on VIEW
GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
INSERT INTO EMPVIEW VALUES(7,'H') -- Failed with below error, because the INSERT statement is not satisfying the Cretria set in Select Statement, VIEW can have all records where EMPID<5 in this case EMPID is greater than 5 so it failed.
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
GO
INSERT INTO EMPVIEW VALUES(0,'I') -- Successfully Inserted new record in Base Table because it satisfied EMPVIEW Criteria.
GO
SELECT * FROM EMP
Database Views| MSSQL Views| SQL Server Views| Syntax of Database Views| Types of Views
A view may be thought of as a virtual table, that is, a table that does not reallyexist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead adefinition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.SyntaxCREATE VIEW [<> . ] view_name [ ( column [ ,...n ] ) ] [ WITH <> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ]
<> ::= { ENCRYPTION SCHEMABINDING VIEW_METADATA }
Note: Column is for statements, which contain aggregate functions
Encryption: Code View is encrypted by using this.
Schemabinding: The view is binded with the underlying table structure. Suppose you drop a column of the base table, normally the view gives an error that there is no such column name But if you specify With Schemabinding, then we will not be able to drop the column as it is being accessed by other objects.
View_MetaData: Returns the meta data information about the view.
Check Option: Suppose we create a view with a query, which has where condition. For this view, if the user tries to insert the data that does not match the query then its of no use as that is not shown in the result. So that means, we should give access only to insert records which meets the query criteria. Hence WITH CHECK OPTION is used. The result would be an error for the situation above.A CREATE VIEW statement cannot:Include ORDER BY clause, unless there is also a TOP clause (remember that a view is nothing else but a virtual table, why would we want to order it?)
Include the INTO keyword.
Include COMPUTE or COMPUTE BY clauses.
Reference a temporary table or a table variable.
Select statement can use multiple SELECT statements separated by UNION or UNION ALL.Restrictions for updating data:
A view cannot modify more than one table. So if a view is based on two or more tables, and you try to run a DELETE statement, it will fail. If you run an UPDATE or INSERT statement, all columns referenced in the statement must belong to the same table.
It’s not possible to update, insert or delete data in a view with a DISTINCT clause.
You cannot update, insert or delete data in a view that is using GROUP BY.
It’s not possible to update, insert or delete data in a view that contains calculated columns.
Types:
Indexed Views: Indexed views work best for queries that aggregate or compute columns in the table. The disadvantage of indexed views is that it will slow down a query that updates data in the underlying tables.
Partitioned Views: When you need to write a join query with tables that are in different databases, then we use partitioned views.
View
Database Views| MSSQL Views| SQL Server Views| Syntax of Database Views| Types of Views
A view may be thought of as a virtual table, that is, a table that does not reallyexist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead adefinition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.SyntaxCREATE VIEW [<> . ] view_name [ ( column [ ,...n ] ) ] [ WITH <> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ]
<> ::= { ENCRYPTION SCHEMABINDING VIEW_METADATA }
Note: Column is for statements, which contain aggregate functions
Encryption: Code View is encrypted by using this.
Schemabinding: The view is binded with the underlying table structure. Suppose you drop a column of the base table, normally the view gives an error that there is no such column name But if you specify With Schemabinding, then we will not be able to drop the column as it is being accessed by other objects.
View_MetaData: Returns the meta data information about the view.
Check Option: Suppose we create a view with a query, which has where condition. For this view, if the user tries to insert the data that does not match the query then its of no use as that is not shown in the result. So that means, we should give access only to insert records which meets the query criteria. Hence WITH CHECK OPTION is used. The result would be an error for the situation above.A CREATE VIEW statement cannot:Include ORDER BY clause, unless there is also a TOP clause (remember that a view is nothing else but a virtual table, why would we want to order it?)
Include the INTO keyword.
Include COMPUTE or COMPUTE BY clauses.
Reference a temporary table or a table variable.
Select statement can use multiple SELECT statements separated by UNION or UNION ALL.Restrictions for updating data:
A view cannot modify more than one table. So if a view is based on two or more tables, and you try to run a DELETE statement, it will fail. If you run an UPDATE or INSERT statement, all columns referenced in the statement must belong to the same table.
It’s not possible to update, insert or delete data in a view with a DISTINCT clause.
You cannot update, insert or delete data in a view that is using GROUP BY.
It’s not possible to update, insert or delete data in a view that contains calculated columns.
Types:
Indexed Views: Indexed views work best for queries that aggregate or compute columns in the table. The disadvantage of indexed views is that it will slow down a query that updates data in the underlying tables.
Partitioned Views: When you need to write a join query with tables that are in different databases, then we use partitioned views.
View
OVERVIEW
A virtual entity of DB which looks like a table but does not store the records physically.
It is something like window over a table
Complex query can be converted into view and can be used in FROM clause
A table can be dropped even though view is existing which is using this table. However view created with SCHEMABINDING option won’t allow dropping a table
All DML operations are possible on View but again it has some limitations.
It shouldn't violate any constraint like foreign key, unique, identity, not null, etc.
View should point to only 1 table and not multiple tables
View should not have GROUP by clause or any arithmetic functions
Won’t allow SELECT INTO, ORDER BY
We can use ORDER BY but it should have TOP operator then only we can use it
Index can be created on view but with following conditions. If we do this, view will start storing the data physically
View should be schemabound
First we have to create unique clustered index then only we can create other non-clustered indexes
Creating index on view is sort of alternate solution of partition.
SYNTAX
Basic Syntax
CREATE VIEW vwName
AS
SELECT QUERY
With Schemabinding syntax
CREATE VIEW vw
WITH SCHEMABINDING
AS
SELECT eid, ename , salary, dob
FROM dbo.Employee
WHERE Salary > 10000
Common Error in case of Materialized View
Introduction
In this article we are trying to discuss about some common error related to materialize view. To understand the nature of the Error we are going to take an simple example. Hope it will be informative.
Example
Step-1 [ Creating the Base Table ]
IF OBJECT_ID(N'tbl_StudentDetails', N'U') IS NOT NULL
DROP TABLE tbl_StudentDetails;
CREATE TABLE tbl_StudentDetails
(
StdRoll INT NOT NULL IDENTITY PRIMARYKEY,
StdName VARCHAR(50) NOT NULL
);
GO
Step-2 [ Insert some records in the Base Table ]
INSERT INTO tbl_StudentDetails
(StdName)
VALUES ('Joydeep Das'), ('Santinath Mondal'), ('Avijit da');
GO
Step-3 [ Now Create a Simple View from the Base table ]
CREATE VIEW view_StudentDetails
AS
SELECT * FROM tbl_StudentDetails;
We find that the view is Created Successfully and there is no problem in case of simple view creation.
Step-4 [ Now Create a Materialized View with above Example ]
CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT * FROM tbl_StudentDetails;
The error occurred:
Msg 1054, Level 15, State 6, Procedure view_StudentDetails, Line 26
Syntax '*' is not allowed in schema-bound objects.
So we are not providing star (*) in Materialized view and we must provide the column name instead of star (*).
Try to fix it by providing the column name in SELECT statement of materialized view.
CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT StdRoll, StdName FROM tbl_StudentDetails;
Again another Error occurs:
Msg 4512, Level 16, State 3, Procedure view_StudentDetails, Line 33
Cannot schema bind view 'view_StudentDetails' because name 'tbl_StudentDetails' is
invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Now the above error says that we must provide the Schema name with Table Name in case of materialized view.
CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;
Now it’s perfect and complied successfully.
Summary:
Form the above example we find that the in case of materialized view we must provide the column name within the SELECT statement and also provide the schema name in the Table object.
SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;
DELETE restrictions of VIEWS
One of my friends told me that, he has a VIEW from a single base table and the user of the view is always trying to DELETE the records from VIEW. So the underlying TABLE records are also deleted for deletion actions of the view. He wants to restrict the deletions of records from VIEW.
The article is related to the DELETE restrictions of VIEWS. We can easily do it by REVOKING the DELETE permissions from the VIEW objects. But my friends have no permission over GRANT and REVOKE.
So the solution is given by using the INSTEAD OF DELETE Trigger. Here I am not going to describe the definition of it.
To know more about it, just go to my previous article.
FOR | AFTER | INSTEAD OF
http://sqlknowledgebank.blogspot.in/2012/03/for-after-instead-of.html
Rather I just provide a simple self explanatory example to demonstrate it
-- Base Table
IF OBJECT_ID('emp_data') IS NOT NULL
BEGIN
DROP TABLE emp_data
END
GO
CREATE TABLE emp_data
(
IDNO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
CONSTRAINT CHK_emp_data_EMPGRADE
CHECK (EMPGRADE IN('A','B','C'))
)
GO
-- Insert records
INSERT INTO emp_data
(EMPNAME, EMPGRADE)
VALUES ('Sudip Das', 'A'),
('Joydeep Das', 'B'),
('Sangram Jit', 'B'),
('Tuhin Shinah', 'B'),
('Bhola', 'C')
-- Display records
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data
-- Output
IDNO EMPNAME EMPGRADE
1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C
-- Creation of VIEWS
IF OBJECT_ID('view_emp_data') IS NOT NULL
BEGIN
DROP VIEW view_emp_data
END
GO
CREATE VIEW view_emp_data
AS
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data
One of my friends told me that, he has a VIEW from a single base table and the user of the view is always trying to DELETE the records from VIEW. So the underlying TABLE records are also deleted for deletion actions of the view. He wants to restrict the deletions of records from VIEW.
The article is related to the DELETE restrictions of VIEWS. We can easily do it by REVOKING the DELETE permissions from the VIEW objects. But my friends have no permission over GRANT and REVOKE.
So the solution is given by using the INSTEAD OF DELETE Trigger. Here I am not going to describe the definition of it.
To know more about it, just go to my previous article.
FOR | AFTER | INSTEAD OF
http://sqlknowledgebank.blogspot.in/2012/03/for-after-instead-of.html
Rather I just provide a simple self explanatory example to demonstrate it
-- Base Table
IF OBJECT_ID('emp_data') IS NOT NULL
BEGIN
DROP TABLE emp_data
END
GO
CREATE TABLE emp_data
(
IDNO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
CONSTRAINT CHK_emp_data_EMPGRADE
CHECK (EMPGRADE IN('A','B','C'))
)
GO
-- Insert records
INSERT INTO emp_data
(EMPNAME, EMPGRADE)
VALUES ('Sudip Das', 'A'),
('Joydeep Das', 'B'),
('Sangram Jit', 'B'),
('Tuhin Shinah', 'B'),
('Bhola', 'C')
-- Display records
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data
-- Output
IDNO EMPNAME EMPGRADE
1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C
-- Creation of VIEWS
IF OBJECT_ID('view_emp_data') IS NOT NULL
BEGIN
DROP VIEW view_emp_data
END
GO
CREATE VIEW view_emp_data
AS
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data
GO
-- Display the Records of Views
SELECT IDNO, EMPNAME, EMPGRADE
FROM view_emp_data
-- Output from View
IDNO EMPNAME EMPGRADE
1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C
-- Protect DELETION From VIEWS
IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
BEGIN
DROP TRIGGER trg_RestrictDEL
END
GO
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16,2 )
ROLLBACK
END
END
GO
-- Now try to delete records from views
DELETE view_emp_data
WHERE IDNO=1
-- Output generated for DELETE Action
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 8
Rows from Views Named: view_emp_data, CANNOT be DELETED!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Why VIEW takes long time to Execute
Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.
Like stored procedure, the optimizers cache the execution plan in the case for further use.
Let's take an example:
We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)
CREATE TABLE emp_Table
(empid INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empname VARCHAR(50) NOT NULL,
empsal DECIMAL(20,2))
GO
CREATE VIEW vw_empView
AS
SELECT empid, empname, empsal
FROM emp_Table
GO
-- Example-1
SELECT * FROM emp_Table
GO
-- Example-2
SELECT * FROM vw_empView
GO
Here in this example which one is executed faster?
It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.
Both execution plans are same. So, why the execution of views take long time?
This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.
This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.
Can Views take the Input Parameters
One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views.
The answer is NO. It is not possible at any version of the SQL server as the view is not build for that purpose.
But we have others ways to do that, and we can do it very easily by table value functions. Here in this article I am trying to demonstrate it easily by taking an example.
-- My base table
CREATE TABLE my_Emplyee
(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMP_NAME VARCHAR(50) NULL,
SAL DECIMAL(20,2)NOT NULL)
-- Inserting so records on it
INSERT INTO my_Emplyee
(EMP_NAME, SAL)
VALUES('Sukamal jana', 40000),
('Manisankar Year', 30000),
('Tuhin Shina', 40000),
('Sabgram jit', 30000),
('Subrata Kar', 20000),
('Debojit Ganguli', 20000)
-- Display records
SELECT * FROM my_Emplyee
Output
ID EMP_NAME SAL
1 Sukamal jana 40000.00
2 Manisankar Year 30000.00
3 Tuhin Shina 40000.00
4 Sabgram jit 30000.00
5 Subrata Kar 20000.00
6 Debojit Ganguli 20000.00
I am again mentioned that the parameters cannot be passed in views.
Now I am moving to the solution of that by table value function and passing some parameters to get the desired result set.
IF OBJECT_ID (N'fn_EMP_VIEW') IS NOT NULL
DROP FUNCTION dbo.fn_EMP_VIEW
GO
CREATE FUNCTION dbo.fn_EMP_VIEW
(@p_Sal DECIMAL(20,2))
RETURNS TABLE
AS RETURN
(
SELECT *
FROM my_Emplyee
WHERE SAL>=@p_Sal
)
GO
We want to display the employee details that have salary 40,000 or more than that.
-- Execute it
SELECT *
FROM dbo.fn_EMP_VIEW(40000)
Output
ID EMP_NAME SAL
1 Sukamal jana 40000.00
3 Tuhin Shina 40000.00
Materialize view and the performance issue
In this article I am trying to illustrate some points related to materialize view and the performance issue.
Views are the good example to handle the complex query.
Thinks about the situations like this
If a view performs an aggregation of millions of rows, query performance of the view decreases dramatically and this operation is performed many times every day, the database system must access millions of rows repeatedly.
The solutions is the materialized view
A new data structure must be defined to deal with this kind of scenario. A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries.
How I implement it
SET STATISTICS TIME ON
It displays the number of milliseconds required to parse, compile, and execute each statement.
Now execute the Query mentioned bellow:
SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)
FROM Production.TransactionHistory t
INNER JOIN Production.Product p on t.ProductID=p.ProductID
GROUP BY p.ProductID;
It takes 42334 microseconds to execute.
To improve the response time, our strategy is to implement the materialize view.
To implements we must follows this steps.
1. Create a normal view
2. Include WITH SCHEMABINDING Options
3. Make a clustered index on this view
Step-1 and 2
CREATE VIEW view_totCostQtyByProd
WITH SCHEMABINDING
AS
SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)
FROM Production.TransactionHistory t
INNER JOIN Production.Product p ON t.ProductID=p.ProductID
GROUP BY p.ProductID;
Step-3
CREATE UNIQUE CLUSTERED INDEX Indx_TotCostQtyByProd
ON view_totCostQtyByProd(ProductID)
Now execute the view
SELECT *
FROM view_totCostQtyByProd;
It takes just 32 milliseconds, dramatically improve the performance.
Summary
So we understand that the creating materialized views dramatically improve the performance of the executions.
About VIEW
So what is a VIEW?
A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.
Once you have defined a view, you can reference it like any other table in a database.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
Views ensure the security of data by restricting access to the following data:
Specific rows of the tables.
Specific columns of the tables.
Specific rows and columns of the tables.
Rows fetched by using joins.
Statistical summary of data in a given tables.
Subsets of another view or a subset of views and tables.
Some common examples of views are:
A subset of rows or columns of a base table.
A union of two or more tables.
A join of two or more tables.
A statistical summary of base tables.
A subset of another view, or some combination of views and base table
Type of VIEWS
There are 3 type of view that we can create.
Standard View
Combining data from one or more tables through a standard view lets you satisfy most of the benefits of using views. These include focusing on specific data and simplifying data manipulation. Indexed Views
Indexed View
An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.
Partitioned Views
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.
SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued='No'
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL Updating a View
You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name
Special NOTE:
Remember the ORDER BY clause is not directily used in the view. For example if you want to create a view and used order by clause it gives you and error msg.
Msg 1033, Level 15, State 1, Procedure abc, Line 3
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
But you can still use ORDER BY with view by TOP 100 PERCENT like this
CREATE VIEW [ViewName]
AS
SELECT TOP 100 PERCENT itemcode, itemdescr
FROM item_master ORDER BY itemdescr
Indexed View
Views allow you to create a virtual table by defining a query against one or more tables. With a standard view, the result is not stored in the database. Instead, the result set is determined at the time a query utilizing that view is executed.
Creating a unique clustered index on a view changes it to an indexed view. The clustered index is stored in SQL Server and updated like any other clustered index, providing SQL Server with another place to look to potentially optimize a query utilizing the indexed view.
Queries that don't specifically use the indexed view can even benefit from the existence of the clustered index from the view. In the developer and enterprise editions of SQL Server, the optimizer can use the indexes of views to optimize queries that do not specify the indexed view. In the other editions of SQL Server, however, the query must include the indexed view and specify the hint NOEXPAND to get the benefit of the index on the view.
If your queries could benefit from having more than one index on the view, non-clustered indexes can also be created on the view. This would supply the optimizer with more possibilities to speed up the queries referencing the columns included in the view.
Where to Use Them
Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost.
Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.
How to Create Them
A view that is to be indexed has to be created with schema binding. This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. It also means that all the tables referenced in the view must be referenced by their two-part name (schemaname.tablename).
Below is an example of the CREATE statement for an indexed view, MyView, and its underlying table, MyBigTable. The table is first created, then the view that references two of the table's three columns, and finally the unique clustered index on the view making it an indexed view.
CREATE TABLE MyBigTable(
ItemID INT PRIMARY KEY,
ItemDsc VARCHAR(20),
QTY INT)
GO
CREATE VIEW MyView WITH SCHEMABINDING AS
SELECT ItemID, QTY
FROM dbo.MyBigTable
WHERE QTY > 10
GO
CREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(QTY)
Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available (assuming the execution cost is non-trivial). Consider the following query:
SELECT ItemID
FROM MyBigTable
WHERE QTY > 30
Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provided better performance than the clustered or non-clustered indexes on the base table.
If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint NOEXPAND. Conversely, if you'd like to see how a query containing an indexed view would perform utilizing the base tables instead, you can specify the option EXPAND VIEWS, thus saving you the time substituting the base tables yourself.
Constraints
An index cannot be created on just any view. Several constraints exist that a view must meet in order for the index creation to be successful. We discussed WITH SCHEMABINDING and two-part table names above. Here are some other constraints.
The view must have been created with certain SET options, such as QUOTED_IDENTIFIER and CONCAT_NULL_YIELDS_NULL set to ON.
The session creating the index must also have the correct SET options.
Any user-defined functions referenced by the view must have been created using WITH SCHEMABINDING.
The view must be deterministic (consistently providing the same result given the same input).
The base tables must have been created with the proper ANSI_NULLS setting.
The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider.
In addition to this, there are constraints on the contents of the view. For instance, the view may not contain EXISTS or NOT EXISTS, OUTER JOIN, COUNT(*), MIN, MAX, sub queries, table hints, TOP, UNION, and much more. Check the SQL Server Development Center on MSDN for a complete listing.
Conclusion
Indexed Views are a great way to realize performance gains under the right circumstances. Be aware of the costs of creating indexed views in highly transactional environments. If your environment happens to be one of more querying than updating, however, indexed views might be just what the optimizer ordered.
