MSSQL Server: Working with VIEWs in SQL Server

 Views in SQL Server and other RDBMSs are simply Virtual Tables or Stored Queries. Views are like tables, contains rows and columns, but do not store any data within. Instead they use the Query that is defined to create the view to show data from one or more tables.

 

–> A view can be used for:

1. Simplifying a complex query, give it a simple name, and use it like a table.

2. Providing security by restricting the table access and showing only selected Columns and Rows via Views.

3. Providing compatibility with other client systems that cannot be changed.
 

–> Now let’s see how Views helps with above points:

Point #1: Consider Query below, it will be difficult and time consuming to write the same query every time. Also due to human error at times you may not be able to get same or desired results, because you can commit mistake or forget something while writing the same logic.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

USE [AdventureWorks2014] GO   SELECT      P.BusinessEntityID AS EmpID     ,P.Title     ,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName     ,P.Suffix     ,E.BirthDate     ,CASE         WHEN E.Gender = 'M' THEN 'Male'         ELSE 'Female'      END as Gender     ,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus FROM Person.Person P JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = P.BusinessEntityID

… here we have Joined 2 tables and selected only required columns. You will also notice that we have changed the column names (alias), and created calculated columns, like EmployeeName, Gender & MaritalStatus.

So rather than writing this query every time, its better to store this query as a View, like below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE VIEW dbo.vwPersonEmployee AS SELECT      P.BusinessEntityID AS EmpID     ,P.Title     ,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName     ,P.Suffix     ,E.BirthDate     ,CASE         WHEN E.Gender = 'M' THEN 'Male'         ELSE 'Female'      END as Gender     ,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus FROM Person.Person P JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = P.BusinessEntityID GO

… and simply execute the view instead of the query now onwards, like:

1	SELECT * FROM dbo.vwPersonEmployee

 

Point #2: The above View uses 2 tables Person.Person & HumanResources.Employee. Now if you want a user to have restricted access to these 2 tables, but also want the user to query View to get desired and restricted data, then you can GRANT access only to the View, like:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE USER userView WITHOUT LOGIN;   GRANT SELECT ON dbo.vwPersonEmployee TO userView; GO   EXECUTE AS USER = 'userView';   SELECT * FROM dbo.vwPersonEmployee  -- Displays View data   SELECT * FROM Person.Person             -- ERROR: The SELECT permission was denied on the object SELECT * FROM HumanResources.Employee   -- ERROR: The SELECT permission was denied on the object   REVERT; GO   DROP USER userView GO

… here when the user executes the View he can see the data, but with only selected columns. But if he tries to use tables, he will get error.
 

Point #3: Now let’s say a client application which was getting data from an old table, let’s say dbo.Person, with following columns: PersonID, PersonFirstName, PersonLastName. Now on the new DB system the table is replaced by a new table Person.Person with different column names. This will make the system unusable and unstable.

But with the use of Views we can fill the gap, by creating a new View with name dbo.Person on top of Person.Person, and aliasing new columns with old column names, like:

1 2 3 4 5 6 7 8

CREATE VIEW dbo.Person AS SELECT      BusinessEntityID as PersonID     ,FirstName as PersonFirstName     ,LastName as PersonLastName FROM Person.Person GO

… so by this way the client application can talk to the new table by hitting the View instead of the Table.
 

–> Dropping/Deleting Views:

1 2	DROP VIEW dbo.vwPersonEmployee DROP VIEW dbo.Person