|
|
Posted by David Portas on 10/21/05 09:00
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!
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.
--
David Portas
SQL Server MVP
--
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96F6485618D6Yazorman@127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
>> You don't believe everything MS tell you, do you? :-)
>
> If MS says "we guarantee this", and I get an unexpected result, that's
> a bug, for which I can require a hotfix, if there are business reasons
> enough for it. If they say "this is undefined", I am left on my own in
> the wilderness.
>
> It might be that this was only for SQL 2005, but I'm quite sure that
> it applied to SQL 2000 as well. I need to research this a little more.
> (The repro does produce an ordered output when I test on SQL 2005.
>
>> Thanks due to Gert-Jan for the following repro. Tested on SP4 (with or
>> without MAXDOP) it doesn't order the INSERT (YMMV). Admittedly it might
>> sometimes be possible to order *some* INSERTs but how are we supposed to
>> know which will work, which won't and which will fall apart after then
>> next hotfix?
>
> With that reasoning, how could you trust SQL Server at all? After the
> next hotfix, it may say that 2 + 2 = 5.
>
> I recall that during the beta 2 phase of SQL 2000, someone in the beta
> groups asked about this, because he had a case where he did not get the
> expected results. But he also reported that with the RTM version, he did
> get the correct results. I also recall that I tried to produce a repro
> where the IDENTITY values did not follow the order. My plan was then
> to open a case, in order to get a statement whether I had a bug or
> not. However, I was never able to produce such a repro.
>
> So saying that it "works sometimes" is quite an understatement. It works
> most of the time, and in this particular case the question was about a
> one-off thing.
>
>
> --
> 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]
|