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.  

4 comments:

  1. Some of other advantages of views...

    1) Combines complex tables and can have required columns from them.
    2) Index can be created on views (indexed Views) for faster data retrieval.
    3) View can be encrypted..make sure to store the source to decrypt later.
    4) Views can be granted to the user for access instead of providing access of related tables used in it.
    5) Parametrized views can be created using Inline UDF.
    6) View can be materialized when indexed.

    --JP

    ReplyDelete
  2. Very nice.... :)

    ReplyDelete
  3. What is the different between the stored procedure and view and use.

    ReplyDelete
  4. WITH SCHEMABINDING can be used in Views and T-SQL Functions, but not in Stored Procedures.

    Objects that are schema bound can have their definition changed, but objects that are referenced by schema bound objects cannot have their definition changed.

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us