I have a table EugeneTest(id_num, fname, minit, lname)
where field "id_num" is type IDENTITY and has UNIQUE constraint
Let's say 2 user execute this code at the same time:
DECLARE @return integer
use EugeneTest
INSERT employees
( fname, minit, lname)
VALUES
( 'Eugene3', 'F', 'Josephs')
SET @return = @@IDENTITY
Is is not possible to get duplicated value in id_num column becuase of
UNIQUE
constraint, but is it possible the both user get the same @@IDENTITY
number
( for example, if @@IDENTITY is evaluated and kept somewhere in the
buffer before the data actually get written to the disk )
Thanks, Eugene 5 3983
Eugene wrote: I have a table EugeneTest(id_num, fname, minit, lname) where field "id_num" is type IDENTITY and has UNIQUE constraint
Let's say 2 user execute this code at the same time:
DECLARE @return integer use EugeneTest INSERT employees ( fname, minit, lname) VALUES ( 'Eugene3', 'F', 'Josephs') SET @return = @@IDENTITY
Is is not possible to get duplicated value in id_num column becuase
of UNIQUE constraint, but is it possible the both user get the same @@IDENTITY number ( for example, if @@IDENTITY is evaluated and kept somewhere in the buffer before the data actually get written to the disk )
Thanks, Eugene
The problem with @@IDENTITY is that, whilst it holds the last identity
value inserted into any table, it is modified by ALL user sessions
(i.e. it has global scope). Thus, if 2 concurrent users execute the
code in your example above, it is possible for User1 to actually
return the value generated by User2 if user2 is running a slpit-second
behind User1.
To get around this problem, you should use SCOPE_IDENTITY() instead.
This also returns the most recent indetity value inserted into any
table, BUT ONLY FOR THAT SESSION (i.e. local scope, hence the name).
This means that you will not pick up an identity value generated by
another user session.
On 4 Feb 2005 09:23:17 -0800, "Phil" <ph********@btopenworld.com> wrote: Eugene wrote: I have a table EugeneTest(id_num, fname, minit, lname) where field "id_num" is type IDENTITY and has UNIQUE constraint
Let's say 2 user execute this code at the same time:
DECLARE @return integer use EugeneTest INSERT employees ( fname, minit, lname) VALUES ( 'Eugene3', 'F', 'Josephs') SET @return = @@IDENTITY
Is is not possible to get duplicated value in id_num column becuaseof UNIQUE constraint, but is it possible the both user get the same @@IDENTITY number ( for example, if @@IDENTITY is evaluated and kept somewhere in the buffer before the data actually get written to the disk )
Thanks, Eugene
The problem with @@IDENTITY is that, whilst it holds the last identity value inserted into any table, it is modified by ALL user sessions (i.e. it has global scope). Thus, if 2 concurrent users execute the code in your example above, it is possible for User1 to actually return the value generated by User2 if user2 is running a slpit-second behind User1.
Right on 1 - wrong on 2. @@IDENTITY is only affected by statements executed
in the same connection, even though @@ normally means Global.
From the T-SQL Help: "@@IDENTITY and SCOPE_IDENTITY will return the last
identity value generated in any table in the current session. However,
SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is
not limited to a specific scope." To get around this problem, you should use SCOPE_IDENTITY() instead. This also returns the most recent indetity value inserted into any table, BUT ONLY FOR THAT SESSION (i.e. local scope, hence the name). This means that you will not pick up an identity value generated by another user session.
@@IDENTITY won't pick up a value generated by another user session, but it can
pick up a value generated by a trigger action on another table that was
triggered by the action you explicitly executed.
On 4 Feb 2005 09:23:17 -0800, "Phil" <ph********@btopenworld.com> wrote: Eugene wrote: I have a table EugeneTest(id_num, fname, minit, lname) where field "id_num" is type IDENTITY and has UNIQUE constraint
Let's say 2 user execute this code at the same time:
DECLARE @return integer use EugeneTest INSERT employees ( fname, minit, lname) VALUES ( 'Eugene3', 'F', 'Josephs') SET @return = @@IDENTITY
Is is not possible to get duplicated value in id_num column becuase of UNIQUE constraint, but is it possible the both user get the same @@IDENTITY number ( for example, if @@IDENTITY is evaluated and kept somewhere in the buffer before the data actually get written to the disk )
Thanks, Eugene
As I understand it, the next value for an identity column is incremented
immediately, and not rolled back in order to prevent just that problem. Thus,
IDENTITY values are not guaranteed to be consecutive, they are guaranteed
never to produce duplicates, even if 2 users have insert transactions
overlapping in time.
Looks like I was being sloppy and using session and connection
interchangeably. I must confess I thought I'd finally got the hang of
identity value behaviour - clearly not! :-)
Still, 99% of the time all I need to know is that code at our place
needs to use scope_identity, and not @@identity or ident_current (which
is another topic altogether!)
On 4 Feb 2005 10:03:56 -0800, "Phil" <ph********@btopenworld.com> wrote: Looks like I was being sloppy and using session and connection interchangeably. I must confess I thought I'd finally got the hang of identity value behaviour - clearly not! :-)
Still, 99% of the time all I need to know is that code at our place needs to use scope_identity, and not @@identity or ident_current (which
I agree with that completely :)
is another topic altogether!) 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...
|
by: Andy |
last post by:
Hi All!
We are doing new development for SQL Server 2000 and also moving from
SQL 7.0 to SQL Server 2000.
What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?
Please,...
|
by: brent.ryan |
last post by:
How do I get the next int value for a column before I do an insert in
MY SQL Server 2000? I'm currently using Oracle sequence and doing
something like:
select seq.nextval from dual;
Then I...
|
by: Jerry Camel |
last post by:
I need to be able to generate unique names for files. I was considering
that hash alogorithms, but if I had two files with the same name, I'd get
the same hash. I am collecting and storing files...
|
by: pb648174 |
last post by:
In a multi-user environment, I would like to get a list of Ids
generated, similar to:
declare @LastId int
select @LastId = Max(Id) From TableMania
INSERT INTO TableMania (ColumnA, ColumnB)...
|
by: Dave A |
last post by:
Has anyone written or using an identity mapper design pattern in an
enterprise framework?
The identity mapper pattern is described in Martin Fowler's Patterns of
Enterprise Application...
|
by: Brad Eck |
last post by:
In Access, newID returns a unique for the table. In SQL Server, newid()
returns a GUID - unique in the world. I do not need or desire that
complexity. Is there a way to get a simple unique int on...
|
by: Dabbler |
last post by:
I'm using an SQLCommand to insert row using a text command. Is there a way to
return the IDENTITY key value after the insert ?
Thanks much!
|
by: cedric.louyot |
last post by:
Hi,
I've written a schema that looks like :
<xs:schema>
<xs:complexType name="myType">
<xs:sequence>
<xs:element name="e1" type="T1" maxOccurs="unbounded"/>
<xs:element name="e2"...
|
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...
|
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...
|
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...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |