SQL CREATE SEQUENCE

1. Introduction

The CREATE SEQUENCE statement in SQL is a versatile tool for generating sequences of numbers. Often, these sequences come in handy for tasks like creating unique row identifiers, managing version numbers, or any scenario requiring an ordered run of numeric values. Unlike auto-increment columns, which are tied to a single table, sequences operate as independent objects and can be shared across multiple tables and applications. This flexibility makes them especially valuable in broader database design.

In essence, the CREATE SEQUENCE command is part of the SQL Data Definition Language (DDL). It allows you to define starting values, increments, min/max limits, cycling behavior, and more. Understanding how to leverage sequences is crucial for building dependable and scalable database solutions. In this article, we’ll explore the CREATE SEQUENCE syntax, dive into its various options, and demonstrate practical examples for different database platforms.

Whether you’re looking to generate unique primary keys or maintain strict version controls, knowing the ins and outs of creating and managing sequences is a cornerstone of good database development. Below, you’ll find a comprehensive discussion of the CREATE SEQUENCE command, its parameters, and usage examples across popular SQL implementations.

2. Basic Syntax and Options

Core Syntax

At a high level, the CREATE SEQUENCE statement involves naming your sequence and then specifying a series of properties that define how it behaves. You begin with the CREATE SEQUENCE keywords followed by your sequence name, and then you can layer in options like START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE, and ORDER. These properties grant granular control over number generation, ensuring the sequence fits your application’s needs.

Most database systems allow you to tailor:

• Initial value
• Increment size (positive or negative)
• Minimum and maximum values
• Cycling vs. non-cycling behavior
• Caching behavior for performance

Though the exact syntax can differ slightly between database vendors, the core concepts remain consistent. Here’s a simple pseudocode example:

CREATE SEQUENCE my_sequence
START WITH 1
INCREMENT BY 1;

This basic sequence begins at 1 and increments by 1. You can refine it with other options—like MINVALUE, MAXVALUE, CYCLE, and CACHE—to handle more advanced needs.

Common Options

• START WITH sets the initial value returned by the sequence.
• INCREMENT BY specifies how much the sequence value changes between consecutive calls—this can be positive (ascending) or negative (descending).
• MINVALUE / MAXVALUE configure the lower and upper bounds of the sequence.
• CYCLE allows the sequence to “wrap around” once it reaches its limit, whereas NO CYCLE halts further generation and typically raises an error.
• CACHE speeds things up by pre-allocating a set of numbers in memory, although any unused cached values could be lost if the database crashes.
• ORDER (where supported) ensures sequence values are produced in the precise order requested, which can be helpful for certain concurrency or logging scenarios.

Another noteworthy feature in some systems is the OWNED BY clause. When a sequence is associated with a specific table column in this way, dropping that table or column will also remove the sequence automatically. It’s a good way to avoid leftover or “orphaned” sequences.

3. Data Types and Limits

Integer Types

When creating a sequence, you can specify a data type that sets the allowable range of generated values. Common choices include TINYINT, SMALLINT, INT/INTEGER, or BIGINT. In larger or enterprise-scale applications, BIGINT is often the default pick to prevent overflow. You can also use exact numeric types (e.g., DECIMAL, NUMERIC) with a scale of zero, if you need highly specific ranges.

If you don’t explicitly declare a type, many systems default to BIGINT, offering the broadest numeric range. However, be mindful of storage requirements and potential overflow. For instance, an INT-based sequence has different bounds than one built on BIGINT. The key takeaway: choose a type that aligns with the largest value your application might ever need.

Maximum and Minimum Values

Unless overridden by MAXVALUE and MINVALUE, a sequence will typically default to the bounds of its data type. Ascending sequences often default to a minimum of 1, while descending sequences might default to a max of -1. You can override these defaults, but note that START WITH sets the initial generated value, whereas MINVALUE and MAXVALUE dictate the cycle boundaries (if you enable CYCLE).

Be sure to keep your INCREMENT BY setting compatible with the min/max bounds. For instance, if you’re incrementing positively, MINVALUE should logically be lower than MAXVALUE. Conversely, for a negatively incrementing sequence, MAXVALUE must be less than or equal to MINVALUE. Failure to observe these rules can lead to sequence errors or unpredictable behavior.

4. Practical Examples

Basic Sequence Creation

Creating a basic sequence is straightforward. Let’s say you want a sequence named customer_id_seq starting at 1 and incrementing by 1:

CREATE SEQUENCE customer_id_seq
START WITH 1
INCREMENT BY 1;

You’d then use the sequence in an INSERT statement with something like NEXT VALUE FOR (exact syntax varies by database). This method ensures each new customer record gets a unique identifier without manually tracking the last ID used.

If you’d like to space out IDs in steps of 10, you might do:

CREATE SEQUENCE order_id_seq
START WITH 100
INCREMENT BY 10;

This approach helps when your business logic calls for non-consecutive IDs—maybe to reserve future blocks of numbers or simply to make IDs more distinctive.

Using Sequences with Tables

Sequences shine when generating primary keys for tables. For instance:

CREATE SEQUENCE product_id_seq
START WITH 1000
INCREMENT BY 1;
 
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255)
);
 
INSERT INTO products (product_id, product_name)
VALUES (NEXT VALUE FOR product_id_seq, 'Laptop');

Here, product_id_seq handles unique product IDs. You can even apply the same sequence to multiple tables if you want a single, globally unique range of IDs across your database. It’s this independence from a specific table that makes sequences a compelling choice in complex designs.

5. Advanced Sequence Features

Cycling Sequences

By default, a sequence stops when it hits its max or min value. With CYCLE, you can wrap around so that once the upper (or lower) bound is reached, the sequence resets to the opposite bound. For example:

CREATE SEQUENCE code_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE;

This setup generates values from 1 to 100, then cycles back to 1. It’s particularly handy if you have a constrained range of codes you want to reuse. Be sure your application logic tolerates this recycling, since it can lead to duplicates if handled improperly.

Caching and Performance

When the CACHE option is enabled, the database pre-allocates a batch of future sequence values in memory, which substantially boosts performance in high-traffic environments. For example, CACHE 20 means 20 values sit ready in memory. If the database crashes before they’re used, those “reserved” numbers are lost, resulting in gaps. If absolute continuity is critical, consider NO CACHE, but expect a performance trade-off.

6. Sequence Management

Modifying Sequences

If your requirements change, you can adjust sequence settings using ALTER SEQUENCE. This might include tweaking the increment value, bounds, or caching behavior. Keep in mind that changes don’t always take effect immediately if there are already cached values. Also, you usually can’t alter the data type after creation; if you need a different type, you’ll likely have to drop and recreate the sequence.

Dropping Sequences

Should you no longer need a sequence, you can remove it via DROP SEQUENCE sequence_name. This is irreversible, so confirm that the sequence isn’t still in use. If your database supports OWNED BY and you’ve tied the sequence to a specific table column, dropping that table or column will automatically remove the sequence. Otherwise, it remains until explicitly dropped.

7. Security and Permissions

Required Privileges

In most systems, you’ll need the CREATE SEQUENCE privilege to create a sequence in your own schema, and potentially CREATE ANY SEQUENCE if you’re dealing with someone else’s schema. To use a sequence, a user typically needs USAGE rights, and to modify or drop it, they need ALTER or DROP privileges, respectively. Restricting these permissions helps maintain security and prevent accidental or malicious changes.

Auditing

Keeping track of who creates, alters, or drops a sequence can be crucial for compliance or troubleshooting. Many databases offer auditing features to log these actions, making it easier to spot suspicious activity or errors. Reviewing these logs periodically is a good best practice, particularly in environments with strict security and governance requirements.

8. Differences Across Database Systems

MariaDB

MariaDB’s CREATE SEQUENCE closely aligns with general SQL standards but includes some Oracle-compatible clauses like NOMAXVALUE and NOMINVALUE. It also supports the AS keyword for data type specification. You can use NEXT VALUE FOR to fetch the next sequence number, and SETVAL to reset it. Additionally, MariaDB offers atomic DDL for CREATE SEQUENCE, ensuring the entire operation is either committed or rolled back as one unit.

Snowflake

Snowflake’s version of CREATE SEQUENCE is simpler, employing keywords like START, INCREMENT, and optional clauses such as MINVALUE, MAXVALUE, and CYCLE. However, it doesn’t provide a CACHE feature or an ORDER guarantee in the same sense as some other systems. Gaps can occur naturally, and numbers aren’t necessarily contiguous, reflecting Snowflake’s cloud-native design[^2].

PostgreSQL

PostgreSQL supports common clauses (INCREMENT BY, MINVALUE, MAXVALUE, START WITH, CACHE, and CYCLE) and introduces helpful features like OWNED BY. You can create temporary sequences that last only for the session’s lifetime, and you can use functions like nextval, currval, and setval to manipulate them. Identity columns also piggyback on sequences, further simplifying auto-generated IDs.

SQL Server

SQL Server’s syntax is similar, offering START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, and CACHE. It uses NEXT VALUE FOR to retrieve the next value, and you can manage multiple integer and numeric types. SQL Server also provides the stored procedure sp_sequence_get_range for bulk fetching of sequence numbers, plus a metadata view (sys.sequences) for deeper insights[^3].

Oracle

Oracle supports robust sequence features, including INCREMENT BY, START WITH, MINVALUE, MAXVALUE, CYCLE, NOCYCLE, CACHE, NOCACHE, ORDER, and NOORDER. Oracle uses CURRVAL and NEXTVAL to access sequence values, and it allows for specialized sharing clauses in multitenant environments. Caching is strongly recommended in RAC (Real Application Clusters) setups to mitigate performance bottlenecks.

9. Key Learnings for Liam

The CREATE SEQUENCE statement in SQL is a useful tool for automatically generating unique numeric values, often employed as primary keys or other identifiers. By customizing aspects such as the starting value, increment, and cache size, users can fine-tune the performance and behavior of these sequences to suit various needs—like generating version numbers or handling IDs across multiple tables.

As of the time of writing this article, Liam ERD does not inspect the contents of a CREATE SEQUENCE statement, but understanding how this feature works can help you prepare for possible future enhancements. Sequence-based approaches can streamline schema management, boost performance, and offer greater consistency compared to other identifier methods. They can also raise security considerations, reminding you to manage access rights and ensure thorough auditing.

Learning Resource: This content is designed to help Liam users learn and grow their skills. For the most current information, please check our official documentation and vendor-specific resources.

GitHub - liam-hq/liam: Automatically generates beautiful and easy-to-read ER diagrams from your database.

github.com