漫谈SQL Server中的标识列(一)

昵称728549 2010-02-07

展开全文

一、标识列的定义以及特点

SQL Server中的标识列又称标识符列,习惯上又叫自增列。
该种列具有以下三种特点：

1、列的数据类型为不带小数的数值类型
2、在进行插入(Insert)操作时，该列的值是由系统按一定规律生成,不允许空值
3、列值不重复，具有标识表中每一行的作用,每个表只能有一个标识列。

由于以上特点，使得标识列在数据库的设计中得到广泛的使用。

二、标识列的组成
创建一个标识列，通常要指定三个内容:
1、类型（type）
在SQL Server 2000中，标识列类型必须是数值类型，如下：
decimal、int、numeric、smallint、bigint 、tinyint
其中要注意的是，当选择decimal和numeric时，小数位数必须为零
另外还要注意每种数据类型所有表示的数值范围

2、种子(seed)
是指派给表中第一行的值,默认为1

3、递增量(increment)
相邻两个标识值之间的增量，默认为1。

三、标识列的创建与修改
标识列的创建与修改，通常在企业管理器和用Transact-SQL语句都可实现，使用企业管理管理器比较简单，请参考SQL Server的联机帮助，这

里只讨论使用Transact-SQL的方法

1、创建表时指定标识列
标识列可用 IDENTITY 属性建立，因此在SQL Server中，又称标识列为具有IDENTITY属性的列或IDENTITY列。
下面的例子创建一个包含名为ID,类型为int,种子为1，递增量为1的标识列
CREATE TABLE T_test
(ID int IDENTITY(1,1),
Name varchar(50)
)

2、在现有表中添加标识列
下面的例子向表T_test中添加一个名为ID,类型为int,种子为1，递增量为1的标识列
--创建表
CREATE TABLE T_test
(Name varchar(50)
)

--插入数据
INSERT T_test(Name) VALUES('张三')

--增加标识列
ALTER TABLE T_test
ADD ID int IDENTITY(1,1)

3、判段一个表是否具有标识列

可以使用 OBJECTPROPERTY 函数确定一个表是否具有 IDENTITY（标识）列,用法:
Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHasIdentity')
如果有，则返回1,否则返回0

4、判断某列是否是标识列

可使用 COLUMNPROPERTY 函数确定某列是否具有IDENTITY 属性,用法
SELECT COLUMNPROPERTY( OBJECT_ID('表名'),'列名','IsIdentity')
如果该列为标识列，则返回1,否则返回0

5、查询某表标识列的列名
SQL Server中没有现成的函数实现此功能，实现的SQL语句如下
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(
OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1

6、标识列的引用

如果在SQL语句中引用标识列，可用关键字IDENTITYCOL代替
例如，若要查询上例中ID等于1的行，
以下两条查询语句是等价的
SELECT * FROM T_test WHERE IDENTITYCOL=1
SELECT * FROM T_test WHERE ID=1

7、获取标识列的种子值

可使用函数IDENT_SEED,用法：
SELECT IDENT_SEED ('表名')

8、获取标识列的递增量

可使用函数IDENT_INCR ,用法：
SELECT IDENT_INCR('表名')

9、获取指定表中最后生成的标识值

可使用函数IDENT_CURRENT，用法:
SELECT IDENT_CURRENT('表名')
注意事项：当包含标识列的表刚刚创建,为经过任何插入操作时，使用IDENT_CURRENT函数得到的值为标识列的种子值，这一点在开发数据库应用程序的时候尤其应该注意。

总结一下标识列在复制中的处理方法

1、快照复制
   在快照复制中，通常无须考虑标识列的属性。

2、事务复制
   举例：
   发布数据库A,订阅数据库B，出版物为T_test_A,订阅表为T_test_B
   CREATE TABLE T_test_A
(ID int IDENTITY(1,1),
  Name varchar(50)
)
   CREATE TABLE T_test_B
(ID int IDENTITY(1,1),
  Name varchar(50)
)

   在这种情况下，复制代理将无法将新行复制到库B，因为列ID是标识列，不能给标识列显示提供值,复制失败。
   这时，需要为标识列设置NOT FOR REPLICATION 选项。这样，当复制代理程序用任何登录连接到库B上的表T_test时，该表上的所有 NOT
   FOR REPLICATION 选项将被激活,就可以显式插入ID列。

   这里分两种情况：
   1、库B的T_test表不会被用户(或应用程序)更新
   最简单的情况是：如果库B的T_test不会被用户(或应用程序)更新，那建议去掉ID列的标识属性，只采用简单int类型即可。

   2、库B的T_test表是会被其他用户（或应用程序）更新

   这种情况下，两个T_test表的ID列就会发生冲突,举例:
   在库A中执行如下语句:
   INSERT T_test_A(Name) VALUES(’Tom’)(假设ID列为1)
   在库B中执行如下语句:
   INSERT T_test_B(Name) VALUES(’Pip’)(假设ID列为1)
   这样，就会在库A和库B的两个表分别插入一条记录，显然，是两条不同的记录。
   然而事情还没有结束，待到预先设定的复制时间，复制代理试图把记录"1 TOM"插入到库B中的T_test表，但库B的T_test_B表已经存在

ID为1的列，插入不会成功，通过复制监视器，我们会发现复制失败了。
   解决以上问题的方法有：
  (1)为发布方和订阅方的标识列指定不同范围的值，如上例可修改为：
     --确保该表记录不会超过10000000
     CREATE TABLE T_test_A
(ID int IDENTITY(1,1),
  Name varchar(50)
)
   CREATE TABLE T_test_B
(ID int IDENTITY(10000000,1),
  Name varchar(50)
)
   (2)使发布方和订阅方的标识列的值不会重复, 如
     --使用奇数值
     CREATE TABLE T_test_A
(ID int IDENTITY(1,2),
  Name varchar(50)
)
     --使用偶数值
     CREATE TABLE T_test_B
(ID int IDENTITY(2,2),
  Name varchar(50)
)
    这种办法可推广，当订阅方和发布方有四处时，标识列属性的定义分别如下
    (1,4),(2,4),(3,4),(4,4)

3、合并复制
   采用事务复制中解决方法,只要使发布表和订阅表标识列的值不重复既可。

SQL Server 返回最后插入记录的自动编号ID

最近在开发项目的过程中遇到这么一个问题，就是在插入一条记录的后立即获取其在数据库中自增的ID，以便处理相关联的数据，怎么做？在sql server 2000中可以这样做，有几种方式。详细请看下面的讲解与对比。

一、要获取此ID,最简单的方法就是

以下举一简单实用的例子)

--创建数据库和表
create database MyDataBase
use MyDataBase
create table mytable
(
id int identity(1,1),
name varchar(20)
)
--执行这个SQL,就能查出来刚插入记录对应的自增列的值
insert into mytable values('李四')
select @@identity

二、三种方式的比较

SQL Server 2000中，有三个比较类似的功能：他们分别是：SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY，它们都返回插入到 IDENTITY 列中的值。
IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。

例如，有两个表 T1 和 T2，在 T1 上定义了一个 INSERT 触发器。当将某行插入 T1 时，触发器被激发，并在 T2 中插入一行。此例说明了两个作用域：一个是在 T1 上的插入，另一个是作为触发器的结果在 T2 上的插入。

假设 T1 和 T2 都有 IDENTITY 列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。

@@IDENTITY 返回插入到当前会话中任何作用域内的最后一个 IDENTITY 列值，该值是插入 T2 中的值。

SCOPE_IDENTITY() 返回插入 T1 中的 IDENTITY 值，该值是发生在相同作用域中的最后一个 INSERT。如果在作用域中发生插入语句到标识列之前唤醒调用 SCOPE_IDENTITY() 函数，则该函数将返回 NULL 值。

而IDENT_CURRENT('T1') 和 IDENT_CURRENT('T2') 返回的值分别是这两个表最后自增的值。
ajqc的实验

40条本地线程,40+40条远程线程同时并发测试,插入1200W行),得出的结论是:
1.在典型的级联应用中.不能用@@IDENTITY,在CII850,256M SD的机器上1W多行时就会并发冲突.在P42.8C,512M DDR上,才6000多行时就并发冲突.
2.SCOPE_IDENTITY()是绝对可靠的,可以用在存储过程中,连触发器也不用建,没并发冲突
SELECT IDENT_CURRENT('TableName') --返回指定表中生成的最后一个标示值
SELECT IDENT_INCR('TableName')--返回指定表的标示字段增量值
SELECT IDENT_SEED('TableName')--返回指定表的标示字段种子值

返回最后插入记录的自动编号
SELECT IDENT_CURRENT('TableName')
返回下一个自动编号:
SELECT IDENT_CURRENT('TableName') + (SELECT IDENT_INCR('TableName'))
SELECT @@IDENTITY --返回当前会话所有表中生成的最后一个标示值

以上是针对sql server 2000的情况，但是诸如my sql或oracle中如何实现呢？怎么处理呢？本人也在摸索中。。。。如有朋友知道此处理方式，别忘了告之，一同分享！

如何在Sql Server中准确的获得标识值

摘要： SQL Server有三种不同的函数可以用来获得含有标识列的表里最后生成的标识值： @@IDENTITY SCOPE_IDENTITY() IDENT_CURRENT('数据表名') 以上三个函数虽然都可以返回数据库引擎最后生成插入标识列的值，但是根据插入行的来源(例如：存储过程或触发器)以及插入

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

　　@@IDENTITY
　　SCOPE_IDENTITY()
　　IDENT_CURRENT('数据表名') IT学习网，全国最大的IT在线学习网站！

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

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

　　SCOPE_IDENTITY()函数跟上一个函数几乎是一摸一样的，不同的地方：即前者返回的值只限于当前范围(即执行中的存储过程)。 IT学习网，全国最大的IT在线学习网站！

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

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

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

　　以下是引用片段：

　　CREATE TABLE dbo.customer
　　(customerid INT IDENTITY(1,1) PRIMARY KEY)
　　GO
　　CREATE TABLE dbo.auditlog
　　(auditlogid INT IDENTITY(1,1) PRIMARY KEY,
　　customerid INT, action CHAR(1),
　　changedate datetime DEFAULT GETDATE())
　　GO

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

　　以下是引用片段：

　　CREATE PROCEDURE dbo.p_InsertCustomer @customerid INT output
　　AS
　　SET nocount ON
　　INSERT INTO dbo.customer DEFAULT VALUES
　　SELECT @customerid = @@identity
　　GO
　　CREATE TRIGGER dbo.tr_customer_log ON dbo.customer
　　FOR INSERT, DELETE
　　AS
　　IF EXISTS (SELECT 'x' FROM inserted)
　　INSERT INTO dbo.auditlog (customerid, action)
　　SELECT customerid, 'I'
　　FROM inserted
　　ELSE
　　IF EXISTS (SELECT 'x' FROM deleted)
　　INSERT INTO dbo.auditlog (customerid, action)
　　SELECT customerid, 'D'
　　FROM deleted
　　GO

现在我们可以执行程序，创建客户表的第一行了：

　　以下是引用片段：

　　DECLARE @customerid INT
　　EXEC dbo.p_InsertCustomer @customerid output
　　SELECT @customerid AS customerid

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

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

　　以下是引用片段：

　　DELETE FROM dbo.customer WHERE customerid = 1

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

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

　　以下是引用片段：

　　DECLARE @customerid INT
　　EXEC dbo.p_InsertCustomer @customerid output
　　SELECT @customerid AS customerid

　　哇!看看出现了什么情况!如果我们现在再看客户工作表，就会发现虽然创建了客户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()函数，并重新执行程序来添加第三个客户条目：
以下是引用片段：

　　ALTER PROCEDURE dbo.p_InsertCustomer @customerid INT output
　　AS
　　SET nocount ON
　　INSERT INTO dbo.customer DEFAULT VALUES
　　SELECT @customerid = SCOPE_IDENTITY()
　　GO
　　DECLARE @customerid INT
　　EXEC dbo.p_InsertCustomer @customerid output
　　SELECT @customerid AS customerid

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

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