identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

identity

Author

Topic

pentahari
Starting Member

26 Posts

Posted - 2008-10-01 : 05:11:59

how to get the identity value before insert.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-01 : 05:14:09

before ???

Not possible. Unless, you are going to do a select top 1 * and order by date desc and then increment it, ( This will only work, if there are no other transactions to that table )

But after, use scope_identity() function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 05:14:17

You can't.

You can however get last used identity value with
SELECT IDENT_CURRENT('Table1')

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-01 : 05:17:07

quote:
Originally posted by Peso

You can't.

You can however get last used identity value with
SELECT IDENT_CURRENT('Table1')

E 12°55'05.63"
N 56°04'39.26"

Hi Peter, the scope_identity() is more reliable.

pentahari
Starting Member

26 Posts

Posted - 2008-10-01 : 05:20:56

quote:
Originally posted by Peso

You can't.

You can however get last used identity value with
SELECT IDENT_CURRENT('Table1')

E 12°55'05.63"
N 56°04'39.26"

How to use store procedure for get identity?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 05:25:59

quote:
Originally posted by afrika

Hi Peter, the scope_identity() is more reliable.

scope_identity() only works if there has been in insert in any identity column in your scope.
You can query any table anytime for last used identity value with my suggestion.

CREATE PROC uspTest
AS

SET NOCOUNT ON

SELECT scope_identity() AS afrika -- returns NULL
SELECT IDENT_CURRENT('Table1') AS Peso -- returns 154478

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-10-01 : 05:28:45

You don't use a stored proc to find the identity value,

what exactly are you trying to do, are you trying to find the identity value while doing an insert inside a stored procedure. If that is the case you can use either @@identity or scope_identity() but would recommend scope_identity() more reliable and will return the identity value for that transaction you carrying out by using the @@identity function this can be interefered with by other transactions working

pentahari
Starting Member

26 Posts

Posted - 2008-10-01 : 05:29:07

quote:
Originally posted by Peso

quote:
Originally posted by afrika

Hi Peter, the scope_identity() is more reliable.

scope_identity() only works if there has been in insert in any identity column in your scope.
You can query any table anytime for last used identity value with my suggestion.
CREATE PROC uspTest
AS

SET NOCOUNT ON

SELECT scope_identity() AS afrika -- returns NULL
SELECT IDENT_CURRENT('Table1') AS Peso -- returns 154478

E 12°55'05.63"
N 56°04'39.26"

Thanks for your reply.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 05:31:20

quote:
Originally posted by NeilG

You don't use a stored proc to find the identity value,

It was just for demonstrating purposes.

I forgot to write that @@identity and scope_identity() works the same way, in respect to getting CURRENTLY last inserted identity value.

Open a new query window, connect to an old database not used for ages and type

SELECT IDENT_CURRENT('Table')

and you will get last used identity value even if no value has been inserted in your scope.
@@identity and scope_identity() will both return NULL.

@@identity and scope_identity() are both AFTER values.
IDENT_CURRENT() can be used for both BEFORE and AFTER value.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 05:33:19

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 05:39:06

Here is a simple demonstration

CREATE TABLE	tblTest
		(
			RowID INT IDENTITY(1, 1)
		)
GO
INSERT	tblTest
DEFAULT	VALUES
GO
CREATE TABLE	tblDummy
		(
			RecID INT IDENTITY(10000, 1)
		)
GO
INSERT	tblDummy
DEFAULT	VALUES
GO
CREATE PROCEDURE uspTest
AS

SELECT	'Before INSERT' AS Task,
	IDENT_CURRENT('tblTest') AS LastUsedRowID,
	@@IDENTITY AS [@@IDENTITY],
	SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]

INSERT	tblTest
DEFAULT VALUES

SELECT	'After tblTest INSERT' AS Task,
	IDENT_CURRENT('tblTest') AS LastUsedRowID,
	@@IDENTITY AS [@@IDENTITY],
	SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]

INSERT	tblDummy
DEFAULT VALUES

SELECT	'After tblDummy INSERT' AS Task,
	IDENT_CURRENT('tblTest') AS LastUsedRowID,
	@@IDENTITY AS [@@IDENTITY],
	SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
GO
EXEC uspTest
GO
DROP TABLE	tblTest,
		tblDummy
DROP PROCEDURE	uspTest

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-01 : 05:40:39

quote:
Originally posted by pentahari

quote:
Originally posted by Peso

You can't.

You can however get last used identity value with
SELECT IDENT_CURRENT('Table1')

E 12°55'05.63"
N 56°04'39.26"

How to use store procedure for get identity?

Yeah, I agree with you Peso, but he asked.

First Post.

quote:

how to get the identity value before insert.

And Second quote above.

quote:

How to use store procedure for get identity?

For this, scope_identity() works better

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-01 : 05:43:02

quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

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.

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.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Books online.

I believe He wants the ID from an inserted table ???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 05:44:28

quote:
how to get the identity value before insert?

We don't know enough about the application he is building. Maybe there is only one user?
Nevertheless,

SELECT IDENT_CURRENT('Table1') + IDENT_INCR('Table1')

will, in most cases, give you the next identity values for the table.
Said without warranty because another process or user can use that identity value before you do.

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-01 : 05:53:53

quote:
Originally posted by Peso

quote:
Originally posted by afrika

Hi Peter, the scope_identity() is more reliable.

scope_identity() only works if there has been in insert in any identity column in your scope.
You can query any table anytime for last used identity value with my suggestion.
CREATE PROC uspTest
AS

SET NOCOUNT ON

SELECT scope_identity() AS afrika -- returns NULL
SELECT IDENT_CURRENT('Table1') AS Peso -- returns 154478

VERY FUNNY

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-01 : 08:46:31

One thing to keep in mind about IDENT_CURRENT. It doesn't see IDENTITY_INSERT..ON values:

add the following to Peter's example:


set identity_insert tblDummy ON
insert tblDummy (recid) values (500)
set identity_insert tblDummy Off

SELECT	'After tblDummy INSERT with Identity_Insert' AS Task,
	IDENT_CURRENT('tblTest') AS LastUsedRowID,
	@@IDENTITY AS [@@IDENTITY],
	SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]

Returns This:
Task                                       LastUsedRowID                           @@IDENTITY                              SCOPE_IDENTITY()
------------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------
After tblDummy INSERT with Identity_Insert 2                                       500                                     500

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 08:55:28

Good spot!

E 12°55'05.63"
N 56°04'39.26"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-01 : 08:59:34