This post is first of yet to come long series on basic concepts of SQL Server. I’ll begin with basic 101 introduction to SQL Server views. I wouldn’t recommend reading any further if you are an experienced sql server’er. I value your time

What are views?

• virtual tables
• named select statements

Where are views used?

• to simplify underlying data model for users
• to implement security mechanism
• anywhere a table is expected

How to create views?

Create View t-sql command is used to create a view. Following is a sample from AdventureWorks database. Views are defined by the underlying query (select statement that creates it). Views contain rows and columns that were returned by the underlying query.

CREATE VIEW dbo.[vEmployee]
AS

SELECT
c.[FirstName]
, c.[LastName]
, [StateProvinceName] = sp.[Name]
, [CountryRegionName] = cr.[Name]
FROM
[HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

A select query can be used to retrieve data from the view.

SELECT * FROM dbo.vEmployee

If you are following along and executing the scripts so far you will notice that selecting from a view will return a result set (rows and columns).

How are views stored?

Unlike tables, contents of a view aren’t physically stored. Only the underlying query is given a name and saved on the server. Okay Mr.Genius, if the contents of a view are not saved how did selecting a view return data? I am glad you asked.

SQL Server internally replaces view with the underlying query and reads                                                                                                        select * from <view name> as select * from <underlying query>.

Uses of Views:

Most of the OLTP databases are highly normalized; which means data is spread across multiple tables and naturally joins will have to be used to retrieve this data making it difficult for report writers and other users. Views like dbo.vEmployee can be used to hide all the complexity from users. They form a simplified layer between database and users.

Views can also be used to implement security mechanism. Imagine a student table with ID, Name, SSN, and DOB. Not all the users should have access to SSN of students, a view can be created with only ID, Name, and DOB and users be granted access to this view limiting them from viewing SSN.

~Sam