Wednesday, May 4, 2011

Views and Indexes

Use MagazineSubscription

--views and indexes
Go
Create view vw_Subscriptions
AS
Select CustLastName [Last Name],
MagName [Magazine],
SubscriptionStart [Start],
SubscriptionEnd [End],
SubscriptTypeName [Subscription Type],
SubscriptionPrice [Price]
From Customer c
Inner Join Subscription s
on c.CustID=s.CustID
Inner Join MagazineDetail md
on md.MagDetID=s.MagDetID
Inner Join Magazine m
on m.MagID=md.MagID
Inner Join SubscriptionType st
on st.SubscriptTypeID=md.SubscriptTypeID
--

--you can update, insert or delete through a view
--if you have not aliased the fields
--if there are no joins
--if there are no calculated fields or functions

Select * from vw_Subscriptions
Order by [Last Name]

Select * from vw_Subscriptions where Magazine='IT Toys'

Create index ix_LastName on Customer(CustLastName)

Create clustered index ix_cLastName on Customer(CustLastName)

Create unique index ix_cLastName on Customer(CustLastName)

No comments:

Post a Comment