The Index: An Elegy
(I have always wondered why nobody has written a book on SQL Server in verse. To correct this lamentable gap in the market, I have been penning some stanzas.
Here, as a sample, is a short verse on indexing)
An index is used as a short-cut to data
a table will warrant one sooner or later
Because only one can be clustered, beware
and ponder the index you cluster with care
the issues are clearer than you might suppose
this index determines the order of rows
so searching the index requires less I/O.
Selecting the column on which it should go
depends on the way that the rows are selected,
which should become clear if the Schema’s inspected.
One problem, however, I think you should know,
retrieving a range can be horribly slow.
A non-clustered index is almost as good
once ordering keys can be well understood
make sure that the columns you use are selective
for if too few values, it’s most ineffective
if data is changing or updating too
with frequent insertions, keep indexes few.
from 2000 on you can index a view
(but then there’s restrictions on what you can do)
and even on computed columns as well
but only if deterministic as hell
For reasons which often are misunderstood
a non-clustered covering index is good
when composite columns are used with some care
they outperform anything else that’s out there