CODEWITHSUNDEEP
sqlsql-servert-sql
MKB
MKB

Reputation: 281

Single column indexes vs multiple column indexes

Imagine two indexes:

idx_1 (urlId, name)
idx_2 (urlId)

Where urlId is bigint and name is nvarchar(320). And a query which use urlId for filtering and does not affect in any way column name.

  1. Does second index will improve performance? Will the difference be significant?
  2. when single column index used instead of compound multiple column index can boost query performance?

I appreciate answers based on examples.

EDIT:

From the answers I've learned that it isn't so simple. It's not 'black and white'. So I change my question a bit. I assume the second one should be a bit faster because it is smaller. But it seems not in all cases. So WHEN there can be no difference in performance and WHY in these situations it happens.

Upvotes: 0

Views: 1976

Answers (1)

Gary Walker
Gary Walker

Reputation: 9134

If your data is usually unique based on urlId alone, you are probably better off not creating a secondary index esp. if you can use irlId as the clustered index. Since the 2nd part of the index is much larger than bigint, you will likely have better performance by have more keys per block and you avoid the extra overhead in key maintenance. However, if you can be fairly sure that you don't have lots of records that share a common urlId, the 2-part is likely to be a better choice for better worst-case performance. You really have to measure to be sure though if the difference is important to you.

Upvotes: 3

Related Questions