Friday, 17 January 2014

SQL Server Interview Questions on Views - Part 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.

What are the advantages of using views?
Or 
When do you usually use 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




Listing 1
Create View      vWEmployee
As
Select               ID, Name, Dept
From                 tblEmployee

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.



Listing 3
Select        Dept, Count(*) As Total
From          tblEmployee
Group By   Dept

If you can think of any other advantages of using views please post them as comments, so they will be useful to other users like you. 

No comments:

Post a Comment

Paging in html table using javascript

I demonstrated how to use HTML tables on the client for a very simple client-side paging solution. I have heard from several people who p...