|
|
Posted by Mike C# on 10/02/16 11:50
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97E4EF3786F5CYazorman@127.0.0.1...
> Mike C# (xxx@yyy.com) writes:
>> I've found that it doesn't work all too often; particularly, as you
>> pointed out, if you are running hyperthreading, multiple processors, or
>> have multiple programs updating the table simultaneously. In that third
>> situation IDENTITY can leave extremely large gaps in a sequence. In my
>> experience, the only thing an IDENTITY column can guarantee is a
>> different number for each row.
>
> Gaps due to simultaneous updates is another story. If you want contiguous
> numbers, you should not use IDENTITY for your real tables. (You can
> still generate ids with help of a temp table with an IDENTITY column.)
So we agree on gaps.
>> To be honest, I don't think the INSERT statement guarantees the order in
>> which the rows will be inserted,
>
> Correct.
And insert statement order guarantees.
>> which is a large part of the OP's problem in this situation.
>
> I hope it isn't! What should matter is in which order the IDENTITY values
> are generated. And that is what is guaranteed, at least in SQL 2005.
But this is a SQL 2000 problem. If this is supposed to be guaranteed in SQL
2000 as well, then there's apparently a hot fix needed for the OP's problem.
>> BTW - I didn't think about it last night, but with the SELECT INTO
>> statement (instead of INSERT) you might be able to use the IDENTITY()
>> function to assign values in the order you require.
>
> No! I pointed this out in my post, but I say it again: SELECT INTO
> with the IDENTITY() function gives no guarantee about order, and is
> overall more prone to botch the order.
Hence my use of the word "might", as in "I didn't try this, so I don't know
if it will produce desired results or not."
Navigation:
[Reply to this message]
|