does index also change the output?

Question

does index also change the output?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
February 28, 2013 at 3:23 pm

#274691

I was in an assumption that index would only change the execution plan and not the actual output of the data. I have a query which gives completely different results on two different indices?

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

When you say you are getting two different results, are you saying that the result sets are different or just ordered differently? If you are talking about order, there is no guarantee of order unless you have an ORDER BY on the outer most query returning data.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

Nope. The decision by the engine to use one index or another to satisfy a query will not change the contents of the resultset. The order of the resultset is a separate matter from contents and may appear to be dictated by the index and may appear reliable but can actually vary from one execution to another even when using the same index depending on other server activity. You'll need to provide an ORDER BY if reliable ordering of your resultset is important to you.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sqldba_newbie (2/28/2013)
I was in an assumption that index would only change the execution plan and not the actual output of the data. I have a query which gives completely different results on two different indices?

Only if you have something in that query that assumes an order without having an order by specified. Essentially when you're assuming an order exists where there is none, an index (and a bunch of other things) can change that order resulting in different output (or different update results). Not the fault of the index, the result of assuming there's an order when there's no order by specified.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

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?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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?

I would guess it has to do with the UPDATE ... FROM where there's not a 1-1 relationship between the table and the results of the FROM. In that case, what value is used for the update depends on the order that the rows in the FROM are returned and that can be changed by an index (or parallelism or a few other things)

The quirky update also depends on a particular order for the rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

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.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

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.

TOP is an interesting example and granted, it could change the presented result, but without an ORDER BY, TOP makes little sense.

I do not feel that the UPDATE...FROM/JOIN and the Quirky Update apply here however. The UPDATE...FROM/JOIN is not the case that came to mind initially but is why I have begun using MERGE to replace UPDATE...FROM/JOIN queries, to gain protection from ambiguous update scenarios. iirc UPDATE...FROM/JOIN is a proprietary T-SQL implementation.

My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks. Diving into the UPDATE scenarios I think you have to parse out the modification of the data from the selection of the data to evaluate what the "resultset" would be in a SELECT scenario.

Just my take.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

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.

Without an ORDER BY that query will not be guaranteed to return the same result every time. The ORDER BY in the window function determines the ranking each row coming back from the FROM-clause yields. You need the ORDER BY on the query to guarantee order else the engine will just return the results in the quickest way it can find with no attention paid to ordering.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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.

Post the exact query. Somewhere in there there's an assumption about order without an order by, hence when you change the index, the order the rows are processed changes and your query results change because of the incorrect assumption.

Update stats has nothing to do with anything here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

What is with the DISTINCT? Using the ROW_NUMBER function will make each row unique.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

opc.three (3/1/2013)
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks.

I can probably produce you an example where it is (row number over a non-unique column and filter on that row number comes to mind)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 14

GilaMonster (3/1/2013)
opc.three (3/1/2013)
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks.
I can probably produce you an example where it is (row number over a non-unique column and filter on that row number comes to mind)

I am not picturing it. Wouldn't that require a derived table, in which case the ORDER BY in the ranking function would guarantee the correct result?

Was thinking something like this:

SELECT name,

row_num

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

name

FROM sys.tables

) tbls ( row_num, name )

WHERE row_num = 1;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato