Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

does index also change the output? Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 3:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991

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?
Post #1425302
Posted Thursday, February 28, 2013 6:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1425319
Posted Thursday, February 28, 2013 10:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425345
Posted Friday, March 1, 2013 12:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425374
Posted Friday, March 1, 2013 3:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #1425408
Posted Friday, March 1, 2013 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425476
Posted Friday, March 1, 2013 6:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #1425490
Posted Friday, March 1, 2013 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425573
Posted Friday, March 1, 2013 9:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
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 #1425588
Posted Friday, March 1, 2013 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425595
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse