在SQL Server 2005中插入行后的主键值 [英] Primary key value after insertion of row in SQL Server 2005

问题描述

在SQL Server 2005中，我正在使用存储过程在表中插入一行，并且我想在插入该行后立即获取新的主键值.我正在使用以下方法在插入行后获取主键值

In SQL Server 2005 I am inserting a row into a table using a stored procedure and I want to fetch the new primary key value just after inserting that row. I am using following approach to get primary key value after insertion row

Create Proc Sp_Test
@testEmail varchar(20)=null,-- Should be Unique
@testName varchar(20)=null -- Should be Unique
as

begin

insert into tableTest  (testUserEmail,testUserName)values (@testValue,@testName)

select MAX(ID) from tableTest --ID is Primary Key 

-或

select ID from tableTest  where  testUserEmail =@testValue and testUserName = @testName

-或

select  SCOPE_IDENTITY() as ID

end

请建议我哪种方法更好地执行所描述的任务.

Please suggest me which approach is better to perform described task.

推荐答案

通过所有方式-如果您的ID列是INT IDENTITY-仅 会使用SCOPE_IDENTITY()正确的结果！

By all means - use the SCOPE_IDENTITY() if your ID column is an INT IDENTITY - only that will give you the correct results!

如果您有多个客户端几乎同时插入行，则使用MAX(ID)的第一种方法将彻底失败-您将得到错误的结果.不要使用它！

The first approach with the MAX(ID) will fail terribly if you have multiple clients inserting rows almost at the same time - you'll get false results back. Don't use that!

如果存在另一个具有相同电子邮件和名称值的条目，则第三种方法可能会失败.

The third approach might fail if another entry with the same values for E-Mail and name already exists.

此外，请注意:您切勿使用sp_作为前缀！这是Microsoft保留的前缀，在性能方面有缺点-请使用其他功能.

Also, as a side-note: you should never use sp_ as your prefix! This is a Microsoft-reserved prefix and has downsides in terms of performance - use something else.

这篇关于在SQL Server 2005中插入行后的主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！