关于MS-SQL中的@@IDENTITY

关于MS-SQL中的@@IDENTITY

分类:数据库

基本知识

1. IDENTITY 列不能由用户直接更新，它是由系统自动维护的。
2.该列数据类型必须为数值型：int, smallint, tinyint, decimal or numeric with scale 0。
3.该列不能为 null。
4.不能在该列上设置缺省值。
5.递增量只能为整形(比如：1，2，-3)。不能为小数，也不能为0。
6.基值(种子值 seed)可以由用户设置，缺省值为1。

理解 @@IDENTITY

@@IDENTITY 返回最后一个插入 IDENTITY 的值，这些操作包括：INSERT， SELECT INTO，或者 bulk copy。如果在给没有 IDENTITY 列的其他表插入记录，系统将其置为 null。如果有多行记录插入到 IDENTITY 表中，@@IDENTITY 表示最后一个产生的值。如果触发了某个触发器，并且这个触发器执行向另一个带有 IDENTITY 列的表的插入操作，@@IDENTITY 将返回这个由触发器产生的值。如果这个触发器插入的表中不包含 IDENTITY 列，那么 @@IDENTITY 将为 null。如果插入操作失败，@@IDENTITY 值依然会增加，所以 IDENTITY 不保证数据的连续性。
@@IDENTITY 是当前连接的全局变量，只对当前连接有效。也就是说，如果断开连接再重新连接后，@@IDENTITY 为 null。以 ADO 来说，@@IDENTITY 在 Connection 对象打开和关闭期间是有意义的，即在 Connection 对象的存在范围内有效。在 MTS 组件中，从打开连接到显式的关闭连接(Connection.Close)或者到调用了 SetAbort，SetComplete之前，在这期间，@@IDENTITY 有意义。
使用 Truncate table 语句会使 IDENTITY 列重新开始计算。

得到 @@IDENTITY 的值 (有三种方法，以下代码均使用 VBScript)

方法一：

Dim Conn, strSQL, Rs
Set Conn = CreateObject("ADODB.Connection")
’ Open a connection to the database
Conn.Open("DSN=myDSN;；UID=myUID;；PWD=myPWD;；")

’ Insert a new record into the table
strSQL = "INSERT INTO mtTable (columnName) valueS (’something’)"

’ Execute the SQL statement
Conn.Execute(strSQL)

’ Get the @@IDENTITY.
strSQL = "SELECT @@IDENTITY AS NewID"
Set Rs = Conn.Execute(lsSQL)
NewID = Rs.Fields("NewID").value

’ Close the connection
Conn.Close()
Set Conn = Nothing

方法二(仅限于 ADO 2.0 以上)：

Dim Conn, strSQL, Rs
Set Conn = CreateObject("ADODB.Connection")
’ Open a connection to the database
Conn.Open("DSN=myDSN;；UID=myUID;；PWD=myPWD;；")

’ Insert a new record into the table
lsSQL = "INSERT INTO myTable (columnName) valueS (’something’);；" &_

　　 "SELECT @@IDENTITY AS NewID;；"

’ Execute the SQL statement
Set Rs = Conn.Execute(lsSQL)

’ Get the second resultset into a RecordSet object
Set Rs = Rs.NextRecordSet()

’ Get the inserted ID
NewID = Rs.Fields("NewID").value

’ Close the connection
Conn.Close()
Set Conn = Nothing

方法三：

Dim Conn, strSQL, Rs
Set Conn = CreateObject("ADODB.Connection")
’ Open a connection to the database
Conn.Open("DSN=myDSN;；UID=myUID;；PWD=myPWD;；")

’ Insert a new record into the table
strSQL = "SET NOCOUNT ON;；" &_

　　 "INSERT INTO myTable (columnName) valueS (’something’);；" &_

　　 "SELECT @@IDENTITY AS NewID;；"

’ Execute the SQL statement
Set Rs = Conn.Execute(lsSQL)

’ Get the inserted ID
NewID = Rs.Fields("NewID").value

’ Close the connection
Conn.Close()
Set Conn = Nothing

 

慎用@@IDENTITY

2006-08-31 16:02

OK，也许大家对SQL Server中的 @@IDENTITY 都不陌生，都知道它是获取数据表中最后一条插入数据的IDENTITY值。比如，表 A 中有个 ID 为自增1的字段，假设此时 ID 的值为100，现在如果我往表A插入一条数据，并在插入后 SELECT @@IDENTITY，则其返回 101，最后一条IDENTITY域（即ID域）的值。 现在问题来了，为什么说要慎用@@IDENTITY呢？原因是 @@IDENTITY 它总是获取最后一条变更数据的自增字段的值，而忽略了进行变更操作所在的范围约束。比如，我有表 A 和表 B 两个表，现在我在表 A 上定义了一个Insert触发器，当在表 A 中插入一条数据时，自动在表 B 也插入一条数据。此时，大家注意，有两个原子操作：在A中插入一条数据，接着在B中随后插入一条数据。 现在我们想下，假设上面表 A 和表 B 都有IDENTITY自增域，那么我们在表 A 插入一条数据后，使用了 SELECT @@IDENTITY 输出时，输出的到底是 A 还是 B 的自增域的值呢？  答案很明显，是谁最后插入就输出谁，那么就是 B 了。于是，我本意是想得到 A 的自增域值，结果得到了 B 的自增域值，一只 BUG 随之诞生，搞不好还会影响到整个系统数据的混乱。 因此，对于这种情况，建议大家慎用 @@IDENTITY，而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值，但是它是仅限在一个操作范围之内，而不像 @@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。 我们具体执行段微软提供的示范SQL语句看看就理解了：  1  USE  tempdb  2  GO  3  CREATE   TABLE  TZ (  4   Z_id  int   IDENTITY ( 1 , 1 ) PRIMARY   KEY ,  5   Z_name  varchar ( 20 )  NOT   NULL )  6   7  INSERT  TZ  8    VALUES  ( ' Lisa ' )  9  INSERT  TZ 10    VALUES  ( ' Mike ' ) 11  INSERT  TZ 12    VALUES  ( ' Carla ' ) 13  14  SELECT   *   FROM  TZ 15  16  -- Result set: This is how table TZ looks. 17  Z_id Z_name 18  -- ----------- 19  1  Lisa 20  2  Mike 21  3  Carla 22  23  CREATE   TABLE  TY ( 24   Y_id  int   IDENTITY ( 100 , 5 ) PRIMARY   KEY , 25   Y_name  varchar ( 20 )  NULL ) 26  27  INSERT  TY (Y_name) 28    VALUES  ( ' boathouse ' ) 29  INSERT  TY (Y_name) 30    VALUES  ( ' rocks ' ) 31  INSERT  TY (Y_name) 32    VALUES  ( ' elevator ' ) 33  34  SELECT   *   FROM  TY 35  -- Result set: This is how TY looks: 36  Y_id Y_name 37  -- ------------- 38  100  boathouse 39  105  rocks 40  110  elevator 41  42  /* Create the trigger that inserts a row in table TY  43  when a row is inserted in table TZ */ 44  CREATE   TRIGGER  Ztrig 45  ON  TZ 46  FOR   INSERT   AS   47    BEGIN 48    INSERT  TY  VALUES  ( '' ) 49    END 50  51  /* FIRE the trigger and determine what identity values you obtain  52  with the @@IDENTITY and SCOPE_IDENTITY functions. */ 53  INSERT  TZ  VALUES  ( ' Rosalie ' ) 54  55  SELECT   SCOPE_IDENTITY ()  AS   [ SCOPE_IDENTITY ] 56  GO 57  SELECT   @@IDENTITY   AS   [ @@IDENTITY ] 58  GO