Table of Contents
- What is View
- Use of Views
- General syntax for Views
- Creating a View
- Get result from a View
- Drop a View
- Different Types of Views
- System View
- Information View
- Catalog View
- Dynamic Management View
- User Defined View
- When to use a View
- View creation options
- Schema binding option
- Use SSMS for creating a View
- Topics to be covered in the next article
- References and future study
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_Table2will only show the information from those columns. Let's checkout the basic syntax for creating a View:
CREATE VIEW [View Name] AS [SELECT Statement]
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.
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] AS [SELECT Statement]
CREATE VIEW SampleView As SELECT EmpID, EmpName FROM EmpInfo
which will create a View with the name
SampleViewthat will only contain EmpID, EMPName.
This is similar to a
select * from SampleView
Now have a look at the output of
DROP VIEW SampleView
Now if we want to select data from
SampleView, we will get the following error:
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.
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:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='EmpInfo'
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:
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:
SELECT connection_id, session_id,client_net_address, auth_scheme 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 [^].
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 AS SELECT EmpID, EmpName, Phone FROM EmpInfFROM EmpInfo
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.
There are two different options for creating a View:
- Schema Binding Option
If we create a View with the
SCHEMABINDINGoption, 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 With SCHEMABINDING As SELECT EmpID, EmpName, FROM DBO.EmpInfo
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
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 With ENCRYPTION.EmpInfo
Note: Once the View is encrypted, there is no way to decrypt it again.
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.