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]
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:
CREATE VIEW [View_Name]
CREATE VIEW SampleView
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 * from SampleView
Now have a look at the output of
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
Now we will take a look at the different types of Views in SQL Server 2005.
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
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:
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:
The following will be the output:
Similarly we can use other schema Views to read database information.
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.
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:
- Server-scoped DMV: Stored in the Master database.
- Database-scoped DMV: Specific to each database.
For example, if we want to check all SQL Server connections, we can use the following query:
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.
CREATE VIEW DemoView
SELECT EmpID, EmpName, Phone
FROM EmpInfFROM EmpInfo
When to Use a View
There are a number of scenarios where we will like to create our own View:
- To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
- To control access to rows and columns of data.
View Creation Option
There are two different options for creating a View:
- Schema Binding Option
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.
CREATE VIEW DemoSampleView
And one more thing that we need to remember, while specifying the database name, we have to use
After creating the View, try to alter the table EmpInfo, we won't be able to do it! This is the power of the
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:
CREATE VIEW DemoView
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.