Hello, Guys In this tutorial we are going to learn what are Views in SQL Server. Views are a virtual table in SQL Server that does not contain its own data.
After Completing this tutorial you will be able to understand in SQL Server.
- What are Views in SQL Server?
- Why should we use View?
- How to Create, Modify and Drop a View in SQL Server.
What are Views in SQL Server?
Views are database object that does not contain the data of itself It contains data from one or more tables. Views are a temporary object that is stored in the database. It is a virtual table that contains the select statements only. The purpose of using Views is to restrict the user from viewing certain columns from the tables. It provides the security mechanism because it hides the table name and certain fields from the user by providing the virtual table.
Why Should we use Views?
The purpose of using views in SQL Server is to provide security to the schema of the table in data because it encapsulates our table name and the fields that we don’t want to show to the user. The data inside the views are not stored permanently in the database. One more reason behind using a View in SQL Server is that if we have some complex type of join query from more than one table then instead of writing join query, again and again, we can create a View and use it anywhere.
The syntax of a View.
- SchemaName specifies the name of Schema that owns the View.
- ViewName specifies the name of View.
- ENCRYPTION It will encrypt text of the ALTER VIEW statement in sys.syscomments.
- SCHEMABINDING It ensures that the underlying table definitions can not be modified so as to affect the VIEW.
- VIEW_METADATA It will ensure that SQL Server has metadata about the VIEW.
- The Expression specifies the ColumnNames or Calculative values.
- Tables species the name of Table in the database.
- Where Condition This is Optional that specifies the condition on the basis of which we want to get records.
Example using View:
Let’s take a look at how to create a view in SQL Server. Before creating a view we need to create a table and insert few records in that table
Create a table called Employee:
Insert few records in Employee Table.
Suppose I want to create a view to get records for those Employees who has salary less than 40000.
Run the Above Script.
Test the View.
To test the vwGetEmployee View run the following query.
The result will be:
Alter a View.
You can alter a view using the Alter View Statement.
Syntax to Alter a View.
The example of Alter View:
Suppose I want don’t want to get EmployeeID from the View. Our view would like this.
Run the following query to test the result
You can see in the above snapshot there is no EmployeeID in the result set.
How to Drop a View?
You can simply drop a View using the Drop Statement.
Where ViewName is the name of the View.
The example of Drop View:
Hope you understand the concept of Views in SQL Server. I would appreciate your feedback, comments and suggestions.