Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Sequences and Identity

SQL Server Usage

Automatic enumeration functions and columns are common with relational database management systems and are often used for generating surrogate keys.

SQL Server provides several features that support automatic generation of monotonously increasing value generators.

Identity

The IDENTITY property is probably the most widely used means of generating surrogate primary keys in SQL Server applications. Each table may have a single numeric column assigned as an IDENTITY, using the CREATE TABLE or ALTER TABLE DDL statements. You can explicitly specify a starting value and increment.

The IDENTITY value is generated as part of the transaction that inserts table rows. Applications can obtain IDENTITY values using the @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT functions.

You can manage IDENTITY columns using the DBCC CHECKIDENT command, which provides functionality for reseeding and altering properties.

Syntax

IDENTITY [(<Seed Value>, <Increment Value>)]

Examples

The following example creates a table with an IDENTITY column.

CREATE TABLE MyTABLE
(
  Col1 INT NOT NULL
  PRIMARY KEY NONCLUSTERED IDENTITY(1,1),
  Col2 VARCHAR(20) NOT NULL
);

The following example inserts a row and retrieve the generated IDENTITY value.

DECLARE @LastIdent INT;
INSERT INTO MyTable(Col2)
VALUES('SomeString');
SET @LastIdent = SCOPE_IDENTITY()

The following example creates a table with a non-key IDENTITY column and an increment of 10.

CREATE TABLE MyTABLE
(
  Col1 VARCHAR(20) NOT NULL
  PRIMARY KEY,
  Col2 INT NOT NULL
  IDENTITY(1,10),
);

The following example creates a table with a compound primary key including an IDENTITY column.

CREATE TABLE MyTABLE
(
  Col1 VARCHAR(20) NOT NULL,
  Col2 INT NOT NULL
  IDENTITY(1,10),
  PRIMARY KEY (Col1, Col2)
);

SEQUENCE

Sequences are objects that are independent of a particular table or column and are defined using the CREATE SEQUENCE DDL statement. You can manage sequences using the ALTER SEQUENCE statement. Multiple tables and multiple columns from the same table may use the values from one or more SEQUENCE objects.

You can retrieve a value from a SEQUENCE object using the NEXT VALUE FOR function. For example, a SEQUENCE value can be used as a default value for a surrogate key column.

SEQUENCE objects provide several advantages over IDENTITY columns:

Syntax

CREATE SEQUENCE <Sequence Name> [AS <Integer Data Type> ]
START WITH <Seed Value>
INCREMENT BY <Increment Value>;

ALTER SEQUENCE <Sequence Name>
RESTART [WITH <Reseed Value>]
INCREMENT BY <New Increment Value>;

Examples

The following example creates sequence and uses it for a primary key default.

CREATE SEQUENCE MySequence AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE MyTable
(
  Col1 INT NOT NULL
  PRIMARY KEY NONCLUSTERED DEFAULT (NEXT VALUE FOR MySequence),
  Col2 VARCHAR(20) NULL
);

INSERT MyTable (Col1, Col2) VALUES (DEFAULT, 'cde'), (DEFAULT, 'xyz');

SELECT * FROM MyTable;

Col1  Col2
1     cde
2     xyz

Identity

SQL Server provides two sequential generation functions: IDENTITY and NEWSEQUENTIALID.

You can use the IDENTITY function only in a SELECT …​ INTO statement to insert IDENTITY column values into a new table.

The NEWSEQUNTIALID function generates a hexadecimal GUID, which is an integer. While the NEWID function generates a random GUID, the NEWSEQUENTIALID function guarantees that every GUID created is greater (in numeric value) than any other GUID previously generated by the same function on the same server since the operating system restart.

You can use NEWSEQUENTIALID only with DEFAULT constraints associated with columns having a UNIQUEIDENTIFIER data type.

Syntax

IDENTITY (<Data Type> [, <Seed Value>, <Increment Value>]) [AS <Alias>]

NEWSEQUENTIALID()

Examples

The following example uses the IDENTITY function as surrogate key for a new table based on an existing table.

CREATE TABLE MySourceTable
(
  Col1 INT NOT NULL PRIMARY KEY,
  Col2 VARCHAR(10) NOT NULL,
  Col3 VARCHAR(10) NOT NULL
);

INSERT INTO MySourceTable
VALUES
(12, 'String12', 'String12'),
(25, 'String25', 'String25'),
(95, 'String95', 'String95');

SELECT IDENTITY(INT, 100, 1) AS SurrogateKey,
  Col1,
  Col2,
  Col3
INTO MyNewTable
FROM MySourceTable
ORDER BY Col1 DESC;

SELECT *
FROM MyNewTable;

SurrogateKey  Col1  Col2      Col3
100           95    String95  String95
101           25    String25  String25
102           12    String12  String12

The following example uses NEWSEQUENTIALID as a surrogate key for a new table.

CREATE TABLE MyTable
(
  Col1 UNIQUEIDENTIFIER NOT NULL
  PRIMARY KEY NONCLUSTERED DEFAULT NEWSEQUENTIALID()
);

INSERT INTO MyTable
DEFAULT VALUES;

SELECT *
FROM MyTable;

Col1

9CC01320-C5AA-E811-8440-305B3A017068

For more information, see Sequence Numbers and CREATE TABLE (Transact-SQL) IDENTITY (Property) in the SQL Server documentation.

PostgreSQL Usage

The PostgreSQL CREATE SEQUENCE command is mostly compatible with the SQL Server CREATE SEQUENCE command. Sequences in PostgreSQL serve the same purpose as in SQL Server; they generate numeric identifiers automatically. A sequence object is owned by the user that created it.

Sequence Parameters

Syntax

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]

Most SQL Server CREATE SEQUENCE parameters are compatible with PostgreSQL.

Examples

The following example creates a sequence.

CREATE SEQUENCE SEQ_1 START WITH 100
  INCREMENT BY 1 MAXVALUE 99999999999 CACHE 20 NO CYCLE;

The following example drops a sequence.

DROP SEQUENCE SEQ_1;

View sequences created in the current schema and sequence specifications.

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES;
OR
\ds

The following example uses a PostgreSQL sequence as part of a CREATE TABLE and an INSERT statement.

CREATE TABLE SEQ_TST
(COL1 NUMERIC DEFAULT NEXTVAL('SEQ_1') PRIMARY KEY, COL2 VARCHAR(30));
INSERT INTO SEQ_TST (COL2) VALUES('A');
SELECT * FROM SEQ_TST;

col1  col2
100   A

Use the OWNED BY parameter to associate the sequence with a table.

CREATE SEQUENCE SEQ_1 START WITH 100 INCREMENT BY 1 OWNED BY SEQ_TST.COL1;

Query the current value of a sequence.

SELECT CURRVAL('SEQ_1);

Manually increment a sequence value according to the INCREMENT BY value.

SELECT NEXTVAL('SEQ_1');
OR
SELECT SETVAL('SEQ_1', 200);

Alter an existing sequence.

ALTER SEQUENCE SEQ_1 MAXVALUE 1000000;

IDENTITY Usage

Starting from PostgreSQL 10, there is a new option called identity columns which is similar to the SERIAL data type but more SQL standard compliant. The identity columns are slightly more compatible compared to SQL Server identity columns.

To create a table with identity columns, use the following statement:

CREATE TABLE emps (
  emp_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  emp_name VARCHAR(35) NOT NULL);

INSERT INTO emps (emp_name) VALUES ('Robert');
INSERT INTO emps (emp_id, emp_name) VALUES (DEFAULT, 'Brian');

SELECT * FROM emps;

col1  col2
1     Robert
2     Brian

In PostgreSQL, for SERIAL and IDENTITY, you can insert any value, so long as it won’t violate the primary key constraint. If the value violates the primary key constraint and you use the identity column sequence value again, the following error might be raised:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "emps_iden_pkey"
Detail: Key (emp_id)=(2) already exists.

SERIAL Usage

In PostgreSQL, you can create a sequence similar to the IDENTITY property supported by identity columns. When you create a new table, the sequence is created through the SERIAL pseudo-type. Other types from the same family are SMALLSERIAL and BIGSERIAL.

By assigning a SERIAL type to a column during table creation, PostgreSQL creates a sequence using the default configuration and adds a NOT NULL constraint to the column. The newly created sequence behaves like a regular sequence (incremented by 1) and no composite SERIAL option.

The following example uses SERIAL sequence.

CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10));

INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A');
SELECT * FROM SERIAL_SEQ_TST;

col1  col2
1     A

\ds

Schema  Name                     Type      Owner
public  serial_seq_tst_col1_seq  sequence  pg_tst_db

The following example uses the PostgreSQL SERIAL pseudo-type with a sequence that is created implicitly.

CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10));

\ds

Schema  Name                     Type      Owner
public  serial_seq_tst_col1_seq  sequence  pg_tst_db

ALTER SEQUENCE SERIAL_SEQ_TST_COL1_SEQ RESTART WITH 100 INCREMENT BY 10;
INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A');
INSERT INTO SERIAL_SEQ_TST(COL1, COL2) VALUES(DEFAULT, 'B');
SELECT * FROM SERIAL_SEQ_TST;

col1  col2
100   A
110   B

Use the ALTER SEQUENCE command to change the default sequence configuration in a SERIAL column.

Create a table with a SERIAL column that uses increments of 10:

CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10));

ALTER SEQUENCE serial_seq_tst_col1_seq INCREMENT BY 10;

Create a table with a compound primary key including a SERIAL column:

CREATE TABLE SERIAL_SEQ_TST
(COL1 SERIAL, COL2 VARCHAR(10), PRIMARY key (COL1,COL2));

Summary

The following table identifies similarities, differences, and key migration considerations.

For more information, see CREATE SEQUENCE, Sequence Manipulation Functions, Numeric Types, and CREATE TABLE in the PostgreSQL documentation.