Indexes

--Indexes
--Clustered -- physically orders the table--by default the
--primary key is a clustered index
--unclustered indexes which form a B-Tree
--unique indexes

Use CommunityAssist

--dropped the primary key and its clustered index
Alter table personAddress
Drop Constraint PK__PersonAd__7CE0EF7203317E3D

--Add a new unclusted primary key
Alter Table PersonAddress
Add Constraint PK_PersonAddress primary key nonclustered(PersonAddressKey)

--create a new unclustered index
Create clustered index Ix_ClusteredLastName on PersonAddress(PersonKey)

--not sure if any change can be seen
Select * From PersonAddress

--creating non clustered indexes
Create index ix_LastName on Person(lastName)
--non clustered index on multiple fields
Create index ix_cityState on PersonAddress(City, [State])

--creating a unique index--fails because non unique data
--in table
Create unique index ix_ContactInfo on PersonContact(ContactInfo)

--disable an index
Alter index ix_LastName on Person Disable

--re-enable and rebuild an index
Alter index ix_LastName on Person Rebuild

--select with forced use of indexes
--generally SQL's query optimizer will ignore
--indexes if the number of table rows is too small
Select Lastname, Firstname, street, city, [State], zip
From Person p with (index (ix_LastName))
inner join PersonAddress pa with (index (Ix_ClusteredLastName))
on p.PersonKey=pa.PersonKey
Where LastName='Smith'