0

Problem is that index as is. I can't alter or add it.

Can I do something for better query plan?

Index on 2 columns: pid, Date.

But select is only on Date...

Table deal is very big (>1 000 000 rows)

create table deal
(
    Id  Int,           NOT NULL PRIMARY KEY NONCLUSTERED,
    pid Int,           NOT NULL,
    Date              smalldatetime NOT NULL
)

create clustered index pk ON deal (pid, Date)

select * 
from deal 
where Date between @d1 and @d2
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alexander Alexander
  • 21
  • 4
  • Can you change the order of the index `(Date,Pid)` ? – sagi May 26 '16 at 13:41
  • 2
    Add a separate index on just `Date` (BTW: it's a **really bad idea** to use reserved SQL Server keywords like `Date` as column names - use something **more meaningful** to your problem domain, e.g. 'DealDate` or whatever!). How many rows out of those 1 million or more does a single `date` value return?? – marc_s May 26 '16 at 13:44
  • "deal is very big (>1 000 000 rows)"... that's not very big... – Tanner May 26 '16 at 13:46

2 Answers2

0

I would recommend using ID as your clustered index and create a second index on date including ID and PID (Covering Index). If you do batch inserts then drop the date index and recreate it after to improve insert performance.

Joe C
  • 3,925
  • 2
  • 11
  • 31
0

I would create my clustered index over id and create a non clustered index over date like this

CREATE NONCLUSTERED INDEX noncluxidxdate ON deal (Date)
INCLUDE (id, pid);

this post will help you understand What do Clustered and Non clustered index actually mean?

Community
  • 1
  • 1
Rafa
  • 443
  • 5
  • 14