Views and Indexes

--Views and indexes
--View is stored query or filter
--
use CommunityAssist
go

Create View vw_Employee
As
Select LastName [Last Name], 
firstName [First Name], 
HireDate [Hire Date], 
SSNumber [Social Security No.], 
Dependents
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
 
go 
Select * from vw_Employee 
--you have to use the aliases as the field names
--when you query a view
--the view helps abstract the database
--users of the view don't know the underlying
--table structure or column names
Select [Last Name], [Social Security No.] From vw_Employee
Where Dependents is not null
go
--another view
--after you create a view if you want to change it
--you must alter it
--also the ORDER  BY clause is forbidden in views
Alter View vw_DonationSummary
AS
Select MONTH(DonationDate) [Month],
Year(DonationDate) [Year], '$' +
cast(SUM(donationAmount) as varchar(7)) [Total]
From Donation
Group by Year(DonationDate), MONTH(donationDate)

Select * from vw_DonationSummary
Where [Year]=2010

--binary tree non clustered index
Create index ix_LastName on Person(LastName)

--a clustered is where table is physically order by the indexed field
create clustered index ix_lastnameClustered on Person(LastName)

--unique and filtered--filtered because of the where clause
--it only applies to the values that meet the condition
--also a index can be on more than one column in a table
--just separate them by commas in the parentheses
Create unique index ix_uniqueEmail on PersonContact(contactinfo)
Where ContactTypeKey = 6

Select * from PersonContact

--will generate an error because it is a duplicate 
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('lmann@mannco.com', 2,6)

--drops an index
Drop index ix_uniqueEmail on PersonContact

--syntax for forcing an index
Select LastName, firstname from Person with (nolock,index(ix_LastName))