What will be the output of the following?

  • UMG Developer

    SSChampion

    Points: 13482

    Sadowy,

    I think it is very common for the questions/answers to be corrected/edited between when they are email and when you may get to the web site. Being on the west coast of the US corrections are normally made before I get up and attempt the question.

    Given that I don't pay much attention to the question in the email, I always read and answer what is on the web site.

  • JF1081

    SSC Eights!

    Points: 982

    sadowy (6/30/2010)


    I object.

    The question presented in the email had:

    mainkey int not null identity (-1,1)

    but the code in the question and answer on the web site had:

    mainkey int not null identity (-1,-1)

    EDIT: Removing grumpy comment that I added. Sorry, it has been a tough day.

    The reason the email and question are different is explained in the 13 previous pages. You probably do not have to look much past page 2.

  • badatthis

    SSC Enthusiast

    Points: 133

    This may have been covered but can someone tell me why the answer says the order will be -1,-2,-3 since in the create table NO PK IS DEFINED? I understand that this is the order the data was inserted, but without a PK or more specificly a clustered index how can you be assured of the data sort order.

  • UMG Developer

    SSChampion

    Points: 13482

    badatthis: Yes, that issue has been brought up more than once. And I think it goes further than that, even with a PK/clustered index you aren't guaranteed a sort order unless you specify ORDER BY in your SELECT statement. (If I am wrong on this can somebody correct me?)

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    UMG Developer (6/30/2010)


    badatthis: Yes, that issue has been brought up more than once. And I think it goes further than that, even with a PK/clustered index you aren't guaranteed a sort order unless you specify ORDER BY in your SELECT statement. (If I am wrong on this can somebody correct me?)

    no you're right.

  • dhirendra.singh

    Newbie

    Points: 2

    Create Table MyTable

    (

    mainkey int not null identity (-1,-1)

    )

    GO

    insert into MyTable default values

    insert into MyTable default values

    insert into MyTable default values

    Select * from MyTable

    == > ok in that case mainkey will increase by -1 ,

    it will show -1,

    -1+(-1)=-2

    -2 + (-1) =-3

    and so on

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    UMG Developer (6/28/2010)


    Also, if the identity increment was -1, then both option 1 and 3 would be correct, as there is no clustered index to order it, and no ORDER BY statement specified, so either -1, -2, -3 or -3, -2, -1 could be the result.

    +1

    Without explicit ORDER BY, there is no way we can predict the order. We could get any permutation, such as 1-,-3,-2 or -2,-1,-3 and so on.

    Can you fix the incorrect multiple choice?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715809

    Added an order by to the question.

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    Thank you Steve!

  • vinayak-365346

    Mr or Mrs. 500

    Points: 590

    Nice question. now I got clear idea on Identity(seed, increment). Thanks

Viewing 10 posts - 136 through 145 (of 145 total)

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