Views and Indexes

 --Views

 use communityAssist
 Go
 Alter View vw_donorInfo
 AS
 Select Lastname as [Last Name],
 FirstName as [First Name],
 ContactInfo as Email
 From Person p
 inner Join PersonContact pc
 on p.PersonKey=pc.PersonKey
 Where ContactTypeKey=6
 Go
 --to change the view you either drop and recreate
 Drop View vw_donorInfo

 --or you can make changes with ALTER


 Update vw_donorInfo
 Set [last Name] = 'manning'
 Where Email='lmann@mannco.com'

 Select * from vw_donorInfo
 order by [Last Name]

Select * from vw_donorInfo
Where [last name] like 'C%' 

Create View vw_person
As
Select PersonKey,FirstName,Lastname
From Person

Select * from vw_person
Where 

Update vw_Person set Firstname='Jason' where Personkey=1


Create nonclustered index ix_lastname on Person(LastName)
Create nonclustered index ix_last on Person(LastName)

--query which forces the use of the ix_lastname 
Select Lastname as [Last Name],
 FirstName as [First Name],
 ContactInfo as Email
 From Person p WITH ( INDEX (ix_lastname) )
 inner Join PersonContact pc
 on p.PersonKey=pc.PersonKey
 Where ContactTypeKey=6

 --unique and filtered index
 Create unique index ix_email on PersonContact(contactinfo)
 Where ContactTypeKey=6

 --drop an index
 Drop index ix_email on PersonContact

 --composite index (two or more columns
 Create index ix_Address on PersonAddress(State, zip)

 --primary key's usually have a clustered index by default
 --so to add a clustered index you need to drop the key
 Alter table PersonB
 Drop Constraint [PK__PersonB__5F59DF1842B1C384]

 
 --now add a key
 Create clustered Index ix_Personb on PersonB(PersonKey)

--this would create a primary key without a clustered index
Alter table PersonB
Add Constraint PK_Personb Primary Key nonclustered(PersonKey) 

 Select * from PersonB

 Drop index ix_PersonB on PersonB

  Create clustered Index ix_Personb on PersonB(LastName)