Differences Between Auto-increment columns and Sequences

I need to use DB2 recently, but I have a lack of understanding about the sequence of DB2. I have found this blog post while searching for a large amount of data. I think it is very detailed, special Collections.

 

 

Original blog: http://blog.csdn.net/chinayuan/archive/2009/03/05/3961092.aspx

 

 

 

 

 

 

 

 

----------------------------------------------------------------
Differences between identity and sequence:
----------------------------------------------------------------
DB2 and SQL Server do not support two or more identity fields in a table.
Oracle does not support the idenity Field
SQL Server does not support sequence usage
DB2 supports sequence and identity usage.

Identity can be used only once in one table.
However, a sequence can be used in multiple fields in a table.

----------------------------------------------------------------
DB2 usage: (sequence and identity are supported)
----------------------------------------------------------------
References: http://www.ibm.com/developerworks/cn/data/library/techarticles/0302fielding/0302fielding.html

Identity: (two methods)
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: (two methods)
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,
....
)

Complex experiment content: (one table has a self-increasing field, and the other two fields use sequence to insert data) the following SQL statement runs normally.
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, increment by 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 usage: (only sequence is supported)
----------------------------------------------------------------
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_priority, event_status)
Values (event_sequence.nextval,: New. pkey, 'indb2admincustomerbg ', 'create', 1, 0 );

Complex experiment content: (two fields in a table are inserted with sequence) the following SQL statement runs properly.

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_id number (20) primary key,
Xid varchar2 (200 ),
Object_key varchar2 (80 ),
Object_name varchar2 (40 ),
Object_function varchar2 (40 ),
Event_priority number (5 ),
Event_time date default sysdate not null,
Event_status number (5 ),
Event_comment varchar2 (100)
);

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

Insert into temp (event_id, event_priority, event_status) values (sequence1.nextval, sequence1.nextval, sequence1.nextval );

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

Select event_id, event_priority, event_status from temp;

----------------------------------------------------------------
SQL Server identity usage: (only identity is supported)
----------------------------------------------------------------
Create Table wbia_jdbc_eventstore (
Event_id decimal (20) Identity (1, 1) primary key,
)

Complex experiment content: (a table has a self-increasing field that does not allow more than two self-increasing columns) the following SQL statement runs properly.

Drop table temp;
Create Table temp
(
Event_id int not null primary key,
Event_priority decimal (20) Identity (1, 1 ),
-- Event_status int identity (2, 1), -- multiple identity columns specified for table 'temp '. Only one identity column per table is allowed.
);

Insert into temp (event_id) values (1 );
Insert into temp (event_id) values (2 );

Select event_id, event_priority from temp;

----------------------------------------------------------------
MySQL identity usage: (only identity is supported)
----------------------------------------------------------------
Drop table temp;
Create Table temp
(
Event_id bigint (12) not null primary key auto_increment,
Xid varchar (200 ),
Object_key varchar (80 ),
Object_name varchar (40 ),
Object_function varchar (40 ),
Event_priority integer,
Event_time timestamp,
Event_status integer,
Event_comment varchar (100)
) Engine = InnoDB auto_increment = 100000 ;;

-- Engine = InnoDB: indicates the type of the table you created:
-- The default value is MyISAM. The old version is isam. InnoDB tables support advanced applications such as external keys and transactions.
-- Use engine = InnoDB at the end of the table creation;
-- Auto_increment = 100000: sets the ID to automatically increase the column, and increases from 100000.

Insert into temp (event_priority, event_status) values (1, 1 );

Insert into temp (event_priority, event_status) values (1, 1 );

Insert into temp (event_priority, event_status) values (1, 1 );

Select event_id, event_priority, event_status from temp;

-----------------------------------------------------------------------
Sequence technology: http://baike.baidu.com/view/71967.htm
-----------------------------------------------------------------------
Sequence is a sequence of numbers automatically added by the database system according to certain rules. This sequence is generally used as the proxy primary key (because it will not be repeated) and has no other meaning.
Sequence is a feature of the database system. Some databases have sequence and some do not. For example, sequence exists in Oracle, DB2, and PostgreSQL databases, and sequence exists in MySQL, SQL Server, Sybase, and other databases.
According to my personal understanding, sequence is a table in the data that stores the differential sequence. This table is controlled by the database system, at any time, the database system can obtain the next record in the table based on the current number of records plus the step size. This table has no practical significance and is often used for primary keys, which is very good, haha, but very depressing database vendors don't get into one pot-each has its own set of sequence definitions and operations. Here I will compare and summarize the sequence definitions and operations of the three common databases for future reference.
1. Define Sequence
Define a seq_test with a minimum value of 10000 and a maximum value of 99999999999999999. Starting from 20000, the incremental step is 1 and the cache is 20.
Oracle definition method:
Create sequence seq_test
Min value 10000
Max value 99999999999999999
Start with 20000
Increment by 1
Cache 20
Cycle
Order;

DB2 statement:
Create sequence seq_test
As bigint
Start with 20000
Increment by 1
Min value 10000
Max value 99999999999999999
Cycle
Cache 20
Order;

PostgreSQL statement:
Create sequence seq_test
Increment by 1
Min value 10000
Max value 99999999999999999
Start 1, 20000
Cache 20
Cycle;

Ii. Reference parameters for sequence values of Oracle, DB2, and PostgreSQL databases: currval and nextval, indicating the current value and the next value respectively. The values of nextval are obtained from the sequence of the three databases respectively.
In ORACLE: seq_test.nextval
Example: Select seq_test.nextval from dual;
In DB2: nextval for seq_topicms
Example: Values nextval for seq_test;
In PostgreSQL: nextval (seq_test)
Example: Select nextval (seq_test );

Iii. Differences and relationships between sequence and indentity
Sequence and indentity have similar basic functions. Can generate auto-incrementing sequence.
Sequence is an object in the database system. It can be used in the whole database and has no relationship with the table. indentity only specifies a column in the table and its scope is the table.

Iv. Sequence allocation policy in PostgreSQL (postgresql8.3.x)
After the select nextval (seq_test); is called, the system immediately assigns a sequence number to the user, and then the system's sequence immediately adds the step size (increment by 1) set earlier ), whether or not the serial number is used by the user.
Call select currval (seq_test); return the current serial number. This serial number will not change as long as it is not used. It will not change until it is used, and the serial number has been assigned to the current request, no other requests will be distributed.
Therefore, if currval is used to reduce waste, and the ID is not continuous, nextval can be used. This is because the serial number will be discarded if it fails to be used once it is assigned to you!

Introduction to Oracle Sequence
In Oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. Create Sequence
First, you must have the create sequence or create any sequence permission,
Create sequence emp_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cache 10;
Once emp_sequence is defined, you can use currval, nextval
Currval = returns the current Sequence Value
Nextval = increase the sequence value, and then return the Sequence Value
For example:
Emp_sequence.currval
Emp_sequence.nextval
Where sequence can be used:
-Select statements that do not contain subqueries, snapshot, and view
-The insert statement is in the subquery.
-In the value of the nsert statement
-Update in Set
See the following example:
Insert into EMP values
(Em1_q. nextval, 'Lewis ', 'cler', 7902, sysdate, 1200, null, 20 );
Select empseq. currval from dual;
Note that:
-The first nextval returns the initial value. The subsequent nextval automatically increases the value of your defined increment by and then returns the added value. Currval always returns the value of the current sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur. Nextval increases the sequence value once. Therefore, if you use multiple nextval values in the same statement, their values are different. Understand?
-If the cache value is specified, Oracle can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, Oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.

2. Alter Sequence
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.
Alter sequence example
Alter sequence emp_sequence
Increment by 10
Max value 10000
Cycle -- start from scratch after 10000
Nocache;
Initialization parameters that affect sequence:
Sequence_cache_entries = sets the number of sequence that can be simultaneously cached.
It's easy to drop sequence.
Drop sequence order_seq;