[dba-SQLServer] Pointless Indexes?

Don't know if I'm just repeating myself, or if this will make some points in
my posting about covering indexes a few weeks ago more clear:

Question 1:
If the bit column is not the first in a *composite* index (as opposed to an
index with included columns), then it can't be used for search operations,
and not for order-by operations either.
If the bit column is the first in a *composite* index, then in theory it
could be used for search operations, but in practice the query engine most
likely will ignore it because of its low selectivity.
If the bit column is the first in a *composite* index, then it can be useful
for order-by operations.
If the bit column is an *included* column in an index, then it can't be used
for search operations, and not for order-by operations either.
If the bit column is an *included* column in an index, then it can be useful
as a covering index preventing bookmark lookups.

Question 2:
Only 6 possible values doesn't necessarily mean an index is useless: SQL
Server maintains statistical information on the amount of duplicates for the
different values in your column, and if your search criteria points to a
value having few duplicates, then selectivity is high and the index might be
useful.
Another concern here: As you probably know, it's good practice always to
index a FK because it will make join operations faster. For developers used
to MS Access this is a point to remember: whereas MS Access automatically
creates an index on the FK when you "enforce referential integrity", SQL
Server doesn’t do this when you create a FK constraint - you have to create
the FK index explicitly yourself in SQL Server.

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Fuller
Sendt: 11. august 2008 19:31
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] Pointless Indexes?

I've been reading about indexes and bookmark lookups and so on, and
examining the indexes in our database, table by table. I have two general
questions:

1. Is there any point in including a bit column in a compound index, whether
as an indexed column or an included column? Since it can only have two
possible values, it strikes me that there is no point.
2. Similar question: I have a table with about 200K rows in it currently and
an FK in said table with only 6 possible values. Any point in indexing it?

It seems the more I read, the more confused I get :)

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