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 13 4437
Try ORDER BY C
<be*******@gmai l.com> wrote in message
news:11******** *************@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
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*******@gmai l.com> wrote in message news:11******** *************@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
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*******@gmai l.com> wrote in message
news:11******** *************@i 40g2000cwc.goog legroups.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*******@gmai l.com> wrote in message news:11******** *************@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 >
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*******@gmai l.com> wrote in message news:11******** *************@i 40g2000cwc.goog legroups.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*******@gmai l.com> wrote in message news:11******** *************@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 >
<be*******@gmai l.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.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.
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*******@gmai l.com> wrote in message news:11******** **************@ u72g2000cwu.goo glegroups.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.
(be*******@gmai l.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****@sommarsk og.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
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1... (be*******@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.
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...
>> 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 way to use
IDENT_CURRENT() to obtain this value?
Thanks for your kind attention
Yours faithfully,
|
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 referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.
|
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:
Table contents prior to record delete:
Fname(varchar), Lname (varchar), row_id (identity)
--------------------------------------------------
Smith, Jane, 1
Smith, Tom, 2
Jones, Mark 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 triggered at insert.
I can't see that it can be made in pure SQL, but Java is not a problem.
Any of you that can tell me the way of doing it ?
|
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ā€¯;
dataSet11.Tables.Rows.Add(dr);
sqlDataAdapter1.Update(dataSet11, myTable);
| |
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 = Dataset.tblMtTable.IndentityID
Simple but can't seem to do it in the formview.
Thanks
|
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 table:
INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)
The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.
I'm trying...
|
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 in order to switch to Jet 4.0.
The code below conforms to the Microsoft documentation for an Identity field, but it causes an error "Syntax error in field definition." I get a different error if I omit the
(1,1) (seed, increment), and I still...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |