out of order identity field - sql2000

[bevanward@gmail.com]

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don't understand why the values in tmp.C aren't in the order
suggested by the sort.

Any comments most appreciated
Bevan

[Mike C#]

Re: out of order identity field - sql2000

Try ORDER BY C


<bevanward@gmai l.com> wrote in message
news:1150338610 .291820.67350@h 76g2000cwa.goog legroups.com...[color=blue]
> Hi All
>
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
>
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
>
> Have I been wrong in assuming that it should reflect the order from the
> sort?
>
> The code is ...
>
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.
>
> Any comments most appreciated
> Bevan
>[/color]

[bevanward@gmail.com]

Re: out of order identity field - sql2000

Hi Mike

Thanks for your comment - C is the field in the target table of the
insert that I was hoping would increment in the same sequence as the
sort of Aa, Ll

Cheers
Bevan

Mike C# wrote:[color=blue]
> Try ORDER BY C
>
>
> <bevanward@gmai l.com> wrote in message
> news:1150338610 .291820.67350@h 76g2000cwa.goog legroups.com...[color=green]
> > Hi All
> >
> > I am finding unexpected results when inserted into a newly created
> > table that has a field of datatype int identity (1,1).
> >
> > Basically the order I sort on when inserting into the table is not
> > reflected in the order of the values from the identity field.
> >
> > Have I been wrong in assuming that it should reflect the order from the
> > sort?
> >
> > The code is ...
> >
> > create table tmp (A varchar(50), L float, C int identity(1,1))
> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> >
> > and I don't understand why the values in tmp.C aren't in the order
> > suggested by the sort.
> >
> > Any comments most appreciated
> > Bevan
> >[/color][/color]

[Mike C#]

Re: out of order identity field - sql2000

You can't rely on an IDENTITY column to be assigned in a particular order or
to not have gaps in the sequence, btw. Try assigning a rank value manually
instead:

CREATE TABLE #tmp (A VARCHAR(50),
L FLOAT,
C INT NOT NULL PRIMARY KEY)

CREATE TABLE #tmp1 (Aa VARCHAR(50),
Ll FLOAT(50),
PRIMARY KEY (Aa, Ll))

INSERT INTO #tmp1 (Aa, Ll)
SELECT 'ABC', 123.45
UNION SELECT 'DEF', 456.12
UNION SELECT 'XYZ', 999.99
UNION SELECT 'RST', 023.43
UNION SELECT 'GHI', 146.56

INSERT INTO #tmp (A, L, C)
SELECT t1.Aa, t1.Ll, COUNT(*) Rank
FROM #tmp1 t1
INNER JOIN #tmp1 t2
ON t1.Aa >= t2.Aa
AND t2.Ll >= t2.Ll
GROUP BY t1.Aa, t1.Ll
ORDER BY t1.Aa, t1.Ll

SELECT C, A, L
FROM #tmp
ORDER BY C

DROP TABLE #tmp1
DROP TABLE #tmp


<bevanward@gmai l.com> wrote in message
news:1150341168 .351876.35870@i 40g2000cwc.goog legroups.com...[color=blue]
> Hi Mike
>
> Thanks for your comment - C is the field in the target table of the
> insert that I was hoping would increment in the same sequence as the
> sort of Aa, Ll
>
> Cheers
> Bevan
>
> Mike C# wrote:[color=green]
>> Try ORDER BY C
>>
>>
>> <bevanward@gmai l.com> wrote in message
>> news:1150338610 .291820.67350@h 76g2000cwa.goog legroups.com...[color=darkred]
>> > Hi All
>> >
>> > I am finding unexpected results when inserted into a newly created
>> > table that has a field of datatype int identity (1,1).
>> >
>> > Basically the order I sort on when inserting into the table is not
>> > reflected in the order of the values from the identity field.
>> >
>> > Have I been wrong in assuming that it should reflect the order from the
>> > sort?
>> >
>> > The code is ...
>> >
>> > create table tmp (A varchar(50), L float, C int identity(1,1))
>> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>> >
>> > and I don't understand why the values in tmp.C aren't in the order
>> > suggested by the sort.
>> >
>> > Any comments most appreciated
>> > Bevan
>> >[/color][/color]
>[/color]

[bevanward@gmail.com]

Re: out of order identity field - sql2000

Hi Mike

Thanks for your comprehensive response. I had always assumed that this
insert was dependable (sequential and contiguous) ... I guess I need to
go back and re-write anywhere I have existing code that made this
assumption.

Thanks again, most appreciated.

Cheers
Bevan


Mike C# wrote:[color=blue]
> You can't rely on an IDENTITY column to be assigned in a particular order or
> to not have gaps in the sequence, btw. Try assigning a rank value manually
> instead:
>
> CREATE TABLE #tmp (A VARCHAR(50),
> L FLOAT,
> C INT NOT NULL PRIMARY KEY)
>
> CREATE TABLE #tmp1 (Aa VARCHAR(50),
> Ll FLOAT(50),
> PRIMARY KEY (Aa, Ll))
>
> INSERT INTO #tmp1 (Aa, Ll)
> SELECT 'ABC', 123.45
> UNION SELECT 'DEF', 456.12
> UNION SELECT 'XYZ', 999.99
> UNION SELECT 'RST', 023.43
> UNION SELECT 'GHI', 146.56
>
> INSERT INTO #tmp (A, L, C)
> SELECT t1.Aa, t1.Ll, COUNT(*) Rank
> FROM #tmp1 t1
> INNER JOIN #tmp1 t2
> ON t1.Aa >= t2.Aa
> AND t2.Ll >= t2.Ll
> GROUP BY t1.Aa, t1.Ll
> ORDER BY t1.Aa, t1.Ll
>
> SELECT C, A, L
> FROM #tmp
> ORDER BY C
>
> DROP TABLE #tmp1
> DROP TABLE #tmp
>
>
> <bevanward@gmai l.com> wrote in message
> news:1150341168 .351876.35870@i 40g2000cwc.goog legroups.com...[color=green]
> > Hi Mike
> >
> > Thanks for your comment - C is the field in the target table of the
> > insert that I was hoping would increment in the same sequence as the
> > sort of Aa, Ll
> >
> > Cheers
> > Bevan
> >
> > Mike C# wrote:[color=darkred]
> >> Try ORDER BY C
> >>
> >>
> >> <bevanward@gmai l.com> wrote in message
> >> news:1150338610 .291820.67350@h 76g2000cwa.goog legroups.com...
> >> > Hi All
> >> >
> >> > I am finding unexpected results when inserted into a newly created
> >> > table that has a field of datatype int identity (1,1).
> >> >
> >> > Basically the order I sort on when inserting into the table is not
> >> > reflected in the order of the values from the identity field.
> >> >
> >> > Have I been wrong in assuming that it should reflect the order from the
> >> > sort?
> >> >
> >> > The code is ...
> >> >
> >> > create table tmp (A varchar(50), L float, C int identity(1,1))
> >> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> >> >
> >> > and I don't understand why the values in tmp.C aren't in the order
> >> > suggested by the sort.
> >> >
> >> > Any comments most appreciated
> >> > Bevan
> >> >[/color]
> >[/color][/color]

[Mike C#]

Re: out of order identity field - sql2000


<bevanward@gmai l.com> wrote in message
news:1150342841 .281994.283570@ u72g2000cwu.goo glegroups.com.. .[color=blue]
> Hi Mike
>
> Thanks for your comprehensive response. I had always assumed that this
> insert was dependable (sequential and contiguous) ... I guess I need to
> go back and re-write anywhere I have existing code that made this
> assumption.
>
> Thanks again, most appreciated.
>[/color]

No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
of the need for the self-join ranking method.

[bevanward@gmail.com]

Re: out of order identity field - sql2000

Hi Mike

I have read fondly of row_number() in 2005 and can't wait. This has
existed in Oracle for years, from what I understand, and I'm not sure
how we have done without it for so long.

I have re-written the code for this and it doubles the execution time
unfortunately.

Thanks again for taking the time, most appreciated

Bevan


Mike C# wrote:[color=blue]
> <bevanward@gmai l.com> wrote in message
> news:1150342841 .281994.283570@ u72g2000cwu.goo glegroups.com.. .[color=green]
> > Hi Mike
> >
> > Thanks for your comprehensive response. I had always assumed that this
> > insert was dependable (sequential and contiguous) ... I guess I need to
> > go back and re-write anywhere I have existing code that made this
> > assumption.
> >
> > Thanks again, most appreciated.
> >[/color]
>
> No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
> of the need for the self-join ranking method.[/color]

[Erland Sommarskog]

Re: out of order identity field - sql2000

(bevanward@gmai l.com) writes:[color=blue]
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
>
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
>
> Have I been wrong in assuming that it should reflect the order from the
> sort?
>
> The code is ...
>
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.[/color]

Interesting. I get it to work most of the time, and I've even been told
that this is guarranteed to work as expected. Definitely in SQL 2005,
but the source said it was OK for SQL 2000 as well.

However, if you are running on a multi-processor machine (including a
hyper-threaded CPU), try adding OPTION (MAXDOP 1) at the end of the
query.

Note that is you use SELECT INTO instead, there is no guarantee that
the order is the desired.

By the way, what does SELECT @@version say?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Mike C#]

Re: out of order identity field - sql2000


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97E4141 9257FYazorman@1 27.0.0.1...[color=blue]
> (bevanward@gmai l.com) writes:
> Interesting. I get it to work most of the time, and I've even been told
> that this is guarranteed to work as expected. Definitely in SQL 2005,
> but the source said it was OK for SQL 2000 as well.[/color]

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.

To be honest, I don't think the INSERT statement guarantees the order in
which the rows will be inserted, which is a large part of the OP's problem
in this situation. Normally it doesn't matter what order rows get inserted
as long as they get in there. In this case the OP is dynamically assigning
numeric identifiers to each row as they're inserted which makes the order of
insertion important.

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. But SELECT INTO requires the target
table not exist before it's run. I haven't tried it, so can't guarantee it
would work, but hey...

[--CELKO--]

Re: out of order identity field - sql2000

>> I am finding unexpected results when inserted into a newly created table that has a field [sic] of datatype int identity (1,1). <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

Next, by definition -- repeat BY DEFINITION !!! -- IDENTITY is not a
key.
[color=blue][color=green]
>> Have I been wrong in assuming that it should reflect the order from the sort? <<[/color][/color]

Your assumptions are MUCH worse than that! You have missed ALL of the
foundations of RDBMS. As they say in Zen, you must empty your cup to
drink new tea. Please get a good book on RDBMS, take some time and get
it right before you kill someone.

[Tony Rogerson]

Re: out of order identity field - sql2000

This might help from the SQL Engine team blog...

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Its point 4, the identities are calculated in the right order just not
inserted but the insert order shouldn't matter if the identities are
calculated in the correct order.

1.. If you have an ORDER BY in the top-most SELECT block in a query, the
presentation order of the results honor that ORDER BY request
2.. If you have a TOP in the same SELECT block as an ORDER BY, any TOP
computation is performed with respect to that ORDER BY. For example, if
there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows
within a given sort. Note that this does not guarantee that subsequent
operations will somehow retain the sort order of a previous operation. The
query optimizer re-orders operations to find more efficient query plans
3.. Cursors over queries containing ORDER BY in the top-most scope will
navigate in that order
4.. INSERT queries that use SELECT with ORDER BY to populate rows
guarantees how identity values are computed but not the order in which the
rows are inserted
5.. SQL Server 2005 supports a number of new "sequence functions" like
RANK(), ROW_NUMBER() that can be performed in a given order using a OVER
clause with ORDER BY
6.. For backwards compatibility reasons, SQL Server provides support for
assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"Mike C#" <xxx@yyy.com> wrote in message
news:Gylkg.2679 $%12.1269@fe09. lga...[color=blue]
>
> "Erland Sommarskog" <esquel@sommars kog.se> wrote in message
> news:Xns97E4141 9257FYazorman@1 27.0.0.1...[color=green]
>> (bevanward@gmai l.com) writes:
>> Interesting. I get it to work most of the time, and I've even been told
>> that this is guarranteed to work as expected. Definitely in SQL 2005,
>> but the source said it was OK for SQL 2000 as well.[/color]
>
> 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.
>
> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted, which is a large part of the OP's problem
> in this situation. Normally it doesn't matter what order rows get
> inserted as long as they get in there. In this case the OP is dynamically
> assigning numeric identifiers to each row as they're inserted which makes
> the order of insertion important.
>
> 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. But SELECT INTO
> requires the target table not exist before it's run. I haven't tried it,
> so can't guarantee it would work, but hey...
>[/color]

[Erland Sommarskog]

Re: out of order identity field - sql2000

Mike C# (xxx@yyy.com) writes:[color=blue]
> 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.[/color]

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.)
[color=blue]
> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted,[/color]

Correct.
[color=blue]
> which is a large part of the OP's problem in this situation.[/color]

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.
[color=blue]
> 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.[/color]

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.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Mike C#]

Re: out of order identity field - sql2000

"Tony Rogerson" <tonyrogerson@s qlserverfaq.com > wrote in message
news:e6timt$hna $1$8300dec7@new s.demon.co.uk.. .[color=blue]
> This might help from the SQL Engine team blog...
>
> http://blogs.msdn.com/sqltips/archiv...20/441053.aspx
>
> Its point 4, the identities are calculated in the right order just not
> inserted but the insert order shouldn't matter if the identities are
> calculated in the correct order.[/color]

I noticed the blogger states "*most* of the rules are valid for SQL 2000
too", though he doesn't specify which ones.

[Mike C#]

Re: out of order identity field - sql2000


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97E4EF3 786F5CYazorman@ 127.0.0.1...[color=blue]
> Mike C# (xxx@yyy.com) writes:[color=green]
>> 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.[/color]
>
> 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.)[/color]

So we agree on gaps.
[color=blue][color=green]
>> To be honest, I don't think the INSERT statement guarantees the order in
>> which the rows will be inserted,[/color]
>
> Correct.[/color]

And insert statement order guarantees.
[color=blue][color=green]
>> which is a large part of the OP's problem in this situation.[/color]
>
> 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.[/color]

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.
[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

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."