I have the following table
CREATE TABLEA( ID NUMERIC(19,0) IDENTITY(1,1), COLUMNA varchar(256) NOT NULL, COLUMNB varchar(256) NOT NULL, COLUMNC varchar(256) NOT NULL, COLUMND varchar(max) NULL, COLUMNE bigint NOT NULL, COLUMNF numberic(19,0), CONSTRAINT PK_ID PRIMARY KEY CLUSTERED ( COLUMNA ASC ) ) ON PRIMARY CREATE NONCLUSTERED INDEX IDX_COLUMNE_COLUMNF ON TABLEA(COLUMNE, COLUMNF)
I am running the following CTE.
WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY COLUMNE ORDER BY COLUMNE) AS rownum, COLUMNA, COLUMNB, COLUMNC, COLUMND, COLUMNE, COLUMNF WHERE COLUMNF = 1 AND COLUMNE >= 1472738400000 AND COLUMNE <= 1475244000000 )
Now the question. When I run
SELECT TOP 30000 * FROM CTE
SQL does an index seek on the nonclustered index with a key lookup on the clustered and takes 3 to 4 seconds to complete.
However, if I run the below SQL does a clustered index scan and completes in about 15 seconds.
SELECT TOP 30000 COLUMNA, COLUMNB, COLUMNC, COLUMND, COLUMNE, COLUMNF FROM CTE
To give some idea of distribution of values:
- The table contains approx. ~13.7 million rows.
- COLUMNF always has a value of 1.
- Excluding the TOP 30000 the query returns 474296 rows, of which there are 92683 have a rownum value of 2 or higher.
Can anyone provide any insight as to why SQL would pick a different execution plan for the two queries?
SELECT by column name https://www.brentozar.com/pastetheplan/?id=HyJZeZlC