Why am I getting index seek?

I am running below query and as it’s returning almost all records from the table, it should be using index scan not seek. Anyone, please explain why it’s using seek not a scan.

DROP TABLE tblPlanDiff
CREATE TABLE tblPlanDiff(Sno int identity,Col_1 int,Col_2 int)
DECLARE @i int=1
INSERT INTO tblPlanDiff values(@i*2,@i*3)
SET @i+=1
END CREATE UNIQUE CLUSTERED INDEX ix_Sno on tblplandiff(Sno ASC) GO CREATE INDEX ix_Col1_Col2 on tblplandiff(Col_1) INCLUDE(Col_2) GO SELECT sno,col_1,col_2 FROM tblPlanDiff WHERE col_1>2

  • SQL set-based range
  • Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
  • Change the discount in the Order Details table on all orders of products of an ordered quantity
  • Spatial Index slows down query with STContains
  • Azure SQL database is not designed to allow users change their passwords. Any workaround?
  • sqlalchemy MSSQL create database statement not allowed within multi-statement transaction
  • .NET Regex for SQL Server string… but not Unicode string?
  • SQL replace a null xml value
  • SQL Server - how to ensure identity fields increment correctly even in case of rollback
  • Generate SQL for SQLite database from Entity Framework Model
  • SQL Server restore and backup per schema
  • Cannot connect anymore to local SQL Server 2008 database
  • 2 Solutions collect form web for “Why am I getting index seek?”

    As I already mentioned in my comment: why are you worried about getting an index seek?

    Your index ix_Col1_Col2 on Col_1 includes Col_2 as an included column, and also includes Sno from the clustered index – so it contains all three columns that you need to satisfy your query.

    So in the end, the query optimizer makes a choice on how to approach this query – and it seems to have preferred an index seek – I don’t see any problem with that at all.

    When running this query on my SQL Server 2008 R2 Developer edition, I have these perf values:

    Table 'tblPlanDiff'. 
    Scan count 1, logical reads 797, physical reads 3, read-ahead reads 499, 
    SQL Server Execution Times: CPU time = 47 ms,  elapsed time = 855 ms.

    When I run the same query with the WITH (FORCESCAN) query hint, I get:

    Table 'tblPlanDiff'. 
    Scan count 1, logical reads 797, physical reads 3, read-ahead reads 499, 
    SQL Server Execution Times: CPU time = 78 ms,  elapsed time = 852 ms.

    So quite obviously, there’s hardly any difference between the two – and there might have been a tiny detail that made the query optimizer prefer the index seek over the scan. Not sure why – but I don’t seen any issue or problem with that. Do you?

    Because you have an index on Col_1, it is able to Seek within that index to a point where Col_1 values are greater than two. Just because it’s doing a Seek doesn’t mean that it’s not seeking MANY rows.

    If you see a Scan, that means it is starting at the beginning of the index and scanning from there. In a sense, an Index Seek may still “scan”; it’s just starting from a precise location in the index.

    Either way, why are you desiring a Scan over a Seek?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.