如何SQLServer中准确的获得标识值

SQL Server有三种不同的函数可以用来获得含有标识列的表里最后生成的标识值：

创新互联是一家集网站建设,札达企业网站建设,札达品牌网站建设,网站定制,札达网站建设报价,网络营销,网络优化,札达网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

 
 
 
    
  
  
  
  1. @@IDENTITY   
    2. 
  
  
  
  2. SCOPE_IDENTITY()   
    3. 
  
  
  
  3. IDENT_CURRENT('数据表名') 
    4.

以上三个函数虽然都可以返回数据库引擎最后生成插入标识列的值，但是根据插入行的来源(例如：存储过程或触发器)以及插入该行的连接不同，这三个函数在功能上也有所不同。

@@IDENTITY函数可以返回所有范围内当前连接插入最后所生成的标识值(包括任何调用的存储过程和触发器)。这个函数不止可以适用于表。函数返回的值是最后表插入行生成的标识值。

SCOPE_IDENTITY()函数跟上一个函数几乎是一摸一样的，不同的地方：即前者返回的值只限于当前范围(即执行中的存储过程)。

最后是IDENT_CURRENT函数，它可以用于所有范围和所有连接，获得最后生成的表标识值。跟前面两个函数不同的是，这个函数只用于表，并且使用[数据表名]作为一个参数。

我们可以举实例来演示上述函数是如何运作的。

首先，我们创建两个简单的例表：一个代表客户表，一个代表审计表。创建审计表的目的是为了跟踪数据库里插入和删除信息的所有记录。

以下是引用片段：

 
 
 
    
  
  
  
  1. CREATE TABLE dbo.customer   
    2. 
  
  
  
  2. (customerid INT IDENTITY(1,1) PRIMARY KEY)   
    3. 
  
  
  
  3. GO   
    4. 
  
  
  
  4. CREATE TABLE dbo.auditlog   
    5. 
  
  
  
  5. (auditlogid INT IDENTITY(1,1) PRIMARY KEY,   
    6. 
  
  
  
  6. customerid INT, action CHAR(1),   
    7. 
  
  
  
  7. changedate datetime DEFAULT GETDATE())   
    8. 
  
  
  
  8. GO 
    9.

然后，我们还要创建一个存储过程和一个辅助触发器，这个存储过程将在数据库表里插入新的客户行，并返回生成的标识值，而触发器则会向审计表插入行：

以下是引用片段：

 
 
 
    
  
  
  
  1. CREATE PROCEDURE dbo.p_InsertCustomer @customerid INT output   
    2. 
  
  
  
  2. AS   
    3. 
  
  
  
  3. SET nocount ON   
    4. 
  
  
  
  4. INSERT INTO dbo.customer DEFAULT VALUES   
    5. 
  
  
  
  5. SELECT @customerid = @@identity   
    6. 
  
  
  
  6. GO   
    7. 
  
  
  
  7. CREATE TRIGGER dbo.tr_customer_log ON dbo.customer   
    8. 
  
  
  
  8. FOR INSERT, DELETE   
    9. 
  
  
  
  9. AS   
    10. 
  
  
  
  10. IF EXISTS (SELECT 'x' FROM inserted)   
    11. 
  
  
  
  11. INSERT INTO dbo.auditlog (customerid, action)   
    12. 
  
  
  
  12. SELECT customerid, 'I'   
    13. 
  
  
  
  13. FROM inserted   
    14. 
  
  
  
  14. ELSE   
    15. 
  
  
  
  15. IF EXISTS (SELECT 'x' FROM deleted)   
    16. 
  
  
  
  16. INSERT INTO dbo.auditlog (customerid, action)   
    17. 
  
  
  
  17. SELECT customerid, 'D'   
    18. 
  
  
  
  18. FROM deleted   
    19. 
  
  
  
  19. GO 
    20.

现在我们可以执行程序，创建客户表的第一行了，以下是引用片段：

 
 
 
    
  
  
  
  1. DECLARE @customerid INT   
    2. 
  
  
  
  2. EXEC dbo.p_InsertCustomer @customerid output   
    3. 
  
  
  
  3. SELECT @customerid AS customerid 
    4.

#p#

执行后返回了我们需要的第一个客户的值，并记录了插入审计表的条目。到目前为止，数据显示没有任何问题。

假设由于先前沟通出现了偏差，一个客户服务代表现在需要从数据库里删除掉这个新增的客户。我们现在就来把新插入的客户行删除掉：

以下是引用片段：

 
 
 
    
  
  
  
  1. DELETE FROM dbo.customer WHERE customerid = 1 
    2.

现在，客户工作表为空表，而审计工作表里则有两行——第一行是记录第一次插入行，第二行是记录删除客户记录。

现在我们再往数据库里增加第二个客户信息并检测一下获得的标识值：

以下是引用片段：

 
 
 
    
  
  
  
  1. DECLARE @customerid INT   
    2. 
  
  
  
  2. EXEC dbo.p_InsertCustomer @customerid output   
    3. 
  
  
  
  3. SELECT @customerid AS customerid 
    4.

哇!看看出现了什么情况!如果我们现在再看客户工作表，就会发现虽然创建了客户2，但是我们的程序返回的标识值为3!到底出了什么问题呢?回想一下，前面讲过@@IDENTITY函数的作用范围，它会返回主程序调用的任何存储过程或触动任何触发器最后生成的标识值，取决于哪一个在函数被调用前最后生成标识值。在我们的例子里，初始范围是p_InsertCustomer，然后是触发器用来记录插入条目的tr_customer_log。因此我们返回获得的标识值是审计工作表里触发器插入生成的标识值，而不是我们想要的客户工作表里的生成的标识值。

在SQL Server 2000之前的版本，@@IDENTITY函数是获得标识值的唯一方法。由于会出现这样的存储过程/触发器问题，SQL Server开发团队在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT这两个函数来解决这个问题。所以在旧的SQL Server版本里，要解决这个问题比较麻烦。如果是SQL Server6.5版本，我建议可以去掉标识列，然后创建一个可以包含下一个需要使用的值的辅助表，可以达到标识列的作用效果。不过这个办法也不是什么高明的办法。

现在我们来修改一下存储过程来使用SCOPE_IDENTITY()函数，并重新执行程序来添加第三个客户条目：

以下是引用片段：

 
 
 
    
  
  
  
  1. ALTER PROCEDURE dbo.p_InsertCustomer @customerid INT output   
    2. 
  
  
  
  2. AS   
    3. 
  
  
  
  3. SET nocount ON   
    4. 
  
  
  
  4. INSERT INTO dbo.customer DEFAULT VALUES   
    5. 
  
  
  
  5. SELECT @customerid = SCOPE_IDENTITY()   
    6. 
  
  
  
  6. GO   
    7. 
  
  
  
  7. DECLARE @customerid INT   
    8. 
  
  
  
  8. EXEC dbo.p_InsertCustomer @customerid output   
    9. 
  
  
  
  9. SELECT @customerid AS customerid 
    10.

我们返回的标识值还是3，不过这次我们获得的标识值是正确的，因为我们添加了第三个客户条目。如果我们检查一下审计工作表，就会发现里面已经有第四个条目记录新插入的客户记录。由于函数SCOPE_IDENTITY()只作用于当前范围，只返回当前执行程序的值，这样就避免了发生刚才那样的问题。

前面讲过，函数@@IDENTITY和函数SCOPE_IDENTITY()不止用于表，不像函数IDENT_CURRENT那样可以用表作为参数。使用@@IDENTITY和SCOPE_IDENTITY()这两个函数的话在设置代码时需要加倍小心，才能够从所需要的表里获得正确的标识值。从表面上来看，放弃这两个函数，只使用函数IDENT_CURRENT并指定表是更安全的办法。这样可以避免出现获得错误标识值的情况，对吧?记得先前说过函数IDENT_CURRENT不仅会跨范围，而且它还会跨连接。也就是说，使用这个函数生成的值不仅仅限于你的连接所执行的程序，它的涵盖范围还包括整个数据库所有的连接。因此，即使是在规模较小的OLTP环境里，它也会出现不能准确返回所需值的问题。这样就可能发生类似前面@@IDENTITY函数/触发器的数据损坏问题。

我的建议是函数SCOPE_IDENTITY()是三个函数里最安全的函数，应该设置为默认函数。使用这个函数，你可以放心地添加触发器和次存储过程，无需担心意外损坏数据。而另外两个函数可以保留应付特殊的情况，当遇到需要使用这两个函数的特殊情况时，建议记录它们的使用情况并进行测试。

小技巧：

Sql Server 判断表是存在标识列

If Exists(Select * from SysColumns Where ID=OBJECT_ID(N'TEST1') And COLUMNPROPERTY(ID,Name,'IsIdentity')=1)

Print N'有自增列'

Else

Print N'没有自增列'

Sql Server 显示当前数据库包含自增列的表

Select b.name,a.* from SysColumns a,sysobjects b Where a.id=b.id and COLUMNPROPERTY(a.ID,a.Name,'IsIdentity')=1

SQL SERVER自增张字段复位方法：

SQLSERVER 复位:

Truncate table Ashare_CJHB

Dbcc checkident (Ashare_CJHB,RESEED,0)

【编辑推荐】

  1. 在T-SQL中使用临时表的注意事项(1)
  2. SQL Server数据库管理常用的SQL和T-SQL语句(1)
  3. 用T-SQL操作面试SQL Server开发人员(1)
  4. 用代码刷新ODBC链接SQL Server表
  5. T-SQL实用例句

分享题目：如何SQLServer中准确的获得标识值
当前URL：http://www.hantingmc.com/qtweb/news26/244276.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

ChatGPT知识

分类信息网站

四川主机托管　　 　蜀锦在线　　 　新都四新网站建设　　 　成都网站设计　　 　四川成都网站制作　　 　成都网站设计　　 　bgp机房托管　　 　重庆app软件开发　　 　眉山电信机房托管　　 　网站推广营销　　 　防护网厂　　 　APP定制　　 　成都网站SEO优化公司　　 　官网SEO排名　　 　成都移动托管　　 　网络营销推广　　 　合江网站建设　　 　成都钢筋机械设备　　 　甘孜州博瑞斯　　 　网站方案