SQL Interview Questions

A sequence in SQL is a database object that generates a sequence of unique numeric values according to specified rules. Sequences are commonly used to generate unique identifiers for rows in tables, particularly for primary key columns. They provide a way to automate the generation of these unique numbers without requiring manual intervention.

Characteristics of Sequences

1. Autonomous: Sequences are independent of tables and can be used by multiple tables.
2. Incremental: Sequences generate numbers in an incremental fashion, based on the specified increment value.
3. Unique Values: Each call to a sequence generates a unique value, ensuring no duplicates.
4. Customizable: Sequences can be customized with various parameters like starting value, increment value, maximum value, and cycling behavior.

Creating a Sequence

To create a sequence, you use the CREATE SEQUENCE statement. This statement allows you to specify various options to define the behavior of the sequence.

Syntax

CREATE SEQUENCE sequence_name
  [START WITH initial_value]
  [INCREMENT BY increment_value]
  [MINVALUE min_value]
  [MAXVALUE max_value]
  [CYCLE | NO CYCLE]
  [CACHE cache_size | NO CACHE];

Example

CREATE SEQUENCE customer_seq
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  NO MAXVALUE
  NO CYCLE
  CACHE 10;

Using a Sequence

Once a sequence is created, you can use it to generate unique values by calling its NEXTVAL or CURRVAL pseudo-columns.

• NEXTVAL: Retrieves the next value in the sequence.
• CURRVAL: Retrieves the current value of the sequence (the last value generated in the current session).

Example: Inserting Data with a Sequence

INSERT INTO customers (customer_id, customer_name)
VALUES (customer_seq.NEXTVAL, 'John Doe');

Example: Retrieving Current Value

SELECT customer_seq.CURRVAL FROM dual;

Parameters and Options

• START WITH: Specifies the starting value of the sequence. Default is 1.
• INCREMENT BY: Specifies the interval between consecutive values. Default is 1.
• MINVALUE: Specifies the minimum value of the sequence. Default is 1.
• MAXVALUE: Specifies the maximum value of the sequence. Default is the maximum value the data type can hold.
• CYCLE: Indicates that the sequence should restart from the minimum value when the maximum value is reached.
• NO CYCLE: Indicates that the sequence should not restart when the maximum value is reached (default).
• CACHE: Specifies how many sequence values should be preallocated and stored in memory for faster access. Default is 20.
• NO CACHE: Indicates that sequence values should not be preallocated.

Modifying a Sequence

You can modify an existing sequence using the ALTER SEQUENCE statement.

Example

ALTER SEQUENCE customer_seq
  INCREMENT BY 2
  MAXVALUE 1000
  CYCLE;

Dropping a Sequence

You can remove a sequence from the database using the DROP SEQUENCE statement.

Example

DROP SEQUENCE customer_seq;

Use Cases for Sequences

1. Primary Keys: Automatically generating unique identifiers for primary key columns.
2. Invoice Numbers: Generating sequential invoice numbers.
3. Order Numbers: Generating unique order numbers for e-commerce transactions.
4. Ticket Numbers: Generating unique ticket numbers for events or support requests.

Advantages of Using Sequences

• Uniqueness: Ensures the generation of unique values, which is crucial for primary keys and other unique identifiers.
• Concurrency: Sequences are designed to handle concurrent access efficiently, making them suitable for multi-user environments.
• Flexibility: Sequences can be customized to fit various use cases and requirements, such as starting value, increment step, and cycling behavior.
• Independence: Being independent of tables, sequences can be reused across multiple tables and applications.

Conclusion

Sequences in SQL are powerful tools for generating unique numeric values, essential for tasks like creating primary keys, order numbers, and other unique identifiers. By understanding how to create, use, modify, and manage sequences, you can leverage their benefits to ensure data integrity and simplify the automation of unique value generation in your database applications.