Sql – Adding a index on the table for this query

indexingperformancesql-serversql-server-2008

I have a 5 million row table that gets hit with a specific query the most. So I want add a index to speed things up.

Query:

SELECT someID 
FROM   someTable 
WHERE 
       myVarChar = @myVarChar AND
       MyBit = 0 AND 
       MyBit2 = 1 AND 
       MyBit3 = 0

Note, the myVarChar column is unique.

What would the best index be for this type of query? I currently have a single index that covers all of the 4 columns in the above query. Also, do I have to reindex every so often or its automatic?

I am using sql server 2008 standard.

Best Solution

Is someID the primary key of that table? If not, you should add it to your index to prevent a bookmark lookup.

Also, look at the execution plan for that query, if your index is properly constructed, you should see two icons in the execution plan view: SELECT and an Index Seek.