473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

out of order identity field - sql2000

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

Jun 15 '06 #1
13 4402
Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.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

Jun 15 '06 #2
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:
Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.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


Jun 15 '06 #3
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
<be*******@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...
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:
Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.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
>

Jun 15 '06 #4
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:
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
<be*******@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...
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:
Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.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
>


Jun 15 '06 #5

<be*******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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.


No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
of the need for the self-join ranking method.
Jun 15 '06 #6
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:
<be*******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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.


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


Jun 15 '06 #7
(be*******@gmail.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 15 '06 #8

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
(be*******@gmail.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.


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...
Jun 15 '06 #9
>> 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.
Have I been wrong in assuming that it should reflect the order from the sort? <<


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.

Jun 15 '06 #10
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.

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#" <xx*@yyy.com> wrote in message
news:Gy*****************@fe09.lga...

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
(be*******@gmail.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.


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

Jun 16 '06 #11
Mike C# (xx*@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.)
To be honest, I don't think the INSERT statement guarantees the order in
which the rows will be inserted,
Correct.
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.
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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 16 '06 #12
"Tony Rogerson" <to**********@sqlserverfaq.com> wrote in message
news:e6*******************@news.demon.co.uk...
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.


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

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Mike C# (xx*@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."

Jun 16 '06 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Benny | last post by:
Dear All, Suppose in the program a record is added to a table whose primary key is a identity field. If I really want to get the lastest value for that field after the insertion, is it the best...
9
by: Rathtap | last post by:
I want to use the Identity field (increment 1,1) as a primary key and have a unique constraint on my other field which is of type char. I am worried that related data in other tables may lose...
5
by: DBA | last post by:
I have an identity field on a table in SQL Server. The identity seed is 1 and the identity increment is 1. If I remove a record from this table, the identity sequence is broken. For example: ...
3
by: Poul Møller Hansen | last post by:
Hi, I need an auto incrementing field that will contain values like N000001, N000002, N000003 etc. I think the way is to use the value from an identity field in a stored procedure that is...
3
by: JIM.H. | last post by:
Hello, Let say myTable has two fields: ID and Name. ID is an identity field and increased sequentially by 1 each time a record is entered into table. dr=”MayName”;...
1
by: Craig | last post by:
In ASP.NET 2.0 and the formview control how do you get the value of the identity field of a newly inserted record? In ASP.NET 1.1 after the new record was saved you just said intNewIdentityID =...
2
by: siddu57 | last post by:
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders...
0
by: snacampbell | last post by:
My project is at a standstill because I cannot get the SQL code below to run without error. I'm using Visual Basic 6.0, referencing dao360.dll (not referencing dao351.dll), and referencing MDAC 2.8...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.