自增长列和序列的区别(identity与sequence的区别)

----------------------------------------------------------------
identity 和sequence的区别：
----------------------------------------------------------------
db2，sql server 都不支持一个表中包含两个以及两个以上的identity字段
oracle 不支持idenity字段
sql server不支持sequence用法
db2 支持sequence,identity的两种用法

identity因为只能在一个table中用一次。
但是一个sequence可以在一张表中的多个字段多被用到。



----------------------------------------------------------------
db2 使用方法：(支持sequence,identity)
----------------------------------------------------------------
参考资料： http://www.ibm.com/developerworks/cn/data/library/techarticles/0302fielding/0302fielding.html

identity :(两种使用方法)
1. Select IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
2.CREATE TABLE WBIA_JDBC_EventStore
(
event_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,INCREMENT BY 1,NO CACHE ) PRIMARY KEY,
)


sequence:(两种使用方法)
CREATE SEQUENCE orders_seq AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER

1. values nextval for $sequenceName$
2.examples:
INSERT INTO customer_orders_t
VALUES
(
NEXT VALUE FOR orders_seq,
....
)


复杂的实验内容：（一个表中有一个自增长的字段，另外两个字段都用sequence来插入数据）下面sql语句得到正常运行
drop sequence sequence1;
drop sequence sequence2;
CREATE SEQUENCE sequence1 AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER;
CREATE SEQUENCE sequence2 AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER;

drop table temp;
CREATE TABLE temp
(
event_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,NO CACHE ) PRIMARY KEY,
xid VARCHAR(200),
object_key VARCHAR(80),
object_name VARCHAR(40),
object_function VARCHAR(40),
event_priority INTEGER,
event_time TIMESTAMP default CURRENT TIMESTAMP NOT NULL,
event_status INTEGER,
event_comment VARCHAR(100)
);


insert into temp(event_priority,event_status) values( NEXT VALUE FOR sequence1,NEXT VALUE FOR sequence2);

insert into temp(event_priority,event_status) values(NEXT VALUE FOR sequence1,NEXT VALUE FOR sequence1);

insert into temp(event_priority,event_status) values( NEXT VALUE FOR sequence2,NEXT VALUE FOR sequence2);

select event_id,event_priority,event_status from temp;

----------------------------------------------------------------
oracle 使用方法： (只支持sequence)
----------------------------------------------------------------
CREATE SEQUENCE event_sequence start with 1;

1.select $sequenceName$.nextval from dual
2.examples:
INSERT INTO wbia_jdbc_eventstore (event_id,object_key,object_name,object_function,event_status)
VALUES( event_sequence.nextval,:NEW.pkey,'InDb2adminCustomerBG','Create',1,0);

复杂的实验内容：（一个表中有两个字段都用sequence来插入数据）下面sql语句得到正常运行

drop sequence sequence1;
drop sequence sequence2;
CREATE SEQUENCE sequence1 start with 1;
CREATE SEQUENCE sequence2 start with 1;

drop table temp;
CREATE TABLE temp
(
event_idNUMBER(20)PRIMARY KEY,
xidVARCHAR2(200),
object_keyVARCHAR2(80),
object_nameVARCHAR2(40),
object_functionVARCHAR2(40),
event_priorityNUMBER(5),
event_time DATEDEFAULT SYSDATE NOT NULL,
event_statusNUMBER(5),
event_commentVARCHAR2(100)
);

insert into temp(event_id,event_status) values(sequence1.nextval,sequence1.nextval,sequence2.nextval);

insert into temp(event_id,sequence1.nextval);

insert into temp(event_id,sequence2.nextval,sequence2.nextval);

select event_id,event_status from temp;

----------------------------------------------------------------
sql server identity 使用方法： (只支持identity)
----------------------------------------------------------------
CREATE TABLE WBIA_JDBC_EVENTSTORE (
event_id DECIMAL(20) IDENTITY(1,1) PRIMARY KEY,
)

复杂的实验内容：（一个表中有一个自增长的字段,不允许两个以上自增长的列）下面sql语句得到正常运行

create sequence seq_test
minvalue 10000
maxvalue 99999999999999999
start with 20000
increment by 1
cache 20
cycle
order;

PostgreSQL的写法：
二、Oracle、DB2、PostgreSQL数据库Sequence值的引用参数为：currval、nextval，分别表示当前值和下一个值。下面分别从三个数据库的Sequence中获取nextval的值。 Oracle中：seq_test.nextval 例如：select seq_test.nextval from dual; DB2中：nextval for SEQ_TOPICMS 例如：values nextval for seq_test; PostgreSQL中：nextval(seq_test) 例如：select nextval(seq_test); 三、Sequence与indentity的区别与联系 Sequence与indentity的基本作用都差不多。都可以生成自增数字序列。 Sequence是数据库系统中的一个对象，可以在整个数据库中使用，和表没有任何关系；indentity仅仅是指定在表中某一列上，作用范围就是这个表。 四、postgresql中的Sequence分配策略（postgresql8.3.x） 调用select nextval(seq_test);后，系统马上分配一个序号给用户，然后系统的次Sequence马上加上之前设置的步长（increment by 1），不论此序号用户是否使用。 调用select currval(seq_test);返回当前的序列号，该序列号只要没有使用，就不会变化，直到被使用后，才会变化，并且该序列号已经分配给当前请求，不会再分配给其他的请求。 所以，减少浪费时，使用currval，不在乎浪费，而且id不连续的话，可以使用nextval。因为一旦分配给你后，使用失败，将会把该序列号丢弃！ ORACLE SEQUENCE的简单介绍 在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。 1、Create Sequence 你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限， CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; 一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL CURRVAL=返回 sequence的当前值 NEXTVAL=增加sequence的值，然后返回 sequence 值 比如： emp_sequence.CURRVAL emp_sequence.NEXTVAL 可以使用sequence的地方： - 不包含子查询、snapshot、VIEW的 SELECT 语句 - INSERT语句的子查询中 - NSERT语句的VALUES中 - UPDATE 的 SET中 可以看如下例子： INSERT INTO emp VALUES (empseq.nextval,'LEWIS','CLERK',7902,SYSDATE,1200,NULL,20); SELECT empseq.currval FROM DUAL; 但是要注意的是： - 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？ - 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。 2、Alter Sequence 你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create . Alter sequence 的例子 ALTER SEQUENCE emp_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE -- 到10000后从头开始 NOCACHE ; 影响Sequence的初始化参数： SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。 可以很简单的Drop Sequence DROP SEQUENCE order_seq;