🔢 EXPERIMENT-12

Understanding and Implementing SQL Sequences

Instructor: Dr. Mohsin Dar

Position: Assistant Professor, Cloud & Software Operations Cluster (SOCS)

Institution: UPES

Course: Database Systems - MTech First Semester

📚 Introduction to SQL Sequences

What is a Sequence?

A sequence is a database object that generates a sequence of numbers in ascending or descending order. It's commonly used to generate unique identifiers for primary key columns in database tables.

💡 Key Characteristics of Sequences

  • Automatic Numbering: Generates sequential numbers automatically
  • Shared Object: Can be used by multiple tables
  • Efficient: Improves performance by reducing table locking
  • Configurable: Can be set to increment or decrement by any value

Common Use Cases

⚠️ Important Notes

  • Sequences are not tied to any specific table
  • Sequence values can have gaps (due to rollbacks or caching)
  • Sequences can be shared across multiple users without causing conflicts

1️⃣ Creating a Basic Sequence

Creating EMPID_SEQ Sequence

Let's create a sequence named EMPID_SEQ that starts at 100 and increments by 1.

SQL 
CREATE SEQUENCE empid_seq
    START WITH 100
    INCREMENT BY 1;

💡 Sequence Parameters

  • START WITH: The first value in the sequence (default: 1)
  • INCREMENT BY: The step value (can be positive or negative)
  • MINVALUE: The minimum value (default: 1 or -10^26)
  • MAXVALUE: The maximum value (default: 10^27 or -1)
  • CYCLE | NOCYCLE: Whether to restart after reaching max/min
  • CACHE: Number of sequence values to pre-allocate in memory

Verifying Sequence Creation

To verify that the sequence was created successfully:

SQL (Oracle) 
SELECT sequence_name, min_value, max_value, 
       increment_by, last_number, cache_size
FROM user_sequences
WHERE sequence_name = 'EMPID_SEQ';

2️⃣ Checking Sequence Status

Viewing Current and Next Values

To check the current and next values of a sequence, you can use the following commands:

SQL (Oracle) 
-- Get the next value without consuming it
SELECT empid_seq.NEXTVAL FROM dual;

-- Get the current value (without incrementing)
SELECT empid_seq.CURRVAL FROM dual;

-- View sequence details
SELECT sequence_name, last_number, cache_size, 
       increment_by, min_value, max_value
FROM user_sequences
WHERE sequence_name = 'EMPID_SEQ';
SQL (PostgreSQL) 
-- Get the next value
SELECT nextval('empid_seq');

-- Get the current value
SELECT currval('empid_seq');

-- View sequence details
SELECT * FROM information_schema.sequences 
WHERE sequence_name = 'empid_seq';

💡 NEXTVAL vs CURRVAL

  • NEXTVAL: Returns the next available sequence number and increments the sequence
  • CURRVAL: Returns the current value of the sequence (doesn't increment)
  • Important: You must call NEXTVAL before CURRVAL in a session

3️⃣ Modifying a Sequence

Changing Sequence Properties

Let's modify the EMPID_SEQ to change its cache value to 20 and set the maximum value to 1000.

SQL 
ALTER SEQUENCE empid_seq
    CACHE 20
    MAXVALUE 1000;

Verifying the Changes

Let's verify that our changes were applied successfully:

SQL (Oracle) 
SELECT sequence_name, cache_size, max_value, 
       last_number, increment_by
FROM user_sequences
WHERE sequence_name = 'EMPID_SEQ';

⚠️ Important Notes on ALTER SEQUENCE

  • You must have the ALTER privilege on the sequence
  • You cannot change the START WITH value of an existing sequence
  • Changing the INCREMENT BY value affects only future sequence numbers
  • Be cautious when modifying sequences used by production applications

Common Sequence Modifications

SQL 
-- Change increment value
ALTER SEQUENCE empid_seq INCREMENT BY 5;

-- Enable/disable cycling
ALTER SEQUENCE empid_seq CYCLE;  -- Start over after reaching max
ALTER SEQUENCE empid_seq NOCYCLE;  -- Default, stops at max

-- Restart sequence (Oracle 12c+)
ALTER SEQUENCE empid_seq RESTART START WITH 1;

4️⃣ Using Sequences with Tables

Creating a Table with Sequence-Generated IDs

Let's create an EMPLOYEES table that uses our sequence for the employee_id column.

SQL 
CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(100),
    hire_date     DATE DEFAULT SYSDATE
);

Inserting Data Using the Sequence

Now let's insert some records using our sequence:

SQL 
-- Insert a single record
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (empid_seq.NEXTVAL, 'John', 'Doe', 'john.doe@example.com');

-- Insert multiple records
INSERT ALL
    INTO employees (employee_id, first_name, last_name, email)
        VALUES (empid_seq.NEXTVAL, 'Jane', 'Smith', 'jane.smith@example.com')
    INTO employees (employee_id, first_name, last_name, email)
        VALUES (empid_seq.NEXTVAL, 'Robert', 'Johnson', 'robert.j@example.com')
SELECT 1 FROM dual;

Using Sequence as Default Value (Oracle 12c+)

In Oracle 12c and later, you can set a sequence as the default value for a column:

SQL (Oracle 12c+) 
CREATE TABLE employees (
    employee_id   NUMBER DEFAULT empid_seq.NEXTVAL PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(100),
    hire_date     DATE DEFAULT SYSDATE
);

-- Now you can insert without specifying employee_id
INSERT INTO employees (first_name, last_name, email)
VALUES ('Alice', 'Williams', 'alice.w@example.com');

💡 Sequence Gaps

Sequence values may have gaps in the following scenarios:

  • When a transaction that generated sequence numbers is rolled back
  • When the database crashes and recovers (cached sequence values are lost)
  • When sequence values are explicitly set or reset

This is normal behavior and generally not a problem for most applications.

5️⃣ Dropping and Creating a Reverse Sequence

Dropping a Sequence

Let's drop the EMPID_SEQ sequence we created earlier:

SQL 
DROP SEQUENCE empid_seq;

Creating a Reverse Sequence

Now, let's create a new sequence called REVERSE that counts down from 10000 to 1000 in steps of 5:

SQL 
CREATE SEQUENCE reverse_seq
    START WITH 10000
    INCREMENT BY -5
    MINVALUE 1000
    MAXVALUE 10000
    NOCYCLE;  -- Stop after reaching 1000

Testing the Reverse Sequence

Let's generate some values from our reverse sequence:

SQL 
-- Generate 5 values from the reverse sequence
SELECT reverse_seq.NEXTVAL AS sequence_value
FROM dual
CONNECT BY LEVEL <= 5;

💡 Use Cases for Reverse Sequences

  • Generating temporary reference numbers that decrease over time
  • Creating test data with specific value ranges
  • Implementing countdowns or reverse numbering systems
  • Allocating blocks of numbers in reverse order

Viewing Sequence Metadata

To view all sequences in your schema and their properties:

SQL (Oracle) 
SELECT sequence_name, min_value, max_value, 
       increment_by, cycle_flag, cache_size, last_number
FROM user_sequences
ORDER BY sequence_name;

🧠 Knowledge Check Quiz

Question 1: What is the purpose of a database sequence?

A. To generate a sequence of unique numbers
B. To store a list of values in order
C. To sort query results
D. To create relationships between tables

Question 2: Which SQL statement correctly creates a sequence that starts at 1, increments by 1, and has a maximum value of 1000?

A. CREATE SEQUENCE seq1 MAXVALUE 1000;
B. CREATE SEQUENCE seq1 START 1 INCREMENT 1 MAX 1000;
C. CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 MAXVALUE 1000;
D. CREATE SEQUENCE seq1 BEGIN 1 STEP 1 MAXIMUM 1000;

Question 3: What happens when you call NEXTVAL on a sequence in Oracle?

A. It returns the current sequence value without incrementing it
B. It increments the sequence and returns the new value
C. It resets the sequence to its starting value
D. It returns the last value used in the current session

Question 4: How do you modify an existing sequence to change its increment value to 5?

A. MODIFY SEQUENCE my_sequence INCREMENT BY 5;
B. ALTER SEQUENCE my_sequence INCREMENT BY 5;
C. UPDATE SEQUENCE my_sequence SET INCREMENT = 5;
D. CHANGE SEQUENCE my_sequence INCREMENT 5;

Question 5: What is the purpose of the CACHE clause when creating a sequence?

A. It specifies how many sequence values to store on disk
B. It limits the total number of sequence values that can be generated
C. It specifies how many sequence values to pre-allocate in memory for faster access
D. It determines how many sequence values to skip before starting

📚 Summary & Best Practices

Key Takeaways

Best Practices

When Working with Sequences

  • Use meaningful names for sequences (e.g., tablename_id_seq)
  • Consider using CACHE for better performance in high-concurrency environments
  • Be aware that sequence values may have gaps (this is normal)
  • Use NOCYCLE (the default) for primary key sequences to avoid duplicates
  • Consider using identity columns (in databases that support them) as a simpler alternative

Common Pitfalls to Avoid

⚠️ Watch Out For

  • Calling CURRVAL before NEXTVAL in a session (will cause an error)
  • Assuming sequence values will always be consecutive (they may have gaps)
  • Using sequences in multi-master replication without proper configuration
  • Setting CACHE too high (can lead to large gaps if the database is restarted)

Additional Resources

Slide 1 of 8