SQL with Manoj

 
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.

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:

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:

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:

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:

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:

DROP VIEW dbo.vwPersonEmployee
DROP VIEW dbo.Person