CODEWITHSUNDEEP
sql-servert-sql
Marc
Marc

Reputation: 9527

Non Clustered Index - One or two index for almost the same need (join between two tables)?

I have a great amount of queries where I make a join between two tables on three columns: col1,col2 and col3. So I created a non clustered index on those three columns in the two tables. But I have the same great amount of queries where I make a join between the two tables on two columns only: col1 and col2. So what you be more efficient (putting aside the size of the index)?

Create a second non clusted index on col1 and col2 in the two tables or is one of those two indexes enough to handle the work? And if so which one? Thank you in advance for your help

Upvotes: 1

Views: 471

Answers (2)

Angel M.
Angel M.

Reputation: 1358

If you have already an index in columns col1, col2 & col3 and you have queries by col1, col2 & col3 and another queries by col1, col2 it is Ok and the Query Optimizer will use the index in both searches. Same question happens if the order is different but the 3 or first 2 fields are used. E.g. col2, col1, col3 or col3, col2, col3. Different question is if the search uses only by col2, col3 or even only per col2. For these cases you will need to create another index on columns col2, col3. If you have queries that use only col3 you will need to create another index for col3.

Upvotes: 0

dfundako
dfundako

Reputation: 8314

First, SQL Server indexes, structure, and usage is heavily documented. You should go read up on it.

In the situation you describe, you would want a single index on col1/col2/col3 in that order. That index can be used in both a query with joins on col1/col2/col3 or a query with col1/col2. That index CANNOT be used in a query that joins on col2/col3 due to how indexes work.

The next thing to consider is the SELECT statement. If you are querying columns other than col1/col2/col3, you will incur key/rid lookups that will hurt performance. You will want to add an INCLUDE section to your index.

Upvotes: 4

Related Questions