ИДЕНТИФИКАЦИЯ SQL-сервера

В этом руководстве вы узнаете, как использовать свойство SQL Server IDENTITY для создания столбца идентификаторов для таблицы.

Введение в столбец IDENTITY SQL Server

Чтобы создать столбец идентификаторов для таблицы, используйте свойство IDENTITY следующим образом:

IDENTITY[(seed,increment)]

В этом синтаксисе:

• Начальное значение — это значение первой строки, загруженной в таблицу.
• Приращение — это инкрементное значение, добавляемое к значению идентичности предыдущей строки.

Значение по умолчанию для seed и increment равно 1, т.е.(1,1). Это означает, что первая строка будет иметь значение один, вторая строка будет иметь значение 2 и т.д.

Если вы хотите, чтобы значение столбца идентификаторов первой строки было равно 10, а инкрементное значение — 10, вы можете использовать следующий синтаксис:

IDENTITY(10,10)

В SQL Server каждая таблица имеет один и только один столбец идентификаторов. Обычно это столбец первичного ключа таблицы.

Пример столбца IDENTITY SQL Server

Давайте создадим новую схему с именем hr для практики:

CREATE SCHEMA hr;

Следующий оператор создает новую таблицу, используя свойство IDENTITY для столбца персонального идентификационного номера:

CREATE TABLE hr.person(
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);

Сначала вставьте новую строку в таблицу персон:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('John','Doe', 'M');

Выход:

Вывод показывает, что первая строка была загружена со значением единицы в столбце person_id.

Во-вторых, вставьте еще одну строку в таблицу персон:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jane','Doe','F');

Выход:

Вывод показывает, что во второй строке столбца person_id содержится значение два.

Повторное использование значений идентичности

SQL Server не использует повторно значения идентификаторов. Если вы вставляете строку в столбец идентификаторов, а оператор вставки завершается неудачей или откатывается, то значение идентификатора теряется и не будет сгенерировано снова. Это приводит к пробелам в столбце идентификаторов.

Рассмотрим следующий пример:

Сначала создайте еще две таблицы в схеме hr с именами position и person_position:

CREATE TABLE hr.position(
    position_id INT IDENTITY(1, 1) PRIMARY KEY,
    position_name VARCHAR(255) NOT NULL,
);
CREATE TABLE hr.person_position(
    person_id INT,
    position_id INT,
    PRIMARY KEY(person_id, position_id),
    FOREIGN KEY(person_id) REFERENCES hr.person(person_id),
    FOREIGN KEY(position_id) REFERENCES hr. POSITION(position_id)
);

Во-вторых, добавьте нового человека и назначьте ему должность, вставив новую строку в таблицу person_position:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new person
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Joan','Smith','F');
        -- assign the person a position
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH
    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;
GO

В этом примере первый оператор вставки выполнен успешно. Однако второй не выполнен из-за отсутствия позиции с идентификатором один в таблице позиций. Из-за ошибки вся транзакция была откачена.

Поскольку первый оператор INSERT использовал значение идентификатора три, и транзакция была откачена, следующим значением идентификатора будет четыре, как показано в следующем операторе:

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');

Вывод заявления:

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

Чтобы сбросить счетчик идентификаторов, используйте команду управления DBCC CHECKIDENT:

DBCC CHECKIDENT('[TableName]', RESEED, 0);
GO

Например:

Сначала удалите все строки из таблицы hr.person:

DELETE FROM hr.person;

Во-вторых, сбросьте счетчик идентификатора на ноль:

DBCC CHECKIDENT('hr.person', RESEED, 0);
GO

Выход:

Checking identity information: current identity value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Выходные данные показывают, что текущее значение идентичности равно 4. Значение сбрасывается до нуля.

В-третьих, вставьте новую строку в таблицу hr.person:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jhoan','Smith','F');

Выход:

person_id
-----------
1
(1 row affected)

Значение person_id равно 1.

Краткое содержание

• Используйте свойство SQL Server IDENTITY для создания столбца идентификаторов для таблицы.