@Gail, In thinking about this a little more, I think the example I gave is just a roundabout way of asking for TOP 1 without an ORDER BY, which I say is nonsensical.

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);

GO

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

SELECT TOP 1

name,

type_desc

FROM dbo.test

WHERE type_desc = 'user';

GO

-- add nc index to change support for ROW_NUMBER

CREATE NONCLUSTERED INDEX [ix] ON dbo.test (type_desc, name desc);

-- same query as above

SELECT TOP 1

name,

type_desc

FROM dbo.test

WHERE type_desc = 'user';

DROP TABLE dbo.test;