identity and order-by in select-intos

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)What is the behavior of the other query you are having problems with ?|||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)?