Sequence in SQL | PART 1

A sequence in SQL, particularly in databases like Oracle, is a database object that generates a series of unique numeric values in a specified order. Sequences are commonly used for automatically generating unique primary key values for tables, ensuring that each value is distinct and follows a sequential order (incremented or decremented).

Key Features of a Sequence:

Unique: Sequences generate unique numbers, making them suitable for auto-incrementing primary keys.

Automatically Incremented: Each time the sequence is accessed (via NEXTVAL), it generates the next value in the defined sequence.

Customizable: You can specify the starting point, increment value, minimum and maximum values, and whether the sequence should cycle after reaching the maximum value.

Not Tied to a Specific Table: Sequences are independent of tables, so they can be used in any table or for other purposes.

Components of a Sequence:

1. START WITH: Defines the starting point of the sequence (e.g., start with 1).

2. INCREMENT BY: Specifies how much the sequence value will increase or decrease (e.g., increment by 1 or by 5).

3. MINVALUE / MAXVALUE: Sets the minimum or maximum limits for the sequence.

4. CYCLE / NOCYCLE: Determines whether the sequence should restart (cycle) after reaching the maximum value or throw an error.

5. CACHE / NOCACHE: Caches sequence values in memory for faster access or generates them without caching.

Syntax for Creating a Sequence:

CREATE SEQUENCE sequence_name
  START WITH initial_value
  INCREMENT BY increment_value
  MINVALUE min_value
  MAXVALUE max_value
  CYCLE/NOCYCLE
  CACHE/NOCACHE;

Example of Creating a Sequence:

CREATE SEQUENCE emp_seq
  START WITH 1        -- Start from 1
  INCREMENT BY 1      -- Increment by 1 for each new value
  MINVALUE 1          -- Minimum value is 1
  MAXVALUE 9999       -- Maximum value is 9999
  NOCYCLE             -- Do not restart after reaching the max value
  CACHE 20;           -- Cache 20 values for performance

Using a Sequence in SQL:

To retrieve the next value of a sequence, you use the NEXTVAL function. You can also use the CURRVAL function to retrieve the current value of the sequence.

Using **NEXTVAL **in an INSERT Statement:

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

This query will insert a new employee and automatically assign a unique employee_id generated by the sequence.

Using CURRVAL:

SELECT emp_seq.CURRVAL FROM dual;

This will return the current value of the sequence, which is the value most recently generated by NEXTVAL in the current session.

Example Scenario:

If you want to auto-generate unique employee IDs for a table of employees, you would:

1. Create a sequence that starts at 1 and increments by 1.

2. Use the sequence's NEXTVAL each time you insert a new employee.

Managing Sequences:

Altering a Sequence: You can modify an existing sequence if needed.

ALTER SEQUENCE emp_seq
INCREMENT BY 2;

Dropping a Sequence: If you no longer need a sequence, you can drop it.

DROP SEQUENCE emp_seq;

Key Points:

Independent of Tables: Sequences are not tied to any specific table, so you can use them in multiple tables if required.

Thread-Safe: Sequences ensure thread safety by guaranteeing that each NEXTVAL call will return a unique value, even in a multi-user environment.

In summary, a sequence in SQL is a powerful tool for generating unique values automatically, commonly used for primary keys and ensuring data integrity across records.