set statistics io on

select lastname, firstname, middleinitial, phone_no
from member
where lastname like '[S-Z]%'

select lastname, firstname, middleinitial, phone_no
from member WITH (INDEX (0))
where lastname like '[S-Z]%'

select lastname, firstname, middleinitial, phone_no
from member WITH (INDEX (Ind1))
where lastname like '[S-Z]%'

create index Ind1 on member(lastname)

create index Index1 
on member(lastname,firstname, middleinitial, phone_no)

create index Index2 
on member(lastname,firstname, middleinitial)
include (phone_no)

create index Index3 
on member(lastname,firstname)
include (middleinitial, phone_no)

create index Index4
on member(lastname)
include (firstname, middleinitial, phone_no)

sp_dbcmptlevel credit, 90

select * from sys.indexes 
where [object_id] = object_id('member')

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('member'), null, null, 'detailed')

select lastname, firstname, middleinitial, phone_no
from member WITH (INDEX (Index1))
where lastname like '[S-Z]%'

select lastname, firstname, middleinitial, phone_no
from member WITH (INDEX (Index2))
where lastname like '[S-Z]%'

select lastname, firstname, middleinitial, phone_no
from member WITH (INDEX (Index3))
where lastname like '[S-Z]%'

select lastname, firstname, middleinitial, phone_no
from member WITH (INDEX (Index4))
where lastname like '[S-Z]%'
