CODEWITHSUNDEEP
sqlsql-serverindexing
sa-es-ir
sa-es-ir

Reputation: 5102

How SQL Server uses its Index

I work in a bank. We have a table with 4 billion records and I created a non-clustered index for three columns: ACNO, HisDate, BranchNo.

My question is, if run this query

SELECT * 
FROM dbo.LastBalance 
WHERE HisDate = '20170813' 
  AND BranchNo = '0512'

Does SQL Server use that index or not?

I should use three columns in the WHERE clause until SQL Server uses the index?
Is column sequence important in WHERE cluase?
Thank you.

Upvotes: 2

Views: 172

Answers (2)

marc_s
marc_s

Reputation: 755541

If the index is really created in this manner:

CREATE INDEX (name) ON dbo.YourTable(ACNO, HisDate, BranchNo)

then it might be used, if:

  • you use all three of these columns in a WHERE clause
  • if you use just ACNO and HisDate
  • if you use just ACNO

but it will NEVER be used in your case - it can only be used if you specify the n left-most columns in the index (this applies to all compound indices - indices that use more than one column).

You are only using HisDate and BranchNo - but you're not specifying ACNO, so this index is useless and will never ever be used. You should have an index on (HisDate, BranchNo) - that would be helpful

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Probably not. Oracle has a "skip-scan" operation on its indexes. I don't think SQL Server implements such a thing.

The best index for the query would have the HisDate and BranchNo as the first two columns in the index (in either order).

Upvotes: 1

Related Questions