2 id int identity (1,1) not null, 3 SomeStr char(6) default '' -- a filler 4 ) 5 GO 6 7 insert into TestingSeeks (SomeStr) 8 select top (500000) '' 9 from sys.columns c1 join sys.columns c2 We have a table here with an identity column on it, starting at 1 and incrementing by 1 row. Hence, there will be no negative values in the table. I’m going to then put a nonclustered index on that column (the table has no cluster, it’s a heap) 1 CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id) Fair enough. If I query all the rows in the table and retrieve just the ID column, I’ll get a scan on that index, as is pretty much expected and Statistics IO tells me that 935 pages were read So a read of the entire index is 935 pages. Now, let me add a filter. 1 select id from TestingSeeks 2 3 where id>0
2 3 4 5 6 7 8 9 id int identity (1,1) not null, SomeStr char(6) default '' -- a filler ) GO insert into TestingSeeks (SomeStr) select top (500000) '' from sys.columns c1 join sys.columns c2 We have a table here with an identity column on it, starting at 1 and incrementing by 1 row. Hence, there will be no negative values in the table. I’m going to then put a nonclustered index on that column (the table has no cluster, it’s a heap) 1 CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id) Fair enough. If I query all the rows in the table and retrieve just the ID column, I’ll get a scan on that index, as is pretty much expected and Statistics IO tells me that 935 pages were read So a read of the entire index is 935 pages. Now, let me add a filter. 1 2 3 select id from TestingSeeks where id>0
Difference%20Between%20index%20seek%20and%20index%20scan.docx