quote:

---

Originally posted by tech1

What is the best way of obtaining the last generated ID or a new ID for a bigint column?

I want to make sure there wont be any "clashes" across all sessions.

I know we have Scope_Identity and Ident_Current('tableName') but which is better?

I do believe Ident_Current is better but would appreciate your thoughts. I just need the next ID available so I can insert it into related tables.

Thanks

---

quote:

---

Originally posted by tech1
I do believe Ident_Current is better

Thanks

---

quote:

---

Originally posted by tech1

I do believe Ident_Current is better but would appreciate your thoughts.

---

quote:

---

Originally posted by tech1

What does the output parameter have to do with anything in this case? :-)

---

quote:

---

so if Ident_current is "poor" (from what I was reading on MSDN, it seemed the right choice because it takes into consideration all sessions and ALL scopes, rather than just the current session and current scope), then what am I left with? SCOPE_IDENTITY?

---

-- setup code, run first

use tempDB;
go

create table TestingIdentity (
ID int identity,
UserName varchar(10)
)

CREATE TABLE [dbo].[Results](
	[IdThatThisSessionInserted] [int] NULL,
	[IdentCurrent] [numeric](38, 0) NULL,
	[SessionName] [varchar](10) NULL
)

go

SET NOCOUNT ON
go

WAITFOR TIME '13:37' -- set to a couple minutes time so that all 3 start together.
go

INSERT INTO TestingIdentity (UserName) VALUES ('Session 1')

-- This will get the latest row that this session inserted
INSERT INTO dbo.Results
        ( IdThatThisSessionInserted ,
          IdentCurrent ,
          SessionName
        )
SELECT TOP 1 ID AS IdThatThisSessionInserted, IDENT_CURRENT('TestingIdentity') AS IdentCurrent, UserName AS SessionName
FROM TestingIdentity 
WHERE UserName = 'Session 1' 
ORDER BY ID DESC 

GO 200

SET NOCOUNT ON
go

WAITFOR TIME '13:37'-- set to the same time as window 1
go

INSERT INTO TestingIdentity (UserName) VALUES ('Session 2')

-- This will get the latest row that this session inserted
INSERT INTO dbo.Results
        ( IdThatThisSessionInserted ,
          IdentCurrent ,
          SessionName
        )
SELECT TOP 1 ID AS IdThatThisSessionInserted, IDENT_CURRENT('TestingIdentity') AS IdentCurrent, UserName AS SessionName
FROM TestingIdentity 
WHERE UserName = 'Session 2' 
ORDER BY ID DESC 

GO 200

SET NOCOUNT ON
go

WAITFOR TIME '13:37' -- set to same time as window 1
go

INSERT INTO TestingIdentity (UserName) VALUES ('Session 3')

-- This will get the latest row that this session inserted
INSERT INTO dbo.Results
        ( IdThatThisSessionInserted ,
          IdentCurrent ,
          SessionName
        )
SELECT TOP 1 ID AS IdThatThisSessionInserted, IDENT_CURRENT('TestingIdentity') AS IdentCurrent, UserName AS SessionName
FROM TestingIdentity 
WHERE UserName = 'Session 3' 
ORDER BY ID DESC 

GO 200

SELECT * FROM dbo.Results AS r WHERE IdThatThisSessionInserted != IdentCurrent

quote:

---

@@identity if you have no triggers on the tables, Scope identity or the output clause.

---

quote:

---

Originally posted by tech1

yes thats exactly what the question is :-)
should I use Scope_Identity, @@Identity, Ident_current?

---

quote:

---

Originally posted by X002548

what am I? Chopped Liver?

mmmmmmmm



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

---

quote:

---

Originally posted by tech1

Hmm. thanks Kristen

---