Re: out of order identity field - sql2000


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns97E4EF3786F5CYazorman@xxxxxxxxxxxx
Mike C# (xxx@xxxxxxx) 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."



.

Relevant Pages

  • Re: Whats wrong with my query
    ... Assuming NO gaps in the ID numbering then the following might be what you ... You can only do this type of SQL join in the SQL view, ... You can set up the query in the grid and then modify it in the SQL ...
    (microsoft.public.access.queries)
  • Re: I think this is an SQL question...
    ... Joins is one of the weak spots of my SQL. ... Could you recommend something to read that would fill in the gaps? ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Lotto - Gaps - probability, HELP
    ... >> all gaps equal. ... >I understand how you've arrived at the 16 Lotto combinations listed. ... with 6 balls caught. ... As about the guarantee, ...
    (sci.math)
  • Re: Locking and Delay in a Bottleneck
    ... requirement that there be no gaps. ... It is often a requirement in book-keeping that must not be gaps in ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Renumbering MS db ID?
    ... Check out "Identity" in Sql Server's Books Online. ... discussion on how to handle this depending on if there are already gaps or ... Have an opinion on the effectiveness of Microsoft Embedded newsgroups? ...
    (microsoft.public.data.ado)