Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » does index also change the output?
Add to briefcase
23 posts, Page 1 of 3
123»»»

does index also change the output? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
curious_sqldba
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: Yesterday @ 4:13 PM
Points: 1,172, Visits: 2,687

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
Lynn Pettis
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 @ 6:19 PM
Points: 21,624, Visits: 27,464
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
opc.three
Posted Thursday, February 28, 2013 10:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 6,724, Visits: 11,771
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425345
Jeff Moden
Posted Friday, March 01, 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 @ 6:46 PM
Points: 32,909, Visits: 26,798
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1425374
GilaMonster
Posted Friday, March 01, 2013 3:02 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996
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
opc.three
Posted Friday, March 01, 2013 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 6,724, Visits: 11,771
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425476
GilaMonster
Posted Friday, March 01, 2013 6:59 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996
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
Jeff Moden
Posted Friday, March 01, 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 @ 6:46 PM
Points: 32,909, Visits: 26,798
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1425573
curious_sqldba
Posted Friday, March 01, 2013 9:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:13 PM
Points: 1,172, Visits: 2,687
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
opc.three
Posted Friday, March 01, 2013 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 6,724, Visits: 11,771
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425595
« Prev Topic | Next Topic »

Add to briefcase
23 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.