Posted by Erland Sommarskog on 10/20/05 01:11

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> If you want the cosmetic reassurance of seeing the values in the
> "right" order you can try creating a new table and using INSERT SELECT
> with an ORDER BY clause to populate it. Be aware that that method is
> definitely NOT always guaranteed to generate the values in the correct
> order - so don't count on it. You may also find that the effort of
> creating and populating a new table negates any slight advantage of
> "fixing" the IDENTITY sequence.

In fact, the word I have from Microsoft people is that INSERT SELECT
ORDER BY is guaranteed to produce the expected result.

This however does not apply to SELECT INTO.

I would recommend, nevertheless, to add an OPTION (MAXDOP 1) as an
extra precaution.

It is also worth pointing that the main reason for having IDENTITY
in opposition ro rolling your own, is that IDENTITY scales better in
an environment where there is a heavy insertion rate. When you roll
your own, inserts tend to get serialized.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]