Click here to Skip to main content
15,388,688 members
Articles / Database Development / SQL Server
Posted 28 Jul 2009


58 bookmarked

Overview of Views in SQL Server 2005

Rate me:
Please Sign up or sign in to vote.
4.86/5 (53 votes)
28 Jul 2009CPOL9 min read
Overview of Views, system Views, creation of Views, and binding and securing Views.

Table of Contents


In this article, I am going to describe about Views in SQL Server 2005. This is a simple topic. I hope this article will help you just like my ASP.NET articles. Please give me your valuable suggestions and feedback to improve my articles.

What is a View

A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.


In the above diagram, we have created a View (View_Table1_Table2) from Table1 and Table2. So the View_Table1_Table2 will only show the information from those columns. Let's checkout the basic syntax for creating a View:

CREATE VIEW  [View Name]
[SELECT Statement]

Use of a View

Views are used as security mechanisms in databases. Because it restricts the user from viewing certain column and rows. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. The rest of the data is totally abstract from the end user.

Along with security, another advantage of Views is data abstraction because the end user is not aware of all the data in a table.

General syntax for Views

In this section, I will describe how to create Views, select data from Views, and deleting Views. I have created a database named ViewDemo. It has a table called EmpInfo as shown below:


which contains the following data:


All the examples I have described are from this database.

Creating a View

Below is the general syntax for creating a View:

 [SELECT Statement]

For example:

SELECT EmpID, EmpName  FROM EmpInfo

which will create a View with the name SampleView that will only contain EmpID, EMPName.

Get result from a View

This is similar to a Select statement:

select * from SampleView

Now have a look at the output of SampleView:


Drop a View

DROP VIEW SampleView

Now if we want to select data from SampleView, we will get the following error:


Different types of Views

There are two different types of Views:

  • System Views
    • Information Schema View
    • Catalog View
    • Dynamic Management View (DMV)
  • User Defined Views
    • Simple View
    • Complex View

Now we will take a look at the different types of Views in SQL Server 2005.

System Views

In SQL Server, there are a few system databases like Master, Temp, msdb, and tempdb. Each and every database has its own responsibility, like Master data is one of the template databases for all the databases which are created in SQL Server 2005. Similarly, System Views are predefined Microsoft created Views that already exist in the Master database. These are also used as template Views for all new databases. These system Views will be automatically inserted into any user created database. There are around 230 system Views available.

We can explore system Views from the SQL Server Management Studio. Expand any database > View > System View.


In SQL Server, all system Views are divided into different schemas. These are used for the security container of the SQL Server database. We can categorize system Views in the following way:

  • Information Schema View
  • Catalog View
  • Dynamic Management View (DMV)

Now all the above categories are themselves huge topics, so I will not go into the details of them. Let us go through an overview of those View types:

Information View

These are one of the most important system grouped Views. There are twenty different schema Views in this group. These are used for displaying most physical information of a database, such as table and columns. The naming convention of this type of Views is INFORMATION_SCHEMA.[View Name]. From the system View image, we can get the names of a few Information Schema Views.

Let's see this with an example.

I have create a database named ViewDemo. It has a table called EmpInfo and the below diagram shows you the design of the table:


Now if we want to know detailed information on the columns of the table Empinfo using the View, we have to run the following query:

Where TABLE_NAME='EmpInfo'

The following will be the output:


Similarly we can use other schema Views to read database information.

Catalog View

This type of Views were introduced in SQL Server 2005. Catalog Views are categorized in to different groups also. These are used to show database self describing information.

For example:

select * from sys.tables

and following is a sample output:


Dynamic Management View

This is newly introduced in SQL Server 2005. These Views give the database administrator information about the current state of the SQL Server machine. These values help the administrator to diagnose problems and tune the server for optimal performance. In SQL Server 2005, there are two types of DMVs:

  1. Server-scoped DMV: Stored in the Master database.
  2. Database-scoped DMV: Specific to each database.

For example, if we want to check all SQL Server connections, we can use the following query:

FROM sys.dm_exec_connections

And the following is the sample output:


If you want to know the details on DMV, here is a complete article on CodeProject: Dynamic Management Views [DMV] - A SQL Server 2005 Feature [^].

Note: There are many things to learn on system Views, I have just introduced them for beginners. If anyone has more interest, look into this article: System Views in SQL Server 2005 [^].

User Defined View

Up till now I described about system Views, now we will take a look at user defined Views. These Views are created by a user as per requirements. There is no classification for UDVs and how to create them, I have already explained the syntax. Now we can take a look at another View creation.

SELECT EmpID, EmpName, Phone

When to Use a View

There are a number of scenarios where we will like to create our own View:

  1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  2. To control access to rows and columns of data.

View Creation Option

There are two different options for creating a View:

  • Schema Binding Option
  • Encryption

Schema BindSchema Binding Option

If we create a View with the SCHEMABINDING option, it will lock the tables being referred to by the View and restrict all kinds of changes that may change the table schema (no Alter command). While creating a schema binding View, we can't mention "Select * from tablename" with the query. We have to mention all column names for reference.

For example:

CREATE VIEW DemoSampleView

And one more thing that we need to remember, while specifying the database name, we have to use Dbo.[DbName]. After creating the View, try to alter the table EmpInfo, we won't be able to do it! This is the power of the SCHEMABINDING option.

If we want to change/alter the definition of a table which is referred by a schema bound View, we will get the following error message:



This option encrypts the definition. This option encrypts the definition of the View. Users will not be able to see the definition of the View after it is created. This is the main advantage of the View where we can make it secure:


Note: Once the View is encrypted, there is no way to decrypt it again.

Use SSMS for Creating a View

SQL Server Management Studio provides a handy GUI for creating and managing Views. In the Object Explorer tab, it lists all the Views corresponding to a database. In this section, we will just quickly check how SSMS is used to create and maintain a View.

First expand ViewDemoDB > Move to View. Right click on the View folder.


When we will click on New View, the following screen will appear. In ViewDemoDB, we have two datatables. Now I am going to create a View from the EmpInfo table.


Select EmpInfo, click on Add. You will be redirected to the Create View screen where you can configure the View creation. Check the following image:


The above image shows three sections where we can select the table name or in the below section, we can write the query for the View. When done, just click on the Save button on the toolbar. Give the name of the View and click on OK.


Now go to ViewDemoDB > View > Expand View folder. Here, along with system Views, you can see the View that we created right now.


So this is our user defined View. If we right click on it, we will get the option of opening the View and which will show the result of the View.


We can also create a View from a View itself in a similar way that we have done with a table.


View is a "Virtual Table" which is stored as an object in a database. This can be used as a security container of the database. We can encrypt a View definition for making it secure. There are more than 230 system Views available which have their own responsibilities. We can create a View by either writing a T-SQL statement or by using SQL Server Management Studio.

Hope I have explained it well. Please give your feedback and suggestions to improve my article.

Topic to be covered on next article

  • Parameterized View
  • Indexing a View
  • View FAQ's

Reference and future study

Points of interest

I have published only a few articles on SQL Server, and this was written after a long time. I have learned a lot while studying and exploring Views.


  • 27 July 2009: Initial post.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Abhijit Jana
Technical Lead
India India
.NET Consultant | Former Microsoft MVP - ASP.NET | CodeProject MVP, Mentor, Insiders| Technology Evangelist | Author | Speaker | Geek | Blogger | Husband

Blog : http://abhijitjana.net
Web Site : http://dailydotnettips.com
Twitter : @AbhijitJana
My Kinect Book : Kinect for Windows SDK Programming Guide

Comments and Discussions

QuestionNice tutorial Pin
Praveen_P3-Nov-14 20:11
MemberPraveen_P3-Nov-14 20:11 
Questionclear all my doubt about the view. Pin
joginder-banger29-Aug-14 1:15
professionaljoginder-banger29-Aug-14 1:15 
QuestionNice article Pin
Rakshith Kumar11-Nov-13 22:50
MemberRakshith Kumar11-Nov-13 22:50 
GeneralMy vote of 5 Pin
amitavin_Sharma27-Feb-13 1:37
Memberamitavin_Sharma27-Feb-13 1:37 
GeneralMy vote of 5 Pin
gdwn3-Jan-13 19:15
Membergdwn3-Jan-13 19:15 
GeneralMy vote of 4 Pin
aankitpal28-Nov-12 22:58
Memberaankitpal28-Nov-12 22:58 
QuestionWhere Views are used in sql server -- Pin
Velkumar Kannan22-Oct-12 19:20
MemberVelkumar Kannan22-Oct-12 19:20 
GeneralMy vote of 5 Pin
Arpan Guin22-Oct-12 3:04
MemberArpan Guin22-Oct-12 3:04 
GeneralMy vote of 3 Pin
Maddy selva9-Oct-12 22:55
professionalMaddy selva9-Oct-12 22:55 
Questionview in sql server Pin
jgrumy14-Sep-12 2:14
Memberjgrumy14-Sep-12 2:14 
GeneralMy vote of 5 Pin
pradiprenushe1-Aug-12 23:44
professionalpradiprenushe1-Aug-12 23:44 
GeneralMy vote of 5 Pin
saikumar172327-Jul-12 10:47
Membersaikumar172327-Jul-12 10:47 
GeneralMy vote of 5 Pin
Akiii_Lethal1-Jun-12 19:47
MemberAkiii_Lethal1-Jun-12 19:47 
GeneralMy vote of 4 Pin
Member 79955425-Mar-12 1:05
MemberMember 79955425-Mar-12 1:05 
GeneralMy vote of 5 Pin
aytharavi24-Nov-11 0:22
Memberaytharavi24-Nov-11 0:22 
GeneralMy vote of 5 Pin
ChiragBMakwana28-Mar-11 21:32
MemberChiragBMakwana28-Mar-11 21:32 
GeneralMy vote of 5 Pin
Sandesh M Patil3-Mar-11 3:32
MemberSandesh M Patil3-Mar-11 3:32 
GeneralMy vote of 5 Pin
VishnuSharmila5-Jan-11 0:52
MemberVishnuSharmila5-Jan-11 0:52 
GeneralMy vote of 5 Pin
rinatyagi19-Sep-10 18:25
Memberrinatyagi19-Sep-10 18:25 
GeneralMy vote of 5 Pin
arun potti2-Sep-10 23:50
Memberarun potti2-Sep-10 23:50 
GeneralMy vote of 5 Pin
dl4gbe24-Jul-10 22:47
Memberdl4gbe24-Jul-10 22:47 
GeneralNice Artice Pin
9TailFox20-Mar-10 21:40
Member9TailFox20-Mar-10 21:40 
GeneralGood Article Pin
mukeshbela27-Nov-09 20:09
Membermukeshbela27-Nov-09 20:09 
Generalnice idea Pin
Md. Marufuzzaman29-Jul-09 9:11
professionalMd. Marufuzzaman29-Jul-09 9:11 
GeneralRe: nice idea Pin
Abhijit Jana29-Jul-09 9:30
professionalAbhijit Jana29-Jul-09 9:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.