CODEWITHSUNDEEP
indexingsql-server-2012
Chad Portman
Chad Portman

Reputation: 1226

Index with multiple fields or multiple indexes

I have done some looking around trying to learn about indexes. Was able to learn a lot from Brent Ozar's site but had a follow up question.

In Brent's video on sargabliity he mentions that if the column is in the where clause then it needs to be the first column in the index. My question is if the where clause has multiple columns from the same table should that be one index with the columns listed in the order of the where clause or should each column have it's own index?

These tables are updated 1/day and contain about 10 million records.

I am using SQL Server 2012.

Upvotes: 0

Views: 655

Answers (1)

FLICKER
FLICKER

Reputation: 6693

Base on your query, those two different indexing will be much different.

if you have both fields in the WHERE clause,

If you have one composite index (2 field in one index), SQL Server will use below plan and it is very fast.

Composite Index Plan

If you have 2 separate index on each field, SQL Server uses a different plan (much slower that above)

Separate Index Plan

As you see, it finds each data set in different threads and then merge them.

You may get different plans based on your data though, but this is the general behavior of SQL Server.

Upvotes: 1

Related Questions