Found one. I was never under the impression that adding or changing an index was completely harmless for various other reasons but not for this particular one. I am still faulting over the fact that it is something in a SELECT-column-list that is altering the outcome, although it is being incorporated into a resultset so all bets are off. This has been added to my list of things to consider when tuning, reviewing and writing code.

Setup:

USE tempdb;

CREATE TABLE dbo.test

(

name VARCHAR(100),

type_desc VARCHAR(100)

);

INSERT INTO dbo.test

( name, type_desc )

VALUES ( 'xyz', 'user' );

INSERT INTO dbo.test

( name, type_desc )

VALUES ( 'abc', 'user' );

CREATE CLUSTERED INDEX [cx] ON dbo.test (name);

Query 1:

SELECT name,

row_num

FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type_desc ),

name,

type_desc

FROM dbo.test

) tbls ( row_num, name, type_desc )

WHERE row_num = 1;

Add another index:

-- add nc index to change support for ROW_NUMBER

CREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc DESC);

Query again, same as above but different result:

SELECT name,

row_num

FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type_desc ),

name,

type_desc

FROM dbo.test

) tbls ( row_num, name, type_desc )

WHERE row_num = 1;

Unsetup:

DROP TABLE dbo.test;