A view is a saved query. The following example creates a view by using a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.
A: create view
The data from this view comes from the HumanResources.Employee table, AdventureWorks2012 database.
This view is showing the number of employees hired by season and month.
Create view vwHiredbySeasonAndMonth
as
with Result as (
select
CHOOSE(MONTH(HireDate),
'Winter', 'Winter', 'Spring',
'Spring', 'Spring', 'Summer',
'Summer', 'Summer', 'Fall',
'Fall', 'Fall', 'Winter') Season,
COUNT(BusinessEntityID) 'Hired' ,
DATENAME(MONTH, hiredate) months
from HumanResources.Employee
Group by HireDate,
DATENAME(MONTH, hiredate)
)
select Season,
SUM(Hired) 'NumberOfHired',
months from Result
group by Season,months with rollup;
go
-------------------------------------
Select * from vwHiredbySeasonAndMonth
To get the metadata about the view you can use these codes:
exec sp_help vwHiredbySeasonAndMonth
exec sp_helptext vwHiredbySeasonAndMonth
Select * from INFORMATION_SCHEMA.VIEWS
Select * from sys.syscomments
B. Using WITH ENCRYPTION (You cannot query the view definition)
CREATE VIEW vwStatus
WITH ENCRYPTION
AS
SELECT JobTitle,
COUNT(JobTitle) 'Headcounts',
IIF(Gender='F',
IIF(MaritalStatus='S','Single Female', 'Married Female'),
IIF(MaritalStatus='S','Single Male', 'Married Male')) Status
FROM HumanResources.Employee
GROUP BY JobTitle,Gender,MaritalStatus
------------------------------------------------------------------------
SELECT * FROM vwStatus
The view definition is hidden.
C. Using WITH CHECK OPTION
The following example shows a view named vwSingleFemale that references
two tables and allows for data modifications to apply only to sigle females from AdventureWorks2012.
CREATE VIEW vwSingleFemale
AS
SELECT JobTitle,
FirstName+ ' '+LastName AS FullName,
[Status]
FROM
(SELECT e.JobTitle, p.FirstName,p.LastName,
COUNT(JobTitle) 'Headcounts',
IIF(Gender='F',
IIF(MaritalStatus='S','Single Female', 'Married Female'),
IIF(MaritalStatus='S','Single Male', 'Married Male')) Status
FROM HumanResources.Employee e
INNER JOIN Person.Person p
on e.BusinessEntityID = p.BusinessEntityID
GROUP BY JobTitle,Gender,MaritalStatus,p.FirstName,p.LastName
) result
WHERE Status = 'Single Female'
WITH CHECK OPTION
----------------------------------
SELECT * FROM vwSingleFemale
D: Using WITH SCHENABIDING
Do not allows any changes to the underlying table that could affect the definition of this view.
CREATE VIEW vwScrap
WITH SCHEMABINDING
AS
SELECT Country,
TotalScrap,
SUM(totalscrap) over (order by Country)As RunningTotal
FROM
(SELECT Country,
SUM(scrapped) as TotalScrap
FROM DBO.XYZ
GROUP BY Country)RESULT
