question about retrieving identity value

hi,
according to the SQL Server Books Online, there're 3 system functions return
last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
...
BEGIN TRAN
...
INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
...
COMMIT TRAN
...
I think using IDENT_CURRENT may be a problem in multiuser environment,
because it returns the last identity value generated in any session. But he
said it's ok, since these statements are put in a transaction and SQL Server
will lock the table. Is it true?Hi
He is wrong. SQL Server may not lock the whole table, so it is possible that
2 processes can insert into the same table, at the same time.
SCOPE_IDENTITY is the correct one to use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:72F8BDEC-FA0F-4880-B1E7-FFBDB8102955@.microsoft.com...
> hi,
> according to the SQL Server Books Online, there're 3 system functions
> return
> last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
> SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
> ...
> BEGIN TRAN
> ...
> INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
> SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
> ...
> COMMIT TRAN
> ...
> I think using IDENT_CURRENT may be a problem in multiuser environment,
> because it returns the last identity value generated in any session. But
> he
> said it's ok, since these statements are put in a transaction and SQL
> Server
> will lock the table. Is it true?