Views

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 6 - Selecting Data through Views
--
-- Paul Nielsen
-----------------------------------------------------------


-- Creating the View

USE CHA2

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vEventList')
DROP VIEW dbo.vEventList

go
CREATE VIEW dbo.vEventList
AS
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
go

SELECT * FROM dbo.vEventList


-- Where
SELECT *
FROM dbo.vEventList
WHERE (Code = '01-016')


-- Order By

SELECT *
FROM vEventList
ORDER BY LastName, FirstName
go

-----------------------------------------------------------
-- Preventing Disappearing Rows

go
CREATE VIEW vCapeHatterasTour
AS
SELECT [Name], BaseCampID
FROM Tour
WHERE BaseCampID = 2

go
SELECT * FROM vCapeHatterasTour

INSERT vCapeHatterasTour ([Name], BaseCampID)
VALUES ('Blue Ridge Parkway Hike', 1)

SELECT * FROM vCapeHatterasTour

-- with check option

DELETE vCapeHatterasTour
WHERE [Name] = 'Blue Ridge Parkway Hike'
go
ALTER VIEW vCapeHatterasTour
AS
SELECT [Name], BaseCampID
FROM Tour
WHERE BaseCampID = 2
WITH CHECK OPTION
go

INSERT vCapeHatterasTour ([Name], BaseCampID)
VALUES ('Blue Ridge Parkway Hike', 1)
-----------------------------------------------------------
-- Protecting from Schema Changes

-- With Schema Binding

Use Tempdb
go
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vTest')
DROP View dbo.vTest
go
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'Test')
DROP TABLE dbo.Test

go
CREATE TABLE dbo.Test (
[Name] NVARCHAR(50)
)
go

CREATE VIEW dbo.vTest
WITH SCHEMABINDING
AS
SELECT [Name] FROM dbo.Test

go
ALTER TABLE dbo.Test
ALTER COLUMN [Name] NVARCHAR(100)

-- Correcting schema/view out-of-sync
USE CHA2
sp_refreshview 'vCapeHatterasTour'

-----------------------------------------------------------
-- Nesting Views
USE CHA2
go

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vEventList30days')
DROP VIEW dbo.vEventList30days
go

CREATE VIEW dbo.vBaseCampCount
AS
SELECT dbo.vEventList.Name as BaseCamp, Count(*) as [Attendees]
FROM dbo.vEventList
GROUP BY dbo.vEventList.Name





Read Users' Comments (0)

0 Response to "Views"

Post a Comment