SQL Server 2005中标识列identity属性及其相关

 

SQL Server 2005中标识列identity属性及其相关

1、如何使用:
在创建表的时候指定某个列为标识列：
create table table_name（ ...,col_name int identity(1,1) ,... )...;

更改表结构时，IDENTITY 属性只能分配给 int 或 bigint 列。
alter  table table_name (  ... alter column col_name identity(1,1),...)...;

2、IDENTITY （seed , increment)  介绍
指示新列是标识列。
在表中添加新行时，数据库引擎将为该列提供一个唯一的增量值。
标识列通常与 PRIMARY KEY 约束一起用作表的唯一行标识符。
可以将 IDENTITY 属性分配给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。
每个表只能创建一个标识列。不能对标识列使用绑定默认值和 DEFAULT 约束。
必须同时指定种子和增量，或者两者都不指定。如果二者都未指定，则取默认值 (1,1)。

seed  是装入表的第一行所使用的值。
increment  是向装载的前一行的标识值中添加的增量值。

从上面的描述来看，实在不如oracle的序列方便和灵活。

上述会隐式的增加和写入数据。
如果需要允许将显式值插入表的标识列中，可使用
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF } 功能

开启。即：
set identity_insert table_name on
。。。

 

3、几个函数：
IDENTITY (data_type [ , seed , increment ] ) AS column_name

只用于在带有 INTO table 子句的 SELECT 语句中将标识列插入到新表中。尽管类似，但是 IDENTITY 函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。
因为该函数在表中创建一个列，所以必须在选择列表中指定该列的名称。

IDENT_INCR ( 'table_or_view' ) 返回增量值（返回形式为 numeric (@@MAXPRECISION,0)），该值是在带有标识列的表或视图中创建标识列时指定的

IDENT_SEED ( 'table_or_view' ) 返回种子值（返回形式为 numeric(@@MAXPRECISION,0)），该值是在带有标识列的表或视图中创建标识列时指定的。使用 DBCC CHECKIDENT 更改标识列的当前值不会更改此函数返回的值。

 

4、SCOPE_IDENTITY、IDENT_CURRENT、 @@IDENTITY

三者的区别：
1)、SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 是相似的函数，因为它们都返回插入到标识列中的值。
2)、IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。

3)、IDENT_CURRENT 返回为某个会话和用域中的指定表生成的最新标识值。
@@IDENTITY 返回为跨所有作用域的当前会话中的某个表生成的最新标识值。
SCOPE_IDENTITY 返回为当前会话和当前作用域中的某个表生成的最新标识值。

4)、如果对空表或没有标识列的表调用 IDENT_CURRENT 函数，则该函数返回 NULL。

5)、如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

6)SCOPE_IDENTITY() 返回插入到同一作用域中的标识列内的最后一个标识值。一个范围是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。
IDENT_CURRENT( 'table_name' ) 返回为某个会话和作用域中指定的表或视图生成的最新的标识值。

实例：

. 比较 IDENT_CURRENT、@@IDENTITY 和 SCOPE_IDENTITY 返回的标识值
以下示例将显示由 IDENT_CURRENT、@@IDENTITY 和 SCOPE_IDENTITY 返回的不同标识值。

USE AdventureWorks;
GO
IF OBJECT_ID(N't6', N'U') IS NOT NULL
    DROP TABLE t6;
GO
IF OBJECT_ID(N't7', N'U') IS NOT NULL
    DROP TABLE t7;
GO
CREATE TABLE t6(id int IDENTITY);
CREATE TABLE t7(id int IDENTITY(100,1));
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
   INSERT t7 DEFAULT VALUES
END;
GO
--End of trigger definition

SELECT id FROM t6;
--id is empty.

SELECT id FROM t7;
--ID is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('t7');
/* Returns value inserted into t7, that is in the trigger.*/

SELECT IDENT_CURRENT('t6');
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL because there has been no INSERT action
up to this point in this session.*/

SELECT SCOPE_IDENTITY();
/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('t7');
/* Returns the last value inserted into t7.*/
 

5、DBCC CHECKIDENT (Transact-SQL)
检查指定表的当前标识值，如有必要，则更改标识值。还可以使用 DBCC CHECKIDENT 为标识列手动设置新的当前标识值。

具体用法如下：

DBCC CHECKIDENT
(
        table_name
        [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]