CODEWITHSUNDEEP
sqlsql-serversql-server-2008indexing
vakarami
vakarami

Reputation: 625

Speed up SQL Server 2008 query using index

I have a query like below

    Select top(10) *
    from myTable
    where CreateTime between '2014-2-3' and '2014-2-5' 
    and (Result is null or Result != 1)

But when i build an index like below

    CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[mytable] 
    (
[CreateTime] ASC,
    Result ASC
    )
    INCLUDE ( [ID])

([ID] is primary Key of mytable)

Sql server does not use that index

how can i speed up above query?

Upvotes: 0

Views: 402

Answers (2)

mordack550
mordack550

Reputation: 500

Try to simply build the index like

CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[mytable] 
(
   [CreateTime]
)

Since you are filtering on CreateTime, just build a covering index on that column. I don't think that adding Result to the index will help the query.

Also you don't have to include the primary key column in the index, SQL Server does that automatically.

Upvotes: 0

zhongxiao37
zhongxiao37

Reputation: 987

Try to use if you want to force sql server to use specific index.

Select top(10) *
from myTable WITH (INDEX(IX_Index))
where CreateTime between '2014-2-3' and '2014-2-5' 
and (Result is null or Result != 1)

Upvotes: 1

Related Questions