Introduction
Getting data from multiple database tables and working with this data is a common need for business applications. Views are one of the ways to do so in a database. We can create a view and get data from multiple tables. Views create a virtual table in the database and a query can be used to work with a view. In this article, we will learn about views in SQL Server. Here we will cover topics such as What is a view in SQL, types of views, create a view in SQL, and alter views with SQL code examples in SQL Server.
Views in SQL Server
Views are virtual tables that hold data from one or more tables. It is stored in the database. A view does not contain any data itself, it is a set of queries that are applied to one or more tables that are stored within the database as an object. Views are used for security purposes in databases. Views restrict the user from viewing certain columns and rows.
In other words, using a view we can apply the restriction on accessing specific rows and columns for a specific user. A view can be created using the tables of the same database or different databases. It is used to implement the security mechanism in the SQL Server.
In the preceding diagram, we have created a view that contains the columns of two tables, Table A and Table B, using a query. A view is created using a select statement. Views are stored in the database as an object so it doesn't require additional storage space. Before starting any discussion about views we should have a basic knowledge of them.
Uses of views in SQL
Views are used to implement the security mechanism in SQL Server. Views are generally used to restrict the user from viewing certain columns and rows. Views display only the data specified in the query, so it shows only the data that is returned by the query defined during the creation of the view. The rest of the data is totally abstract from the end user.
Types of views in SQL Server
There are the following two types of views.
- User-Defined Views
- System-Defined Views
First, we discuss the User-Defined Views.
User Define Views
First, we create two tables. First, create an Employee_Details table for the basic info of an employee.
Now insert some data into the table as in the following.
Now the table Employee_Detail will look as in the following.
We create another table named Employee_Contact.
Insert some values into the Employee_Contact table as in the following.
Now the table Employee_Contact will look as in the following.
Now we start a detailed discussion of User Defined Views (UDVs).
Create SQL VIEW in SQL Server
- CREATE VIEW view_name AS
- SELECT columns
- FROM tables
- WHERE conditions;
Let us create some views.
Method 1, We can select all columns of a table. The following example demonstrates that.
Method 2, We can select specific columns of a table. The following example demonstrates that.
Method 3, We can select columns from a table with specific conditions. The following example demonstrates that.
Method 4, We can create a view that will hold the columns of different tables. The following example demonstrates that.
Retrieve Data From View in SQL Server
This SQL CREATE VIEW example would create a virtual table based on the result set of the select statement. Now we can retrieve data from a view as follows.
The preceding query shows that we can select all the columns or some specific columns from a view.
Dropping a View in SQL Server
We can use the Drop command to drop a view. For example, to drop the view Employee_View3, we can use the following statement.
Renaming the View in SQL Server
We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below.
Example
In the preceding example, we rename the view Employee_View1 as Employee_ViewNew.
Getting Information about a view
We can retrieve all the information of a view using the Sp_Helptext system Stored Procedure. Let us see an example.
Output
Alter View in SQL Server
We can alter the schema or structure of a view. In other words, we can add or remove some columns or change some conditions that are applied in a predefined view. Let us see an example.
Refreshing a View in SQL Server
Let us consider the scenario now by adding a new column to the table Employee_Details and examine the effect. We will first create a view.
Now add a column in the Employee_Details table
Now retrieve the data from the table and view and you will receive the following output.
Output
We don't get the results we expected because the schema of the view is already defined. So when we add a new column to the table it will not change the schema of the view and the view will contain the previous schema. For removing this problem we use the system-defined Stored Procedure sp_refreshview.
sp_refreshview is a system-level Stored Procedure that refreshes the metadata of any view once you edit the schema of the table. Let's execute the following.
Output
SchemaBinding a VIEW
In the previous example, we saw that if we add a new column to the table then we must refresh the view.
In such a way if we change the data type of any column in a table then we should refresh the view. If we want to prevent any type of change in a base table then we can use the concept of SCHEMABINDING. 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).
We can't specify "Select * from tablename" with the query. We need to specify all the column names for reference.
In the preceding example, we create a view using Schemabinding. Now we try to change the datatype of Emp_Salary from int to Decimal in the Base Table.
We find that we cannot change the data type because we used the SCHEMABINDING that prevents any type of change in the base table.
Encrypt a view in SQL Server
The “WITH ENCRYPTION” option can encrypt any views. That means it will not be visible via SP_HELPTEXT. 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.
Now we try to retrieve the definition of the view.
Output
Check Option
The use of the Check Option in a view is to ensure that all the Update and Insert commands must satisfy the condition in the view definition.
Let us see an Example
In the preceding example, we create a view that contains all the data for which Emp_Salry > 30000 but we can insert the data for a salary less than 30000 as follows.
For preventing this problem we can use the Check Option property such as.
Now if we try to execute the preceding query then it will throw an error such as.
Output
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
DML Query In View
In a view, we can implement many types of DML queries like insert, update and delete. But for a successful implementation of a DML query we should use some conditions like:
- View should not contain multiple tables
- View should not contain a set function.
- View should not use the Distinct keyword
- View should not contain Group By, having clauses
- View should not contain Sub query
- View should not use Set Operators
- All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.
If we use the preceding conditions then we can implement a DML Query in the view without any problem. Let us see an example.
Output
Now we implement a DML Query as in the following.
Output
System Define Views
SQL Server also contains various predefined databases like Tempdb, Master, and temp. Each database has its own properties and responsibility. Master data is a template database for all other user-defined databases.
A Master database contains many Predefine_View that work as templates for other databases and tables. Master databases contain nearly 230 predefined views.
These predefined views are very useful to us. Mainly we divide system views into the following two parts.
- Information Schema
- Catalog View
Information schema
There are nearly 21 Information Schemas in the System. These are used for displaying the most physical information of a database, such as table and columns. An Information Schema starts from INFORMATION_SCHEMA.[View Name].
Let us see an example.
Output
This Information_Schema returns the details of all the views used by table Employee_Details.
Output
This Information_Schema returns the information about the constraints of a table.
Catalog View
Catalog Views have been categorized into various groups also. These are used to show the self-describing information of a database. These start with “sys”.
This query provides information to all types of views using a database.
This query will provide information about all the databases defined by the system, including user-defined and system-defined databases.
Conclusion
In this article, we learned about views in SQL Server with its different types with code examples in SQL Server.