quote:
I really enjoyed reading this article - it was very well written, everything explained in simple but comprehensive terms - by the end of it all I could almost hear the grunts and groans of a non-clustered index!
Thanks. Note however that I am not saying that non-clustered indexes are bad in any way, just that I think you should always have a clustered index. Plus, if you need more, some non-clustered indexes.
quote:
However, I do have one question about the suggestion of dropping an ID column as clustered so we can actually use a column that will come in more handy and make sense to use as clustered - generally speaking - indexes are known to improve performance - but at the same time, if we have too many indexes on a table, it would also slow it down as each index means more overheads on inserts, updates and deletes! How does this fit in with the clustered/non-clustered pros and cons ?!
Well, if you have a table with say one incrementing int column 'id' and one varchar column 'name', then you are probably going to need an index on both these columns, right? The id column is primary key so it will be indexed anyway, and the name is probably a column that you will use as the search argument in where clauses, so you'll need an index for that as well.
In general I would recommend that if you're just going to have one index on a table, make it a clustered one. As I said, although a clustered index really shows it's strength in range queries, it still like a non-clustered index for 'normal' queries. So it won't hurt performance to have a clustered index as opposed to a non-clustered index as the only index on a table. The problems of not having a clustered index that I described should also show that you should choose a clustered index.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)