Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: identity and order-by in select-into's

    I have been using identity() and an order-by clause to create an ordering based on some table values. It appears to work, but can this be relied upon for large tables???

    The helpfiles don't say whether identity() honors the order-by clause.

    (I have a larger, more complex query in which it appears not be behaving in this manner)

    drop table jim

    truncate table fred
    insert into fred values(50)
    insert into fred values(10)
    insert into fred values(40)
    insert into fred values(20)
    insert into fred values(30)

    select
    identity (int, 1,1) as order_id,
    *
    into jim
    from fred
    order by value

    select * from jim order by order_id

    order_id value
    ----------- -----------
    1 10
    2 20
    3 30
    4 40
    5 50

    (5 row(s) affected)

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What is the behavior of the other query you are having problems with ?

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: identity and order-by in select-into's

    RE:

    Q1 It appears to work, but can this be relied upon for large tables?
    A1 My guess is that statements within a transaction or stored procedure in the same scope using identity() may potentially allow gaps (skipped ID numbers) or apparently "inexplicable" insertion failures and rollbacks to occur. {Somewhat similar to the sorts of issues seen occasionally when carelessly using @@Identity.} For example: Consider an insert on a target table with an identity column that triggers related inserts to audit tables (which also have identity values). Calling @@IDENTITY after the statement and using its value for may sometimes result in "unexpected" behavior, as it will reflect the identity value of one of the audit table identities (instead of the last identity value of the target).

    RE:

    Q2 I have been using identity() and an order-by clause to create an ordering based on some table values.
    Q3 (I have a larger, more complex query in which it appears not be behaving in this manner)
    A2 The Identity () function is really probably meant to provide unique column row values only (in any event, relational tables aren't normally ordered per se). Therefore, based on that, I would be very hesitant to attempt to use the Identity () function (and rely on its apparent behavior) to create some sort of artificial table ordering.

    A3 How is it "not be behaving in this manner"? Have you considered simply clustering the table on whatever you want the table ordered by (if that is the nature of the required result)?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •