Sequences in SQL

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;