Views and Indexes

Use MagazineSubscription
Go

--simple view
Create view vw_Customer
AS
Select CustLastName as [Last Name]
,CustFirstName as [First Name]
,CustPhone as Phone
From Customer
Go

--stored
Select * from vw_Customer
Order by [Last Name]

Select [Last name], [First Name], Phone
from vw_Customer
Where [Last Name]='Terrance'

--in order to update a view
--on table can't have a join
--cannot have any calculated fields
--

Update vw_Customer
Set [Last Name]='Able'
Where [first Name]='Tina'

Drop view vw_customer
go

Alter View vw_Customer
As
Select CustLastName as [Last Name]
,CustFirstName as [First Name]
,CustAddress as [Address]
,CustCity as City
,CustState as [State]
,CustZipcode as [Zip Code]
,CustPhone as Phone
From Customer



Go
Select * from Subscription
Go
Create view manager.vw_SalesSummary
AS
Select MONTH(SubscriptionStart) as [Month]
,COUNT (SubscriptionID) as [Subscriptions]
,SUM(SubscriptionPrice) as Total
From Subscription s
Inner Join MagazineDetail md
On md.MagDetID=s.MagDetID
Group by MONTH(SubscriptionStart)

Go

Drop view vw_SalesSummary

Create Schema manager

Select * from vw_SalesSummary
Where [Month]=3

--Indexes
--three kinds
--clustered
--non clustered
--unique

Create index ix_LastName on Customer(CustLastName)
Create clustered index ix_LastNameclustered on Customer(CustLastName) --throws error
Create unique index ix_uniquePhone on Customer(CustPhone)

Drop index PK_Customer on Customer