> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# CREATE SEQUENCE
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `CREATE SEQUENCE` statement creates a new sequence in a database. Use a sequence to auto-increment integers in a table.
The \`\` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
## Considerations
* Using a sequence is slower than auto-generating unique IDs with the `gen_random_uuid()`, `uuid_v4()` or `unique_rowid()` built-in functions. Incrementing a sequence requires a write to persistent storage, whereas auto-generating a unique ID does not. Therefore, use auto-generated unique IDs unless an incremental sequence is preferred or required. For more information, see Unique ID best practices.
* A column that uses a sequence can have a gap in the sequence values if a transaction advances the sequence and is then rolled back. Sequence updates are committed immediately and aren't rolled back along with their containing transaction. This is done to avoid blocking concurrent transactions that use the same sequence.
* We discourage indexing on sequential keys. If a table **must** be indexed on sequential keys, use hash-sharded indexes. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hot spots and improving write performance on sequentially-keyed indexes at a small cost to read performance.
* By default, you cannot create sequences that are owned by columns in tables in other databases. You can enable such sequence creation by setting the `sql.cross_db_sequence_owners.enabled` cluster setting to `true`.
## Required privileges
The user must have the `CREATE` privilege on the parent database.
## Synopsis
## Parameters
| Parameter | Description |
| ------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `seq_name` | The name of the sequence to be created, which must be unique within its database and follow the identifier rules. When the parent database is not set as the default, the name must be formatted as `database.seq_name`. |
| `INCREMENT` | The value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.
**Default:** `1` |
| `MINVALUE` | The minimum value of the sequence. Default values apply if not specified or if you enter `NO MINVALUE`.
**Default for ascending:** `1`
**Default for descending:** `MININT` |
| `MAXVALUE` | The maximum value of the sequence. Default values apply if not specified or if you enter `NO MAXVALUE`.
**Default for ascending:** `MAXINT`
**Default for descending:** `-1` |
| `START [WITH]` | The first value of the sequence.
**Default for ascending:** `1`
**Default for descending:** `-1` |
| `RESTART [WITH]` | Sets `nextval` to the specified number, or back to the original `START` value. |
| `NO CYCLE` | All sequences are set to `NO CYCLE` and the sequence will not wrap. |
| `CACHE` | The number of sequence values to cache in memory for reuse in the session. A cache size of `1` means that there is no cache, and cache sizes of less than `1` are not valid.
**Default:** `1` (sequences are not cached by default) |
| `OWNED BY column_name` | Associates the sequence to a particular column. If that column or its parent table is dropped, the sequence will also be dropped.
Specifying an owner column with `OWNED BY` replaces any existing owner column on the sequence. To remove existing column ownership on the sequence and make the column free-standing, specify `OWNED BY NONE`.
**Default:** `NONE` |
| `opt_temp` | Defines the sequence as a session-scoped temporary sequence. For more information, see [Temporary sequences](#temporary-sequences). |
## Sequence functions
CockroachDB supports the following SQL sequence functions:
* `nextval('seq_name')`
* `currval('seq_name')`
* `lastval()`
* `setval('seq_name', value, is_called)`
## Temporary sequences
CockroachDB supports session-scoped temporary sequences. Unlike persistent sequences, temporary sequences can only be accessed from the session in which they were created, and they are dropped at the end of the session. You can create temporary sequences on both persistent tables and temporary tables.
**This feature is in preview** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
Temporary tables must be enabled in order to use temporary sequences. By default, temporary tables are disabled in CockroachDB. To enable temporary tables, set the `experimental_enable_temp_tables` session variable to `on`.
### Details
* Temporary sequences are automatically dropped at the end of the session.
* A temporary sequence can only be accessed from the session in which it was created.
* Temporary sequences persist across transactions in the same session.
* Temporary sequences cannot be converted to persistent sequences.
Like temporary tables, temporary sequences are not in the `public` schema. Instead, when you create the first temporary table, view, or sequence for a session, CockroachDB generates a single temporary schema (`pg_temp_
### Usage
To create a temporary sequence, add `TEMP`/`TEMPORARY` to a `CREATE SEQUENCE` statement.
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET experimental_enable_temp_tables=on;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TEMP SEQUENCE temp_seq START 1 INCREMENT 1;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE temp_seq;
```
```
table_name | create_statement
-------------+--------------------------------------------------------------------------------------------
temp_seq | CREATE TEMP SEQUENCE temp_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)
```
## Examples
### Create a sequence with default settings
In this example, we create a sequence with default settings.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SEQUENCE customer_seq;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE customer_seq;
```
```
table_name | create_statement
---------------+-------------------------------------------------------------------------------------------
customer_seq | CREATE SEQUENCE customer_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)
```
### Use a sequence when creating a table
In this example, we create a table, using the `nextval()` function for a default value, with the `customer_seq` sequence as its input:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE customers (
uid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
rownum INT DEFAULT nextval('customer_seq'),
name STRING
);
```
Inserting into this table with an `INSERT` statement that relies on default values will call `nextval`, which increments the sequence.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO customers (name) VALUES ('Max'), ('Alice');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM customers;
```
```
uid | rownum | name
---------------------------------------+--------+--------
1c7f5b79-88c4-49ec-b40b-6098d28bb822 | 2 | Alice
7ce844af-6a3f-4c52-ba07-25623f345804 | 1 | Max
(2 rows)
```
### View the current value of a sequence
To view the current value without incrementing the sequence, use:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM customer_seq;
```
```
last_value | log_cnt | is_called
-------------+---------+------------
2 | 0 | true
(1 row)
```
If a value has been obtained from the sequence in the current session, you can also use the `currval('seq_name')` function to get that most recently obtained value:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT currval('customer_seq');
```
```
currval
-----------
2
(1 row)
```
### Set the next value of a sequence
In this example, we're going to change the next value of `customer_seq` using the `setval()` function. Currently, the next value will be `3` (i.e., `2` + `INCREMENT 1`). We will change the next value to `5`.
You cannot set a value outside the `MAXVALUE` or `MINVALUE` of the sequence.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT setval('customer_seq', 5, false);
```
```
setval
----------
5
(1 row)
```
The `setval('seq_name', value, is_called)` function in CockroachDB SQL mimics the `setval()` function in PostgreSQL, but it does not store the `is_called` flag. Instead, it sets the value to `val - increment` for `false` or `val` for `true`.
Let's add another record to the table to check that the new record adheres to the new next value.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO customers (name) VALUES ('Sam');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM customers;
```
```
uid | rownum | name
---------------------------------------+--------+--------
19ffe03d-5eac-4a2f-8aa8-1569b998aa44 | 5 | Sam
1c7f5b79-88c4-49ec-b40b-6098d28bb822 | 2 | Alice
7ce844af-6a3f-4c52-ba07-25623f345804 | 1 | Max
(3 rows)
```
### Create a sequence with user-defined settings
In this example, we create a sequence that starts at -1 and descends in increments of 2.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE desc_customer_list;
```
```
table_name | create_statement
---------------------+-----------------------------------------------------------------------------------------------------
desc_customer_list | CREATE SEQUENCE desc_customer_list MINVALUE -9223372036854775808 MAXVALUE -1 INCREMENT -2 START -1
(1 row)
```
### List all sequences
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SEQUENCES;
```
```
sequence_schema | sequence_name
--------------------------------+---------------------
public | customer_seq
public | desc_customer_list
pg_temp_1603124728816183000_1 | temp_seq
(3 rows)
```
### Cache sequence values in memory
For improved performance, use the `CACHE` keyword to cache sequence values in memory.
For example, to cache 10 sequence values in memory:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SEQUENCE customer_seq_cached CACHE 10;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE customer_seq_cached;
```
```
table_name | create_statement
----------------------+------------------------------------------------------------------------------------------------------------------
customer_seq_cached | CREATE SEQUENCE public.customer_seq_cached MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1 CACHE 10
(1 row)
```
## See also
* `ALTER SEQUENCE`
* `DROP SEQUENCE`
* `SHOW CREATE`
* `SHOW SEQUENCES`
* Functions and Operators
* SQL Statements
* Online Schema Changes