Indexes and views

Here is what we did today, but for a more thorough and organized discussion of indexes you can go to this blog entry

--indexes and views

use communityAssist

-- non clustered index
Create nonclustered index ix_lastname on Person(Lastname)

Select * From Person with (index(ix_lastName))
where Lastname='Anderson'

--filtered index
Create index ix_Apartment on personAddress (apartment)
where Apartment is not null

Create unique index ix_uniqueEmail on PersonContact(contactinfo)
where contactTypekey=6

Drop index ix_Apartment on personAddress

Create index ix_location on PersonAddress(City, State, Zip)

Create table Personb
(
 personkey int,
 lastname nvarchar(255),
 firstname nvarchar(255)
)

Insert into Personb(personkey, lastname, firstname)
Select PersonKey, Lastname, firstname from Person

Select * from PersonB

Create clustered index ix_LastNameCluster on PersonB(Lastname)
Drop index ix_lastnameCluster on Personb

Insert into PersonB
Values(60, 'Brady', 'June')

--views
Go

Alter view vw_Donors 
As
Select lastname [Last Name], 
firstname [First Name],  
DonationDate [Date],
DonationAmount [Amount]
From Person p
inner Join Donation d
on p.PersonKey=d.PersonKey


go
Select [Last Name], [First Name], [Date], [Amount]
from vw_Donors

Select * from vw_Donors
where [Date] between '3/1/2010' and '3/31/2010'
order by [Last Name]

I

go
--this creates an updatable view
Create view vw_Person
As
Select lastname, firstname
from person
go
insert into vw_Person(firstname, Lastname)
Values('test', 'test')

Select * from Person

Select * from vw_donors 
where [Last Name]='Mann'
go

--this creates a bad view because the inclusion of contactinfo causes the 
--donation amount to repeat and gives a false sense of how many donations
--each donor has made
Create view vw_BadDonors
As
Select Lastname, firstName, ContactInfo, donationDate, donationamount
From Person p
inner join personContact pc
on p.PersonKey=pc.personkey
inner join Donation d
on p.PersonKey=d.Personkey

select Distinct * From vw_BadDonors where lastname='Mann'

Select sum(Amount) From vw_donors
Select sum(donationAmount) from vw_BadDonors