@@Identity

  • Thread starter Thread starter Scott M.
  • Start date Start date
Michael,

I have no idea why you are insulting someone you don't know, who has never
said anything to you. I think that says something about you.

I also don't know why you are answering a question that has been answered
already. I've gotten my answer, like 6 posts ago in this thread.

I also think that you didn't really read this thread before you decided to
stop beating your children and take whatever is pissing you off out on me.
Clearly, my OP was understood as the first 2 replies answered my question.

So, I'll say the same thing to you that I said to Roy. Perhaps you should
not reply to posts that YOU don't understand?
 
Michael,

This is from the MSDN Library - April 2004

----------------------------------------------------------------------------
------
How Does SQL Server Manage Identity Values?
When you add a row to a table with an Identity column you don't include a
value for the Identity column because the DBMS server automatically adds an
increment (usually 1) to the highest Identity value in the table and uses
this value for the new row's Identity value. If you're using SQL Server, the
value is also saved in a connection-global variable: @@IDENTITY. (GUID
identifiers are supported in SQL Server and other full-featured DBMS
systems, but not Access/JET.) That's fine, but what happens when a row is
deleted? Is that row's Identity value forever orphaned? Yep, unless you
reseed the Identity (DBCC CHECKIDENT), deleted Identity values are lost.
Identity values are also orphaned when a transaction is rolled back. This
means that when you use Identity columns, you'll need to be prepared for
gaps in the series. It also means that eventually, the Integer you're using
will overflow, so it's important that you use an Integer large enough for
your needs-now, and well into the future. An "integer" in SQL Server can
identify about 2 billion rows while a "bigint" can identify
9,223,372,036,854,775,807 rows (that's a lot of rows). However, a "smallint"
can identify only about 32 thousand rows. I actually had someone complain
that he ran out of Identity values-they had used a "tinyint" that ran out
after 255 rows. Sigh. I'm not going to delve into techniques to recover
orphaned identity values-it's tough to do and over the years I've found it's
not worth the trouble. Just make sure to define an integer wide enough to
get you through the next century or corporate take-over when they re-write
everything anyway.

----------------------------------------------------------------------------
------
SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same
scope. A scope is a module -- a stored procedure, trigger, function, or
batch. Thus, two statements are in the same scope if they are in the same
stored procedure, function, or batch.

----------------------------------------------------------------------------
------

IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the value generated for a specific
table in any session and any scope. For more information, see IDENT_CURRENT.

----------------------------------------------------------------------------
------


You have 3 ways of getting identities, @@IDENTITY, SCOPE_IDENTITY and
IDENT_CURRENT.

Notice that the @@IDENTITY is a connection-global variable.
So if 2 or more users connect to the database they'd have there own
@@IDENTITY global variable.
So you can do an INSERT statement then next a SELECT @@IDENTITY to get your
own @@IDENTITY, not someone else's @@IDENTITY.

The problem you're describing is if you use IDENT_CURRENT. That is for the
current table. So if 2 or more people insert records into a database and
used IDENT_CURRENT to retrieve an identity value, then the last person who
inserted, that's the value that you'd be getting which isn't going to be
100% your identity.

A good example that they give in the MSDN for SCOPE_IDENTITY is this:
TABLE1 - has a identity
TABLE2 - has a identity and a trigger that'll insert a record into TABLE1.

If I insert into TABLE2, you'd use SCOPE_IDENTITY to get your identity from
TABLE2 and @@IDENTITY to get your identity from TABLE1.

Hope this clears up how identities work.
 
Actually I was thinking about this.
SCOPE_IDENTITY is better to use

So the problem you described wasn't because people are using IDENT_CURRENT
to get the identity value, it's because someone added some code to insert
into another table that had an identity in it.

Just wanted to correct myself before I get spammed.

But just to point out, @@IDENTITY isn't the real problem, it's when someone
adds code to insert into a table that has an identity value in it. That's
why Microsoft has SCOPE_IDENTITY, my guess.
 
Back
Top