Post reply

Why same data/sp in different DB returns result in different order?

  • August 9, 2012 at 2:11 pm

    #260582

    Hi folks,

    This should not be a difficult question but it is driving me nut!

    I have two DBs, one copied from the other for migration purpose.

    In both DBs, I have a table called Ranking with the following data(sorry I don't know how to quickly generate the script for you to re-create the data, please put your script below, thanks):

    ID RankingType Ranking

    0All Unknown

    1IntegrityVital

    2IntegrityHighly Valuable

    3IntegrityValuable

    4IntegrityLimited Value

    5AvailabilityCritical

    6AvailabilityHigh

    7AvailabilityMedium

    8AvailabilityLow

    9ConfidentialityRegistered Confidential

    10ConfidentialityConfidential

    11ConfidentialityInternal

    12ConfidentialityPublic

    13ContinuityCritical

    14ContinuityHigh

    15ContinuityMedium

    16ContinuityLow

    I have a sp in both DBs:

    ALTER PROCEDURE [dbo].[spListAllRankByType]

    (@Type varchar(20))

    AS

    --SELECT Ranking FROM Ranking WHERE (RankingType = @Type)

    SELECT Ranking

    , [ID]-(select min(id)-1 from ranking where rankingtype = @Type) as id

    FROM Ranking WHERE (RankingType = @Type)

    group by ranking, id

    In the original DB, it returns result (which is what I want):

    DECLARE@return_value int

    EXEC@return_value = [dbo].[spListAllRankByType]

    @Type = N'Confidentiality'

    SELECT'Return Value' = @return_value

    GO

    Registered Confidential1

    Confidential2

    Internal3

    Public4

    However, if I run the same sp in another DB, it returns result like this:

    Confidential2

    Internal3

    Public4

    Registered Confidential1

    HOW COULD THIS HAPPENED?

    Thank you so much in advance!

  • August 9, 2012 at 2:13 pm

    #1524092

    Your query needs and ORDER BY clause if you want them returned in a specific order.

    ORDER BY ID

    would do what you're looking for

  • August 9, 2012 at 2:15 pm

    #1524093

    if there is no ORDER BY, SQL is free to return the data in any order it deems efficient.

    only with an ORDER BY are you guaranteed consistent order by results.

    in your case, it's going to be because either the execution plan is differnet between servers , or because the data is stored in different pages of data compared to the two servers.

    those differences allows the engine to get the data in a different way, and appears in a differnet order.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 2:17 pm

    #1524094

    How did I know Lowell would chime in on this one? Oh wait, it's right there in his sig!

  • August 9, 2012 at 2:22 pm

    #1524103

    lol so true!

    thanks for noticing Scott!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 2:28 pm

    #1524111

    Thanks guys, I realized adding "order by" can resolve the issue but was just puzzling on why the result was generated differently and why for each DB the sp always generate the same result in its respective DB?

  • August 9, 2012 at 2:32 pm

    #1524117

    halifaxdal (8/9/2012)

    Thanks guys, I realized adding "order by" can resolve the issue but was just puzzling on why the result was generated differently and why for each DB the sp always generate the same result in its respective DB?

    mostly coincidence.

    the reuse of the same plan in cache will usually get it int he same order as the previous pass.

    things that can make the results change form pass to pass would be, in no particular order:

    the plan uses parallelism to get the data. depending on which processor finishes first might affect the order of the data when it gets agregated.

    multiple insert/updates/deletes can make the data be stored differently, resulting in a new order.

    different ANSI settigns (never seen that happen, but it could affect varchars)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 10, 2012 at 7:09 am

    #1524406

    Thanks for this enlightening discussion. To get the right result for sure for every time, I think I better put an order by clause for every query I write?

  • August 10, 2012 at 8:26 am

    #1524465

    halifaxdal (8/10/2012)

    Thanks for this enlightening discussion. To get the right result for sure for every time, I think I better put an order by clause for every query I write?

    If the order of the rows returned is important, definitely yes.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 9 posts - 1 through 8 (of 8 total)

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