Monday, February 13, 2012

Indexes and Views

--views

Use CommunityAssist 

Go
If exists
 (Select name from sys.views
  where name='vw_Donors')
Begin
Drop View vw_Donors
end 
Go
--create a view
Create view vw_Donors
As
Select Distinct lastname [Last Name],
Firstname [First Name],
ContactInfo [Email]
From Person p
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
Where ContactTypeKey=6
Go

--use the view just like a table
Select * from vw_Donors
 where [Last Name] Like 'Mann%' 


--clustered index primary keys have a clustered index by default 

Create index ix_Lastname on Person(LastName)

--Force a query to use an index
Select * from Person with  (index(ix_LastName))

--a filtered index
Create index ix_City on PersonAddress(City)
Where City != 'Seattle'

--a unique index
Create unique index ix_contact on PersonContact(contactInfo)

No comments:

Post a Comment