Sequences in SQL

Sequences

A Sequence is simply an automatic counter, which generates sequential numbers whenever required. A sequence can be defined to: 

• Generate numbers in ascending or descending order.
• Provide intervals between numbers.
• Caching of sequence numbers in memory to speed up their availability .

A sequence is an independent object and can be used with any table that requires its output.

Creating a Sequences :

The minimum information required for generating numbers using a sequence is:

• The starting number.
• The maximum number that can be generated by a sequence.
• The increment value for generating the next number.

Syntax:

   CREATE SEQUENCE <Sequence Name>

   [INCREMENT BY <IntegerValue>

   START WITH <Integer Value>

   MAXVALUE <Integer Value>

   MINVALUE <Integer Value>  CYCLE CACHE ]

Example:

create sequence seq increment by 1 start with 1 maxvalue 999 CYCLE;

INCREMENT BY:
 Specifies the interval between sequence numbers. It can be any positive or negative value but not zero. If this clause is omitted, the default value is 1.
 MAXVALUE And MINVALUE :
 Specifies the maximum or minmum value that a sequence can generate.
 START WITH:
Specifies the first sequence number to be generated. The default for an ascending sequence is the sequence minimum value (1) and for a descending sequence, it is the maximum value (-1).
 CYCLE:
Specifies that the sequence continues to generate repeat values after reaching either its maximum value or minimum value.
 CACHE:
Specifies how many values to generate in advance and to keep in memory for faster access. Minimum value is two for this option.

Altering a  Sequence  :

 A sequence once created can be altered.   This is achieved by using the ALTER SEQUENCE statement.
 The START value of the sequence cannot be altered .

Syntax:

   ALTER SEQUENCE <SequenceName> [INCREMENT BY <IntegerValue> MINVALUE <IntegerValue> ] ;

Example:

Alter sequence seq increment by 2;

Destroying  a Sequence:

Syntax:

DROP SEQUENCE sequencename;

Example:

drop sequence seq;