Clustered index

Question

Is this true that Update SQL Query is slow because of Clustered index??????

Answer 1

You would be better off saying 'slower' rather than 'slow'. When data is written to a clustered index, and it doesn't go at the very end of the table, data needs to be joggled around in order to fit it in, in the same way that adding a CD into a big stack of alphabetised CD is a lot slower than just sticking it on the top.

Answer 2

Insertion and updates are slower because of clustered indexes (particularly on huge tables) - but selects are way faster.

Making the index non-clustered usually improves inserts and updates performance retaining selection performance (selects are often less performant with a non-clustered index compared with a clustered index but something's gotta give).

Answer 3

Define slow, ofcourse the clustered index will always be slower than a non-clustered index...

Answer 4

A clustered index dictates how a table is physically stored on disk, and so updating a table with a clustered index may require that significant parts of the table be moved to make space for the new record, and that's slow.

You can mitigate the problem by setting an appropriate fillfactor for your indexes. It's not quite so bad that you have to re-jigger the whole table when you add a record to the middle; it's usually just a few pages. Fillfactor determines how much of each page is filled before creating a new page, and how much to leave as wiggle room for new insertions. A lower fillfactor on an index will leave more space for new records and therefore give faster insert times on average, at the cost of more disk space and more pages and therefore slower reads. But if you're doing a lot more updating than reading it may be worth it.

Answer 5

If you don't have any clustered indexes at all, then what you have is termed a "heap". You also have a heap of trouble, since the order of the data in your table is random - and selecting data from the table will be slow. That may be OK if you're doing many more INSERTs than you are SELECTs, but usually that's not the case.

Whether the clustered index makes INSERTs slower or not depends on:

• The fill factor of the table (i.e. whether there are enough gaps in the data to allow new data to be inserted without moving everything around).

• What columns are chosen as the cluster key.

If you're using an identity column as the cluster key, then you may find that insert performance is perfectly fine, since new entries are always being added on the end. The same may apply to a datetime column if using the current date (which of course also keeps increasing).

You need to keep the size of the cluster key small, since that's the index into the data that's stored in every other index. For example, if your cluster key consists of 3 ints and a datetime, then each entry in all your other indexes will include all that data in addition to whatever it was that you tried to index. For this reason, an identity column is actually a pretty good choice of cluster key since it's nice & small.

The perfect cluster key in any situation can only be chosen with a good deal of thought and a lot of testing (with realistically large data sets). Having a good cluster key can make a huge difference to SELECT performance - which normally outweighs any degradation in INSERT performance.