Sequence usage of Oracle, DB2 and PostgreSQL

Sequence is a sequence of numbers that the database system automatically increases according to certain rules. This sequence is generally used as the surrogate primary key (because it does not repeat) and has no other meaning.

Sequence is the characteristic of database system, some databases have sequence, some have not. For example, Oracle, DB2, PostgreSQL databases have sequence,mysql, SQL Server, Sybase, and other databases without sequence.

According to my personal understanding, sequence is a special stored arithmetic progression table, which is controlled by the database system, at any time the database system can be based on the current number of records and the size of the step to get to the table the next record should be how much, this table has no practical significance, often used for the key to use, very good, Oh, but very depressed each database manufacturer urine less than a pot-each have a set of sequence definition and operation. Here I compare and summarize the definition and sequence of the common three kinds of databases for future viewing.

I. Definition of sequence

Defines a seq_test, with a minimum value of 10000, a maximum of 99999999999999999, starting at 20000, an increment of step 1, and a cyclic sort sequence cached for 20.

How Oracle is defined:

The code is as follows	Copy Code
CREATE SEQUENCE Seq_test MinValue 10000 MAXVALUE 99999999999999999 START with 20000 INCREMENT by 1 CACHE 20 CYCLE order;

DB2 's writing:

The code is as follows	Copy Code
CREATE SEQUENCE Seq_test As BIGINT START with 20000 INCREMENT by 1 MinValue 10000 MAXVALUE 99999999999999999 CYCLE CACHE 20 order;

PostgreSQL's writing:

The code is as follows	Copy Code
CREATE SEQUENCE Seq_test INCREMENT by 1 MinValue 10000 MAXVALUE 99999999999999999 START 20000 CACHE 20 CYCLE;

Second, the value of sequence
In Oracle, DB2, and PostgreSQL, sequence values are different, but both have current and next values. Take the value code as follows

-Oracle

The code is as follows	Copy Code
SELECT Seq_test. Currval from DUAL; --Current value SELECT Seq_test. Nextval from DUAL; --Next value

--DB2

The code is as follows	Copy Code
VALUES prevval for "seq_test"; --Current value VALUES nextval for "seq_test"; --Next value --or VALUES PREVIOUS VALUE for "seq_test"; --Current value VALUES NEXT VALUE for "seq_test"; --Next value

--PostgreSQL

The code is as follows	Copy Code
SELECT Currval (seq_test); --Current value SELECT Nextval (seq_test); --Next value

The difference and relation between sequence and indentity

The basic function of sequence and indentity is similar. Can generate a self-increasing number sequence. Sequence is an object in the database system that can be used throughout the database and has nothing to do with the table; indentity is only specified on a column in the table, and the scope is this table.