Removing clustered index scan even if selected columns are part of index

Question

Given below script is the simplified example of a situation I am facing -

CREATE TABLE #tmp(pk bigint IDENTITY(1,1),id bigint, dt datetime)


DECLARE @X BIGINT=1
WHILE (@X<9223372036854775807)
BEGIN
  INSERT INTO #tmp VALUES (@X,GETDATE())
  SET @x=@x+1
END

CREATE INDEX idx on #tmp(id,dt)

SELECT id,max(dt) from #tmp  GROUP BY id

Execution plan of this SP is scanning created index, how it can be moved to SEEK?

Thanks in advance!

Your query is pulling every row from the table. A scan is going to happen. — Sean Lange, Jan 29 '15 at 16:33
IF the id was duplicate with that index you could have had a loose index scan,but since id is primary key you are stuck wth index scan.Also MAX and GROUP BY are redundant.Maybe you meant `SELECT MAX(id),dt FROM t GROUP BY dt` — Mihai, Jan 29 '15 at 16:34
why would you want a seek if you are going through every row of your table?, here a scan is the better way to do it — Lamak, Jan 29 '15 at 16:37
Out of curiosity, did anyone try to run it all the way to 9223372036854775807 ? That's going to take ages, no ? — deroby, Jan 30 '15 at 11:22
9223372036854775807 is overkill, i just wanted to make sufficiently large database. A Scan is expected but would like to see if there is any better way to handle this. — UVData, Jan 30 '15 at 18:10

score 0 · Answer 1 · edited Aug 23 '15 at 19:55

0

CREATE TABLE #tmp
  (
     pk BIGINT IDENTITY(1, 1),
     id BIGINT,
     dt DATETIME
  )

DECLARE @X BIGINT=1

WHILE ( @X < 9223372036854775807 )
  BEGIN
      INSERT INTO #tmp
      VALUES      (@X,GETDATE())

      SET @x=@x + 1
  END

CREATE INDEX idx
  ON #tmp(id, dt)   /*<-- New*/

SELECT id,
       max(dt)
FROM   #tmp
GROUP  BY id

edited Aug 23 '15 at 19:55

Martin Smith

438,706
87
741
845

answered Jan 30 '15 at 11:05

Dan S

1,186
1
7
12

Please add some explanatory text. – Martin Smith Aug 23 '15 at 19:58

Removing clustered index scan even if selected columns are part of index

1 Answers1