@@identity 3

[HLPCH]

Select @@identity as orderid from orders.

I tried executing this and no value came up. I tried it on the Northwind .Orders.How could i get the last generated number?

Thanks

 

[DotNetGnat]

how abt this..

Select scope_identity() as orderid from orders

what database are you using?

-DNG

 

[amrita418]

OR you could use

select ident_current('orders')

check books online for more information on this.

Regards,
AA

 

[vongrunt]

> Select @@identity|scope_identity() as orderid from orders

This returns as many rows as you have in table Orders :>


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

so vongrunt,

either scope_identity() or @@identity wont return the ID of the last entered record...

can you explain a little more. Because. If suppose i have 50 records in my table and i enter a new record i get 51 with either of these...now if delete one of the records and enter a new record again i will still get 52..but the total number of records i have in my table is 51...

please explain...

-DNG

 

[HLPCH]

Select ident_current('orders')

The above gave me the last generated number. Thanks. I tried this on the orders table located on the Northwind database.

 

[amrita418]

I am not vongrunt but here is what he was trying to say:

select scope_identity() would give one record while
the statement you used
select scope_identity() from orders would return the same value as many times as the number of rows in the orders table.

coming to your second question:
Deleting a row does not reset the identity field. It will still load the next value. Deletion causes breaks in the identity values.

One more thing worth nothing is scope_identity() returns the identity value inserted in the current session which might not be accurate.

Using ident_current('table') will always ensure you get the latest value inserted for the identity column in on the table.

Regards,
AA

 

[DotNetGnat]

still did not understand what you meant by:

select scope_identity() would give one record while
the statement you used
select scope_identity() from orders would return the same value as many times as the number of rows in the orders table.

-DNG

 

[amrita418]

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session.

Like any other function (example getdate()) when used with a tablename (in the from clause) it repeats the same value for every row in the table.

Regards,
AA

 

[mrdenny]

They don't return the last identity value in any table in the current session. They return the last identity generated within the session. Now there are differences between the two.

If you do an insert into a table which has a trigger and that trigger inserts data into a table which also has an identity field @@IDENTITY will return the identity value from the table that was inserted into by the trigger. scope_identity() doesn't have this issue. In the same situation scope_identity() will return the identity value from the parent table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DotNetGnat]

ok..things are getting little murky for me...

-DNG

 

[vongrunt]

can you explain a little more. Because. If suppose i have 50 records in my table and i enter a new record i get 51 with either of these...

I was referring to another issue. This:

SELECT @@identity|scope_identity() as orderid from orders

... would return 50 rows with same value. Try that in Query Analyzer. Perhaps that value is OK (and client probably reads only one via ADO or something) but 50 rows instead of only one is totally unnecessary overhead.

General recommended method for identity retrieval is SCOPE_IDENTITY, shortly or immediately after INSERT.

@@IDENTITY may cause some ugly headaches if affected table has triggers FOR INSERT and trigger fills another table with identity column. Sounds kinda exaggerated, but this scenario is a real possibility (audit purposes).

IDENT_CURRENT() is not recommended for high-traffic databases. It is not limited to any scope/session, so another process/user may INSERT another row shortly before SELECT and after your INSERT. Of course this can be solved with some locking/isolation hints... but then concurrency gets screwed up for something that SCOPE_IDENTITY already does.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

I agree with you mrdenny. I was pointing the commonalities between scope_identity() + @@identity verses ident_current hence posted that line only.

Maybe I should have pasted all of it from books online.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Regards,
AA

 

[DotNetGnat]

thanks for the explanation guys...stars for you...

-DNG