Изменение колонки в SQL: делаем её идентификационной

Быстрый ответ

Преобразование существующего столбца в SQL Server в столбец с идентификатором включает выполнение нескольких действий:

1. Создание копии таблицы без данных при помощи SELECT INTO с условием WHERE 1=0.
2. Добавление в копию дополнительного столбца IDENTITY.
3. Перенос данных с помощью SET IDENTITY_INSERT, синхронизируя при этом идентификаторы.
4. Удаление исходной таблицы и переименование копии.

Вот пример соответствующего кода:

BEGIN TRANSACTION;

-- Сначала создаем точную копию структуры таблицы
SELECT * INTO NewTable FROM OldTable WHERE 1=0;
ALTER TABLE NewTable ADD NewID INT IDENTITY(1,1);

-- Затем аккуратно переносим данные, сохраняя идентификаторы.
SET IDENTITY_INSERT NewTable ON;
INSERT INTO NewTable (NewID, ...) SELECT OldID, ... FROM OldTable;
SET IDENTITY_INSERT NewTable OFF;

-- Удаляем оригинальную таблицу и заменяем её новой версией.
DROP TABLE OldTable;
EXEC sp_rename 'NewTable', 'OldTable';

COMMIT TRANSACTION;

Стратегия добавления идентификатора

Особенности добавления идентификатора

Добавление столбца с идентификатором требует учесть следующее:

• Существующие данные: Новый IDENTITY будет незаполненным столбцом.
• Непрерывность: Чтобы сохранить последовательность ID, используйте DBCC CHECKIDENT.
• Безопасность: Применение IF EXISTS помогает избежать ошибочных удалений.
• Обновление связей: Не забывайте корректировать внешние ключи, индексы и другие зависимости.

Подробнее об этом расскажет наш спикер на видео

Добавление идентификатора в большие таблицы

Для больших таблиц более эффективным будет метод ALTER TABLE...SWITCH, минимизирующий время простоя:

• Доступность: Таблица остается доступной во время всего процесса.
• Требовательность: Этот подход требует внимательности и аккуратности.

Пример применения операции переключения:

BEGIN TRANSACTION;

-- "Обмен" таблицами делает процесс быстрым и эффективным.
ALTER TABLE OldTable SWITCH TO NewTable;
ALTER TABLE NewTable ADD NewID INT IDENTITY(1,1);

-- Включаем режим вставки идентификаторов.
SET IDENTITY_INSERT NewTable ON;
INSERT INTO NewTable (NewID, ...) SELECT OldID, ... FROM OldTable;
SET IDENTITY_INSERT NewTable OFF;

-- Подчищаем следы – удаляем исходную таблицу и переименовываем новую.
DROP TABLE OldTable;
EXEC sp_rename 'NewTable', 'OldTable';

COMMIT TRANSACTION;

Использование последовательностей вместо идентификатора

В SQL Server объект SEQUENCE может быть предпочтительнее для автоинкремента ключей:

• Гибкость: Одна последовательность может быть использована для нескольких таблиц.
• Предотвращение конфликтов: Последовательности помогают избежать проблем с автоинкрементом в процессе вставки.

Реализация последовательности:

CREATE SEQUENCE Seq_As_Identity
    START WITH 1
    INCREMENT BY 1;

ALTER TABLE MyTable
    ADD NewIDCol INT DEFAULT NEXT VALUE FOR Seq_As_Identity
    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED;

-- Удаляем старый ID, он нам больше не пригодится.
ALTER TABLE MyTable DROP COLUMN OldIDCol;

Визуализация 🚂

До: [🚃, 🚃, 🚃] 
// Строки без идентификаторов ждали своего часа!

ALTER TABLE Train ADD COLUMN Car_ID INT IDENTITY(1,1);

После: [🚃1, 🚃2, 🚃3]
// Теперь каждый вагон обзавелся своим уникальным номером!

Работа с активными таблицами

При работе с активными таблицами необходимо:

• Предотвращать конфликты: Назначение высокого начального ID предотвращает дублирование.
• Обеспечивать последовательность данных: Гарантируйте непрерывность вставок в процессе миграции.

Создание скрипта добавления идентификатора

SQL Management Studio предоставляет возможность автоматической генерации скрипта изменений:

• Откройте конструктор таблиц и внесите нужные изменения.
• Вместо сохранения выберите Generate Change Script.

Финальная очистка после миграции

Завершая переход, необходимо:

• Освободить пространство: Удалить старые элементы.
• Обеспечить консистенцию имён: Использовать sp_rename для переименования.

Полезные материалы

1. ALTER TABLE (Transact-SQL) – SQL Server | Microsoft Learn — полное описание команды ALTER TABLE.
2. IDENTITY (Property) (Transact-SQL) – SQL Server | Microsoft Learn — руководство от Microsoft по свойству IDENTITY.
3. SQL Server: ALTER TABLE Statement — содержательная статья об использовании команды ALTER TABLE.
4. Database Journal: Working with Identity Columns — полезная статья об работе со столбцами идентификатора.