Views in SQL

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 theView_Table1_Table2 will only show the information from those columns. Let's checkout the basic syntax for creating a View:

CREATE VIEW  [View Name]
AS
[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:

CREATE VIEW [View_Name]
 AS
 [SELECT Statement]

For example:

CREATE VIEW SampleView
As
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

a. Information Schema View

b. Catalog View

c. Dynamic Management View (DMV)

User Defined Views

a. Simple View

b. 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:

a. Information Schema View

b. Catalog View

c. 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:

Hide   Copy Code

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.

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:

Hide   Copy Code

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:

SELECT

connection_id,session_id,client_net_address,auth_scheme
FROM sys.dm_exec_connections

And the following is the sample output:

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
AS
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:

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:

a. Schema Binding Option

b. 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

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 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:

Encryption

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.

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.