sql server - Query Index for Dictionary Based Queries -

what efficient way query , index following:

select * persons.lastname a-d select * persons.lastname e-l select * persons.lastname m-r select * persons.lastname s-z 

i'm using following terribly inefficient , difficult index:

where lastname '[a-d]%' 

any ideas on better way go ? think might scenario filtered index, clause need more sargable.

thanks

as sam says, like '[a-d]%' sargable (well almost). because of not optimized predicate (see below more info).

example #1: if run query in adventureworks2008r2 database

set statistics io on; set nocount on;  print 'example #1:'; select  p.businessentityid, p.lastname    person.person p   p.lastname '[a-a]%' 

then, execution plan based on index seek operator (optimized predicate: green rectangle, non-optimized predicate:red rectangle): output set statistics io

example #1: table 'person'. scan count 1, logical reads 7 

this means server have read 7 pages buffer pool. also, in case, index ix_person_lastname_firstname_middlename includes columns required select, fromand where clauses: lastname , businessentityid. if table has clustered index non clustered indices include columns clustered index key (businessentityid key pk_person_businessentityid clustered index).

but:

1) query have show columns because of select * (it's bad practice): businessentityid, lastname, firstname, middlename, persontype, ..., modifieddate.

2) index (ix_person_lastname_firstname_middlename in previous example) doesn't includes required columns. reason why, query, index non-covering index.

now, if execute next queries diff. [actual] execution plans (ssms, ctrl + m):

set statistics io on; set nocount on;  print 'example #2:'; select  p.*    person.person p   p.lastname '[a-a]%'; print @@rowcount;  print 'example #3:'; select  p.*    person.person p   p.lastname '[a-z]%'; print @@rowcount;  print 'example #4:'; select  p.*    person.person p with(forceseek)   p.lastname '[a-z]%'; print @@rowcount; 

results:

example #2: table 'person'. scan count 1, logical reads 2805, lob logical reads 0 911  example #3: table 'person'. scan count 1, logical reads 3817, lob logical reads 0  19972  example #4: table 'person'. scan count 1, logical reads 61278, lob logical reads 0 19972 

execution plans:

plus: query give number of pages every index created on 'person.person':

select i.name, i.type_desc,f.alloc_unit_type_desc, f.page_count, f.index_level sys.dm_db_index_physical_stats(     db_id(), object_id('person.person'),      default, default, 'detailed' ) f  inner join sys.indexes on f.object_id = i.object_id , f.index_id = i.index_id order i.type   name                                    type_desc    alloc_unit_type_desc page_count index_level --------------------------------------- ------------ -------------------- ---------- ----------- pk_person_businessentityid              clustered    in_row_data          3808       0 pk_person_businessentityid              clustered    in_row_data          7          1 pk_person_businessentityid              clustered    in_row_data          1          2 pk_person_businessentityid              clustered    row_overflow_data    1          0 pk_person_businessentityid              clustered    lob_data             1          0 ix_person_lastname_firstname_middlename nonclustered in_row_data          103        0 ix_person_lastname_firstname_middlename nonclustered in_row_data          1          1  ... 

now, if compare example #1 , example #2 (both returns 911 rows)

`select p.businessentityid, p.lastname ... p.lastname '[a-a]%'` vs. `select * ... p.lastname '[a-a]%'` 

then see 2 diff.:

a) 7 logical reads vs. 2805 logical reads and

b) index seek (#1) vs. index seek + key lookup (#2).

you can see performance select * (#2) query far worst (7 pages vs. 2805 pages).

now, if compare example #3 , example #4 (both returns 19972 rows)

`select * ... '[a-z]%` vs. `select * ... with(forceseek) '[a-z]%` 

then see 2 diff.:

a) 3817 logical reads (#3) vs. 61278 logical reads (#4) and

b) clustered index scan (pk_person_businessentityid has 3808 + 7 + 1 + 1 + 1 = 3818 pages) vs. index seek + key lookup.

you can see performance index seek + key lookup (#4) query far worst (3817 pages vs. 61278 pages). in case, can see , index seek on ix_person_lastname_firstname_middlename plus key lookup on pk_person_businessentityid (clustered index) give lower performance 'clustered index scan'.

and these bad execution plans possible because of select *.