Google+ Followers

Wednesday, July 30, 2014

Views in SQL Server

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.

We will go through the examples and will understand the concept.

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

Congratulations you have successfully create a simple view, 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

Congratulations you have successfully modified the view.

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'

Congratulations you have successfully deleted the view.

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.

Congratulation! You have successfully hid the view definition, 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'

I hope you enjoyed the tutorial; 

There are couple of topics left don't forget to visit below posts which are related to Views

Partitioned View in SQL Server

1 comment:

Anonymous said...

Good Article ..