|
|
Posted by Erland Sommarskog on 10/22/05 01:00
David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Take a look at this KB:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;273586
>
> It gives an example and does indeed say that IDENTITY order is
> guraranteed.
> What it doesn't do is explain whether this is true in the case of every
> INSERT. The reason for my doubt is that this KB was changed - it used to
> state clearly that the IDENTITY behaviour was undefined!
Since it does insert any waivers, disclaimers etc, it is a natural
interpretation that you can expect INSERT SELECT to work under all
circumstances, and thus Gert-Jan's repro is a bug. Or the article is
in error.
> I think the question will be pretty academic soon. In 2005 the ROW_NUMBER
> function is surely a better way (better defined at least) to generate a
> numbered sequence.
I've checked my references for this statement, and the statement for
SQL 2005 is from a more reliable source. So I would not say that
row_number() is any better defined than IDENTITY. But I do agree for
many situations, row_number() is preferable. However, in this particular
case, Yannick wanted to populate an IDENTITY column, so a plain ORDER BY
is certainly simpler, as it relieves him from SET IDENTITY_INSERT ON.
--
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
Navigation:
[Reply to this message]
|