Ответ

SEQUENCE (например, в PostgreSQL и Oracle) — специальный объект базы данных, который хранит текущее значение и позволяет получать уникальные числа для вставки (обычно — ключей). IDENTITY — свойство поля, автоматически присваивающее следующее по порядку значение при вставке (табличный autoincrement в SQL Server, MySQL, PostgreSQL).

Ключевые отличия:

SEQUENCE — объект вне таблицы, доступен из разных мест, можно получить следующее значение без вставки (nextval / currval). Используется для разных таблиц, может быть нескольких SEQUENCE на одну таблицу.
IDENTITY — часть определения столбца, автоматически увеличивается только при вставке строки.

Пример:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'First Order');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id присвоится автоматически

Проблемы и тонкости:

Если вручную вставлять id, возможно "дырки" или пересечения.
При удалении строк — значения не переиспользуются, возможен разрыв в диапазоне ключей.
В некоторых СУБД (например, MySQL) при откате транзакции autoincrement может "проскочить вперед", оставляя неизменённое значение.
SEQUENCE можно использовать в нескольких таблицах, но это усложняет поддержку уникальности.

Вопрос с подвохом

Что будет, если одновременно два запроса запросят nextval() из одного SEQUENCE? Возможно ли получить неуникальные значения?

Ответ: Нет, SEQUENCE гарантирует уникальность. Каждый запрос получает уникальное значение, даже при одновременном обращении. Однако порядок может не соответствовать порядку вставки из-за параллелизма.

Пример:

-- Первый поток получает nextval=100, второй через мгновение — nextval=101.
-- Даже если потом первый всё откатит, значение 100 уже используется.

История №1

После переноса базы с MySQL на PostgreSQL забыли про отличия SEQUENCE и IDENTITY: попытались вручную вставлять id, что привело к ошибке "duplicate key" при автоматической генерации. Исправили, полностью перенеся управление ключами на SEQUENCE.

История №2

При частичном удалении строк из таблицы с autoincrement однажды понадобилось "плотно" заполнить диапазон id. Из-за неконтролируемого пересоздания новых строк появились неоднозначности — ключи стали неконсистентны с логи бизнес-логики, пришлось перейти на отдельную SEQUENCE для контроля.

История №3

Из-за отсутствия контроля за SEQUENCE в крупной OLTP-системе номер заказа перешагнул 2,147,483,647 и вызвал аварийный сбой в интеграционном API, т.к. он не был готов к значениям bigint. Запрос повторно был реализован с контрольными предельными значениями SEQUENCE.