does index also change the output?

sqldba_newbie (3/1/2013)

Jeff Moden (3/1/2013)

opc.three (3/1/2013)

Jeff Moden (3/1/2013)

Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.

I think we covered the OP's question about SELECT statements so I don't feel bad taking a sidebar here.

Is this something to do with the quirky update? Can you provide some documentation that explains the behavior you're alluding too?

It certainly applies to the quirky update but I wasn't referring to such an arcane method. Gail is spot on for what I meant.

They can also affect SELECTs that use TOP with no ORDER BY. The addition of a index can instantly change what the TOP returns if the index comes into play. In most cases, of course, it is NOT something that should be relied on.

Does have a order by something like this:

(SELECT DISTINCT Row_number()
OVER (
ORDER BY AdDate ASC ) Row

Then at the end of the query result set has:

ORDER BY ROW ASC

However if i remove the "inappropriate" index, query makes use of another index and provides results as expected? I did do a update stats with full scan.

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;

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;

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'
ORDER BY type_desc;
GO

-- add nc index to change support for filter and order
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'
ORDER BY type_desc;

DROP TABLE dbo.test;