CODEWITHSUNDEEP
sql-servert-sql
Arkadiusz
Arkadiusz

Reputation: 427

What column choose to create clustered index

I have a table which has over 25 millions rows. The table gets bigger every day (roughly 35 000 rows). I created nonclustered index on 2 columns - date and debt_id (these columns are used most frequently in WHERE clouse), each debt_id occurs only once in each date). So the table is still the heap because it doesn't have a clustered index. Do you think that it would be a good idea to add identity column (1,1) and create clustered index on it? Or what do you think I should do to boost performance on this table?

Upvotes: 0

Views: 221

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

If your two columns are unique in any case, you can use them as clustered index.

Most important: A clustered index should not change its values, and new rows should be appended in the correct order.

  • The time of insertion as DATETIME2 as the first column of your clustered index is a good bet here.
  • The uniqueness must be guaranteed by the combination of this value and the debt_id you've mentioned.
  • Assuming that neither the time of insertion nor the debt_id are changing data, this looks like a very good combined PK.

Otherwise your clustered index might get fragmented. This would make things even worse... (The main reason why UNIQUEIDENTIFIER IDs tend to be very bad as clustered PK. Regularly running index repair scripts can be an acceptable workaround.)

A non-fragmented clustered index will speed up things, as long as your query filters on both columns (at least the first one must be involved).

You can add more indexes, you might even INCLUDE heavily needed values to them.

Other indexes will use the clustered index as lookup (might need recreation after building the clustered one). This helps if the clustered index is well performing and can make things worse if not.

So I'd say: If the above is true in your case, an additional ID IDENTITY is of little help. This will add one more step to each query, as the Query will need an additional lookup. But, if the index is prone to fragmentation, I'd rather add the additional ID. And finally, to cite George Menoutis in comments

Well, I certainly can't answer this; it is a deep design choice with loads of pros, loads of cons, and loads of discussion

Without knowing your database and your needs this is pure guessing...

Upvotes: 1

Related Questions