Sorry for dragging the discussion. I setup a small test below.

CREATE TABLE DUMMY_TABLE

(

ID_COL INT,

DESC_COL CHAR(8000),

)

GO

INSERT INTO DUMMY_TABLE

VALUES

(1, REPLICATE('X',8000)),

(2, REPLICATE('X',8000)),

(3, REPLICATE('X',8000)),

(4, REPLICATE('X',8000)),

(5, REPLICATE('X',8000)),

(6, REPLICATE('X',8000)),

(7, REPLICATE('X',8000)),

(8, REPLICATE('X',8000)),

(9, REPLICATE('X',8000))

GO

CREATE CLUSTERED INDEX IX_DUMMY_TABLE ON DBO.DUMMY_TABLE

(

ID_COL

)

GO

SET STATISTICS IO ON

-- Query 1

SELECT * FROM DUMMY_TABLE WHERE ID_COL > 1 --AND ID_COL < 9

------------------------

-- Query 2

SELECT * FROM DUMMY_TABLE

------------------------

-- Query 3

SELECT * FROM DUMMY_TABLE WHERE ID_COL = 1 --AND ID_COL < 9

GO

DROP TABLE DUMMY_TABLE

SET STATISTICS IO OFF

Scott, you were right. Execution plans (actual/estimate) show SEEK operation for 1st & 3rd queries. It’s quite surprising to me but I have to accept the fact, it’s SEEK operation.

BUT when I see the IO statistics, I see no difference between first two queries. So the SEEK predicate on in first query is fake IMO.

(9 row(s) affected)

(8 row(s) affected)

Table 'DUMMY_TABLE'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(9 row(s) affected)

Table 'DUMMY_TABLE'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Table 'DUMMY_TABLE'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.