1) What is a View in SQL Server?
You can think of a view either as a compiled sql query or a virtual table. As a view represents a virtual table, it does not physically store any data. When you query a view, you actually retrieve the data from the underlying base tables.2) What are the advantages of using views?
1. Views can be used to implement row level and column level security.Example 1: Consider the tblEmployee table below. I don't want some of the users to have access to the salary column, but they should still be able to access ID, NAME and DEPT columns. If I grant access to the table, the users will be able to see all the columns. So, to achieve this, I can create a view as shown in Listing 1 below. Now, grant access to the view and not the table. So using views we can provide column level security.
Example 2: Let us say, we have a few users who should be able to access only IT employee details and not any other dept. To do this, I can create a view as shown in Listing 2 below. Now, grant access only to the view and not the table. So using views we can provide row level security as well.Listing 2
Create View vWITEmployees
As
Select ID, Name, Dept
From tblEmployee
Where Dept = 'IT'2. Simplify the database schema to the users. You can create a view based on multiple tables which join columns from all these multiple tables so that they look like a single table.
3. Views can be used to present aggregated and summarized data.
Example 1: Consider the tblEmployee table above. I want to aggregate the data as shown in the image below. To do this I can create a view as shown in Listing 3. Now, you can simply issue a select query against the view rather than writing a complex query every time you want to retrieve the aggregated data.
You can think of a view either as a compiled sql query or a virtual table. As a view represents a virtual table, it does not physically store any data. When you query a view, you actually retrieve the data from the underlying base tables.2) What are the advantages of using views?
1. Views can be used to implement row level and column level security.Example 1: Consider the tblEmployee table below. I don't want some of the users to have access to the salary column, but they should still be able to access ID, NAME and DEPT columns. If I grant access to the table, the users will be able to see all the columns. So, to achieve this, I can create a view as shown in Listing 1 below. Now, grant access to the view and not the table. So using views we can provide column level security.
tblEmployee Create View vWEmployee As Select ID, Name, Dept From tblEmployee |
Create View vWITEmployees
As
Select ID, Name, Dept
From tblEmployee
Where Dept = 'IT'2. Simplify the database schema to the users. You can create a view based on multiple tables which join columns from all these multiple tables so that they look like a single table.
3. Views can be used to present aggregated and summarized data.
Example 1: Consider the tblEmployee table above. I want to aggregate the data as shown in the image below. To do this I can create a view as shown in Listing 3. Now, you can simply issue a select query against the view rather than writing a complex query every time you want to retrieve the aggregated data.
 Listing 3Select Dept, Count(*) As Total From tblEmployee Group By Dept |
