[dba-SQLServer] Covering Indexes - Q2

John,

QA is your friend ;-)  Go and test in QA, it's the only place you'll
likely get a definative answer.

Part of the problem is I'm guessing that most on this list are
developers not DBA's.  Your questions are leaning into the DBA realm
IMO.
The other issue is that what works on my setup may be different on
yours.  SQL will optimise sql statements based not only on
what indexes are available but # of processors, memory, # of rows, # of
joins etc etc.

'If so should the PKID be first or last field in the index?'  I've found
no benefit from using a PKID (Clustered) as part of a covering index.
I think it is redundant to do so anyway.  

Not much help I know.

Hope it goes well for you, you should be able to advertise yourself as a
SQL dba by the time you get this all down pat.

cheers


Michael M

OK, so I have done a little reading on covering indexes.  Now, I have 7
"presence of adults AA to BB" fields where AA and BB are ages - 18-24,
25-34 etc.

I have many cases where the client asks for ("PresenceOfAdults_18_24 OR
PresenceOfAdults_25_34") AND NarrowIncomeBand IN('A','B','C','D') AND
XXXX.
These are essentially two different select queries.

Possible strategies: 

1) TWO subqueries - 
	SELECT PKID FROM tblXXX WHERE (PresenceOfAdults_18_24 = 'Y' OR
PresenceOfAdults_25_34='Y')
	SELECT PKID FROM tblXXX WHERE NarrowIncomeBand
IN('A','B','C','D')

Then JOIN the two queries on the PKID

2) One big query with the where clause handles both "subqueries".

If I want to use strategy 1, should I 

1) Create "cover queries" with the PKID and all of the PresenceOfAdults
fields in one cover query?  If so should the PKID be first or last field
in the index?
2) Create 7 different "cover queries" where the PKID and one
PresenceOfAdults field is combined.  If so should the PKID be first or
last field in the index?
3) Create 7 non cover queries containing just a single PresenceOfAdults
field.

To this point I have simply indexed each field that I want to do
searches on.  However I have situations where the client just asks for a
single one of these PresenceOfAdults fields and then other completely
non-related fields (NarrowIncomeBand, Gender, Waterski).  This stuff is
taking a long time to complete (30 minutes or even more).

I am having a tough time figuring out how to even test this stuff.  I
have not figured out the query analyzer though I am headed there.  Can I
have two different indexes using different strategies and look at the
execution plan to see whether one or the other or neither is used?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com