Overview of Views and Types of Views in SQL Server Database

Views in SQL Server Database

In the SQL Server database sometimes views are called virtual tables or stored procedure that are compiled at run time. We commonly keep those columns in views that we need to retrieve again and again. After creating a view in SQL Server database , we can use it as a table. We can also make index, trigger on view. A user can use this virtual table by passing a reference view in T-SQL statements as we reference a table in the database.

In the SQL Server database we create views for security purpose because it restricts the end users to view some columns or fields of the tables. Using SQL Server views we can show only those columns which are associated with the query to make view in the database.

Views in SQL Server is also used to do these functions:

1. Views does not allow a user to view restricted rows in a table.
2. Restricts the user to specific columns.
3. Join columns from multiple tables into a single table to look like a single table.
4. Shows only aggregate information besides supplying details.

Creating Views in SQL Server Database

The following statement can be used to create a view in SQL Server database that returns the names and ID of an employee.

CREATE VIEW vw_Names
AS
SELECT EMP_NAME, EMP_ID FROM EMP;
GO

Now users can use the SELECT statement to check for view.

SELECT * FROM vw_Names;
GO

Different Types of Views in SQL Server Database

In the SQL Server there are two types of views

1. System Defined View
2. User Defined View

System Defined View

As its name implies, system defined views are predefined views which exist in the master database. These views are used as template views for every newly created database. The system defined views are automatically attached to any user defined database.

There are several kind of system defined views in SQL Server which are given as follows:

1. Information Schema View:

SQL Server provides schema views to show the information of database such that tables, columns, etc.

2. Catalog View:

The catalog views were introduced with SQL Server 2005. The catalog view is used to show the self describing information.

3. Dynamic Management View:

Dynamic Management Views in SQL Server database is used to find out the administrator information of the database and current state of the SQL Server machine. With the help of these values administrator can analyze problems and may tune the server for the optimum performance.

There are two type of dynamic management views in SQL Server: Server-scoped Management View and Database-scoped Management View.

User Defined View

These types of view are defined by users. We have two types of user defined views.

1. Simple View:

Creating a view on a single table is called the simple view. In a simple view of a table, we can insert, update and delete the data. If we have a primary key and not null fields then we can insert the data in a simple view.

2. Complex View:

When we create a view on more than one table, it is called complex view. In the complex view we can only update the data, we can't insert data in it.

Uses of Views in SQL Server

The Views in SQL Server are mostly used to focusing, simplifying and customizing the realization for their database. These views can also be used for the security purpose by letting users access data through the view.

1. To Focus on Specific Data:

Views let the users to focus on specific data that are important for them and on the specific tasks for which they are responsible. Unwanted or sensitive data can be left out of the view.

2. To Simplifying the Data Manipulation:

The views can simplify the work of the user. A user can define the join, projection, UNION and SELECT queries as views.

3. Backward Compatibility:

Views allows you to create a backward compatible interface for a table when its schema changes. You can split the table into two tables to avoid the storing redundantly data in the database.

4. Customize Data:

Using views in SQL Server database a user can see the different users data in a different manner, even the user is using the same data at a time.

5. Export and Import Data:

Views can also be used to export data from one application and import to another application.

6. Combined the Data Across Servers:

Views can combine the results using the union operator of two or more queries from a separate table to a single result set. This appears to the user as a single table that is called a partitioned view.

Conclusion

In this article I have tried to explain the SQL Server database views. We have also discussed how to create views and types of views in SQL Server database and its uses.