• Marek Vavrovic

CREATE VIEW in SQL Server using T-SQL.

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





12 views0 comments

Recent Posts

See All