Identity sql что это

Identity sql что это

При создании столбцов в T-SQL мы можем использовать ряд атрибутов, ряд которых являются ограничениями. Рассмотрим эти атрибуты.

PRIMARY KEY

С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.

CREATE TABLE Customers ( Id INT PRIMARY KEY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )

Первичный ключ уникально идентифицирует строку в таблице. В качестве первичного ключа необязательно должны выступать столбцы с типом int, они могут представлять любой другой тип.

Установка первичного ключа на уровне таблицы:

CREATE TABLE Customers ( Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), PRIMARY KEY(Id) )

Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:

CREATE TABLE OrderLines ( OrderId INT, ProductId INT, Quantity INT, Price MONEY, PRIMARY KEY(OrderId, ProductId) )

Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.

IDENTITY

Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )

Также можно использовать полную форму атрибута:

IDENTITY(seed, increment)

Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:

IDENTITY(1, 1)

То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу. Но мы можем это поведение переопределить. Например:

Id INT IDENTITY (2, 3)

В данном случае отсчет начнется с 2, а значение каждой последующей записи будет увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая — 5, третья — 8 и т.д.

Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.

UNIQUE

Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE .

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )

В данном случае столбцы, которые представляют электронный адрес и телефон, будут иметь уникальные значения. И мы не сможем добавить в таблицу две строки, у которых значения для этих столбцов будет совпадать.

Также мы можем определить этот атрибут на уровне таблицы:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), UNIQUE(Email, Phone) )

NULL и NOT NULL

Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL . Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа — в этом случае по умолчанию столбец имеет значение NOT NULL.

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )

DEFAULT

Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE );

Здесь для столбца Age предусмотрено значение по умолчанию 18.

CHECK

Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE CHECK(Email !=''), Phone VARCHAR(20) UNIQUE CHECK(Phone !='') );

Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND . Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).

Также с помощью CHECK можно создать ограничение в целом для таблицы:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE, CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !='')) )

Оператор CONSTRAINT. Установка имени ограничений.

С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, DEFAULT, CHECK.

Имена ограничений можно задать на уровне столбцов. Они указываются после CONSTRAINT перед атрибутами:

CREATE TABLE Customers ( Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18 CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE, Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE )

Ограничения могут носить произвольные названия, но, как правило, для применяются следующие префиксы:

• "PK_" - для PRIMARY KEY
• "FK_" - для FOREIGN KEY
• "CK_" - для CHECK
• "UQ_" - для UNIQUE
• "DF_" - для DEFAULT

В принципе необязательно задавать имена ограничений, при установке соответствующих атрибутов SQL Server автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.

И также можно задать все имена ограничений через атрибуты таблицы:

CREATE TABLE Customers ( Id INT IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30), Phone VARCHAR(20), CONSTRAINT PK_Customer_Id PRIMARY KEY (Id), CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), CONSTRAINT UQ_Customer_Email UNIQUE (Email), CONSTRAINT UQ_Customer_Phone UNIQUE (Phone) )

SQL-Ex blog

При проектировании таблицы базы данных может потребоваться столбец, который заполнялся бы различными числами при вставке каждой строки. Столбец identity может оказаться хорошим способом для автоматического заполнения числового столбца всякий раз, когда вставляется строка. В этой статье я буду обсуждать, что представляет собой столбец identity, и как он работает.

Что такое столбец identity в SQL Server?

Столбец identity - это числовой столбец в таблице, который автоматически получает целое значение, когда вставляется строка. Столбцы identity часто определяются как integer, но они также могут быть объявлены как bigint, smallint, tinyint, или numeric и decimal, если задан масштаб 0. Столбец identity также не может шифроваться с помощью симметричного ключа, но может с помощью прозрачного шифрования данных (Transparent Data Encryption - TDE). Кроме того, определения столбцов identity не должно допускать значений NULL. Одним из недостатков при использовании столбца identity является то, что в таблице может быть только один столбец identity. Если более одного числового поля в таблице должны заполняться автоматически, обратите внимание на объект sequence (последовательность), который в данной статье рассматриваться не будет.

Значения автоматически генерируются для каждой вставляемой строки на основе свойства seed (начальное значение) и increment (приращение) столбца identity. При определении столбца identity используется следующий синтаксис:

IDENTITY [ (seed , increment) ]
Seed - это первое значение, загружаемое в таблицу, а increment добавляется к предыдущему значению identity, создавая следующее значение последовательности. Должны быть указаны оба значения seed и increment , если вы хотите изменить значения, принимаемые по умолчанию. Если эти значения не указываются, принимаются значения по умолчанию, равные 1.

Определение столбца identity в операторе CREATE TABLE

При проектировании таблицы большинство архитекторов данных создают макет, поэтому первым столбцом в таблице является столбец identity. На самом деле это только стандартная практика, а не требование к столбцу identity. Любой столбец в таблице может быть столбцом identity, но такой столбец может быть в таблице только один. В скрипте 1 создается новая таблица с именем Widget, которая содержит столбец identity.

Скрипт 1: Создание таблицы со столбцом identity

CREATE TABLE Widget 
( 
WidgetID int identity(1,1) not null, 
WidgetName varchar(100) not null, 
WidgetDesc varchar(200) not null 
);

WidgetID является столбцом identity, с начальным значением 1 и приращением 1.

Начальное значение определяет значение identity для первой строки, вставляемой в таблицу. Значение приращения используется для определения значения identity для последующих строк, вставляемых в таблицу. Для каждой строки, вставляемой после первой строки, значение приращения добавляется к текущему значению identity для определения значения identity новой добавляемой строки. Текущим значением является целое значение столбца identity последней вставленной строки в таблицу. Чтобы посмотреть как это работает, выполните скрипт 2.

Скрипт2: Код вставки и вывода трех строк, добавленных в таблицу Widget

INSERT INTO Widget VALUES 
('thingamajig','A jig you cannot remember'), 
('doodad','A hair style you cannot remember'), 
('whatchamacallit', 'A thing for which you cannot remember'); 
SELECT * FROM Widget;

При выполнении скрипта 2 будет получен следующий вывод:

В скрипте 2 во вновь созданную таблицу вставляются три строки. Скрипт предоставляет значения только для столбцов WidgetName и WidgetDesc, но не для столбца WidgetID. Значение для столбца WidgetID для первой вставленной строки определяется значением seed в операторе CREATE TABLE, который приведен в скрипте 1. Значение 2 столбца WidgetID для строки с WidgetName doodad было получено добавлением приращения 1 к последнему вставленному значению identity. Значение 3 столбца WidgetID для строки с WidgetName whatchamacallit получило свое значение добавлением 1 к значению identity, использованному для второй вставленной строки.

Помните, что начальное значение и приращение не обязаны быть равными по 1; они могут быть любыми подходящими для таблицы значениями. Например, таблица может использовать начальное значение 1000 и приращение 10, как я сделал для столбца WidgetID.

Скрипт 3: Использование различных значений seed и increment

CREATE TABLE DifferentSeedIncrement 
( 
ID int identity(1000,10), 
A varchar(100), 
B varchar(200) 
);

Столбец ID в скрипте 3 не имеет определения свойства NOT NULL в операторе CREATE TABLE, как я делал это для столбца identity в скрипте 1. Требование not null для столбца можно опустить, поскольку за сценой ядро базы данных автоматически добавляет свойство NOT NULL для любого столбца identity при его создании.

Я оставляю вам выполнение кода в скрипте 3 и вставку нескольких строк в таблицу DifferentSeedIncrement. Тогда вы сами сможете увидеть генерацию значений ID для каждой новой строки, вставляемой в таблицу DifferentSeedIncrement, и как таблица определена для SQL Server.

Уникальность столбца identity

Создание столбца identity в таблице не означает, что значение identity будет уникальным. Причина, по которой значения столбца identity могут не быть уникальными, состоит в том, что SQL Server позволяет вставлять значения identity вручную, а также начальное значение может быть сброшено. Я буду обсуждать вставку значений identity, а также сброс начального значения в следующей статье. Документация SQL Server ясно утверждает, что уникальность может быть наложена с помощью первичного ключа, ограничения уникальности или уникального индекса. Следовательно, гарантия, чтобы столбец identity содержал только уникальные значения, должна быть обеспечена один из вышеупомянутых объектов.

Идентификация столбцов и их определений в базе данных

Существует много способов идентифицировать столбцы identity и их определения в базе данных. Один из них - использовать браузер объектов SQL Server, хотя столбец identity не может быть обнаружен в простом отображении столбцов в таблице, как показано на рис.1.

Рис.1: Отображение определений столбцов для таблиц, созданных скриптами 1 и 3

Для выяснения того, какой столбец действительно является столбцом identity, необходимо посмотреть свойства столбца. Для этого выполните щелчок правой кнопкой на Object Explorer, а затем на пункте Properties в выпадающем контекстном меню. На рис.2 показаны свойства столбца WidgetID в таблице Widget.

Рис.2: Свойства столбца dbo.Widget.WidgetId

Если свойство Identity имеет значение True, то этот столбец является столбцом Identity. Также показаны начальное значение и приращение.

Использование свойств браузера объектов для идентификации столбцов Identity в базе данных с множеством таблиц может потребовать времени. Другим методом для вывода всех столбцов Identity в базе данных является использование представления sys.identity_column, как показано в коде T-SQL скрипта 4.

Скрипт 4: Вывод всех значений Identity в базе данных

SELECT 
OBJECT_SCHEMA_NAME(tables.object_id, db_id()) 
AS SchemaName, 
tables.name As TableName, 
identity_columns.name as ColumnName, 
identity_columns.seed_value, 
identity_columns.increment_value, 
identity_columns.last_value 
FROM sys.tables tables 
JOIN sys.identity_columns identity_columns 
ON tables.object_id=identity_columns.object_id 
GO

Скрипт 4 возвращает такой результат:

Обратите внимание, что столбец last_value для значения DifferentSeedIncrement столбца TableName имеет значение NULL. Это означает, что никаких строк не было вставлено в эту таблицу, поэтому LastValue (последнее значение) не имеет значения.

Добавление столбца identity в существующую таблицу

Существующий столбец нельзя изменить, чтобы он стал столбцом identity, но можно добавить новый столбец identity в существующую таблицу. Чтобы показать, как это может быть сделано, выполните код в скрипте 5. Это скрипт создает новую таблицу, добавляет две строки, а затем меняет таблицу, чтобы добавить новый столбец identity.

Скрипт 5: Добавление столбца identity

CREATE TABLE Invoices 
( 
InvoiceDate date, 
InvoiceNumber varchar(100), 
PayTo varchar (100) 
); 
INSERT INTO Invoices VALUES 
(getdate(), 'GL_0001', 'Greg Larsen'), 
(getdate(), 'GL_0002', 'Greg Larsen'); 
-- Добавить столбец Identity 
ALTER TABLE Invoices 
ADD InvoiceID int identity; 
-- Просмотр строк 
SELECT * FROM Invoices;

Результат выполнения скрипта:

На рисунке видно, что добавлен новый столбец InvoiceID, это столбец был автоматически заполнен значениями identity для всех существующих строк.

Изменение существующей таблицы для определения столбца identity

Как уже говорилось, SQL Server не позволяет использовать команду ALTER TABLE/ALTER COLUMN для непосредственного преобразования существующего столбца в столбец identity. Однако есть вариант модификации существующего столбца таблицы, чтобы он стал столбцом identity. Следующий пример демонстрирует вариант, который использует рабочую таблицу для изменения столбца в существующей таблице на столбец identity.

Чтобы выполнить модификацию существующего столбца в столбец identity, скрипт использует команду ALTER TABLE … SWITCH. Опция SWITCH была добавлена в оператор ALTER TABLE в SQL Server 2005 как часть функции секционирования. Код T-SQL в скрипте 6 использует временную таблицу и опцию SWITCH для поддержки преобразования существующего столбца в столбец identity.

Скрипт 6: Преобразование существующего столбца в столбец identity

DROP TABLE Invoices -- очищаем от предыдущего примера 
GO 
-- Шаг 1: Создание таблицы Invoices и наполнение её данными 
CREATE TABLE Invoices 
( 
InvoiceID int NOT NULL, 
InvoiceDate date, 
InvoiceNumber varchar(100), 
PayTo varchar (100) 
); 
INSERT INTO Invoices VALUES 
(1, getdate(), 'GL_0001', 'Greg Larsen'), 
(2, getdate(), 'GL_0003', 'Greg Larsen'); 
-- Шаг 2: создание временной рабочей таблицы с той же схемой, но имеющей столбец identity 
CREATE TABLE Invoices2 
( 
InvoiceID int identity(1,1), 
InvoiceDate date, 
InvoiceNumber varchar(100), 
PayTo varchar (100) 
); 
-- Шаг 3: Переключение таблиц, удаление исходной и переименование 
ALTER TABLE Invoices SWITCH TO Invoices2; 
-- удаление исходной таблицы 
DROP TABLE Invoices; 
-- Переименование временной таблицы в имя исходной таблицы 
EXEC sp_rename 'Invoices2','Invoices'; 
-- Шаг 4: Обновление текущего значения seed для новой таблицы Invoices 
DBCC CHECKIDENT('Invoices');

Скрипт 6 проходит 4 шага, чтобы преобразовать существующий столбец в столбец identity. Ниже перечислено то, что нужно иметь в виду при использовании этого метода для добавления столбца identity в существующую таблицу:

Чтобы использовать опцию SWITCH в операторе ALTER TABLE, столбец в исходной таблице, изменяемый на столбец identity, не должен допускать NULL-значений. Если он допускает NULL, то операции switch будут неудачны.

Не забудьте сбросить начальное значение столбца identity новой таблицы с помощью команды DBCC CHECKIDENT. Если этого не сделать, то следующая вставленная строка будет использовать исходной значение seed, и могут возникать дубликаты значений identity, если столбец не является первичным ключом или не имеет ограничения уникальности, или уникального индекса на столбце identity.

Перед запуском команды ALTER TABLE… SWITCH необходимо удалить все внешние ключи.

Если на исходной таблице существуют индексы, то временная таблица также должна иметь точно те же самые индексы, или оператор switch завершится неудачно.

При выполнении команды ALTER TABLE …SWITCH не должно быть других транзакций, обращающихся к этой таблице. Все новые транзакции не будут запущены пока выполняется операция switch.

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

Повторное заполнение столбца identity

В предыдущем примере я выполнял повторное заполнение столбца identity с помощью оператора DBCC CHECKIDENT. Имеются другие причины, почему столбец DBCC CHECKIDENT требует повторного заполнения, например, когда несколько строк были неправильно вставлены в таблицу, или ошибочные строки были удалены. Ошибочно вставленные строки вызывают возрастание текущего значения identity для каждой добавленной строки. Таким образом, после удаления всех неправильных строк следующая строка будет использовать следующее значение identity и оставит большой зазор в значениях identity. Если допущена эта ошибка, то повторное заполнение значений identity гарантирует отсутствие больших зазоров пропущенных значений identity.

Для повторного заполнения значений identity в таблице используется команда DBCC CHECKIDENT. Эта команда имеет следующий синтаксис:

DBCC CHECKIDENT 
( 
имя_таблицы 
[, < NORESEED | < RESEED [, новое_значение_seed ] >> ] 
) 
[ WITH NO_INFOMSGS ]

Параметр имя_таблицы - это имя таблицы, которая содержит спецификацию identity. Таблица должна содержать столбец identity, в противном случае при выполнении команды DBCC CHECKINDENT возникает ошибка. Если никакие опции не указаны в этой команде, текущее значение identity будет сброшено к максимальному существующему значению в столбце identity.

Опция NORESEED предписывает не изменять начальное значение (seed). Эта опция полезна для определения текущего и максимального значения identity. Если текущее и максимальное значения различны, то значение identity должно быть повторно заполнено.

Когда текущее значение identity меньше максимального, или существует большой зазор в значениях identity, для сброса текущего значения identity может использоваться опция RESEED. Опция RESEED может быть указана с новое_значение_seed или без него. Если новое_значение_seed не указано, текущее значение identity будет установлено в максимальное значение, записанное в столбце identity указанной таблицы.

Скрипт 6 показывает, как повторно заполнить значение столбца identity с помощью команды DBCC CHECKINDENT без использования опции RESEED. В скрипте 7 показан код T-SQL, который устанавливает текущее значение seed в 2 используя опцию RESEED.

Скрипт 7: Использование опции RESEED

DBCC CHECKIDENT('Invoices',RESEED,2);

Будьте осторожны с использованием опции RESEED с новым значением seed. SQL Server не заботится о том, какое значение используется для нового начального значения. Если новое значение установлено в значение, которое меньше максимального значения seed в таблице, могут возникнуть дублирующиеся значения identity.

Столбец identity в SQL Server

Столбец identity будет автоматически генерировать и заполнять значениями числовой столбец всякий раз, когда новая строка вставляется в таблицу. Столбец identity использует текущее значение seed, а также значение инкремента для генерации нового значения identity для каждой вставляемой строки. Здесь рассмотрены основные аспекты использования столбца identity. В следующей статье будут изучены некоторые нюансы столбцов identity.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Identity sql что это

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

Таблицы базы данных могут содержать столбцы, в которые при добавлении новой записи в таблицу будут автоматически записываться порядковые номера. Такие столбцы называются идентификаторами или счетчиками и обычно используются в качестве первичных ключей. Таблица может содержать только один столбец идентификатор.

Столбцы идентификаторы ведут себя следующим образом.

• Добавление пустого значения в столбец идентификатор приведет к добавлению в данном столбце следующего по счету номера в новой строке.
• При добавлении новой записи с явно указанным значением в столбце идентификаторе произойдет одно из двух. Если запись с таким значением уже в таблице существует, генерируется сообщение об ошибке, так как значения в этом столбце должны быть уникальны. Если строка с таким значением отсутствует, новое значение станет новым максимумом последовательности.
• Значения в автоматически генерируемых последовательностях являются строго возрастающими и не могут использоваться повторно. Если текущее максимальное значение составляет 143 и запись, содержащая это значение, была удалена, следующее генерируемое значение будет 144.
• Диапазон значений столбца идентификатора подчиняется законам диапазона значений для типа, с которым объявлен данный столбец. Например, столбец типа TINYINT UNSIGNED будет иметь максимальное значение 255. По достижению этого значения счетчиком, будет выдаваться сообщение об ошибке "повторение ключа" ("duplicate error").

MS SQL Server

В MS SQL Server столбец идентификатор создается с помощью атрибута IDENTITY в описании столбца:

column_definition ::=
column_name [ NULL | NOT NULL ][DEFAULT constant_expression ] [ IDENTITY [( seed ,increment ) ]]

При добавлении в таблицу новой строки компонент Database Engine формирует для этого столбца последовательное уникальное значение.

Для каждой таблицы можно создать только один столбец идентификаторов. При создании столбца можно указать как начальное значение (параметр seed в описании столбца), так и приращение (параметр increment в описании столбца). Если эти параметры не указаны, применяются значения по умолчанию (1,1).

MySQL Server

MySQL Server поле-счетчик создается с помощью атрибута AUTO_INCREMENТ в описании столбца:

column_defenition::=
col_name data_type [NOT NULL | NULL] [DEFAULT default_value] AUTO_INCREMENT

Столбец должен быть объявлен как NOT NULL и UNSIGNED, а также быть либо первичным ключом либо уникальным индксом.

Существует возможность явного задания начального значения последовательности в момент создания таблицы. Пример создания столбца AUTO_INCREMENT, начальное значение последовательности которой равно 1000000:

CREATE TABLE my_table
(id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY)
AUTOINCREMENT = 1000000

SQL-Ex blog

В предыдущей статье я рассмотрел основную информацию о столбцах identity в SQL Server. В этой статье обсуждаются более продвинутые вещи. В частности, как вручную вставить значения identity, как избежать дублирующих значений identity, как изменить значение точки отсчета identity (seed), как идентифицировать функции и переменные и многое другое.

Вставка значений identity вручную

По умолчанию невозможно вставить вручную значение непосредственно в столбец identity, но значения identity могут быть введены вручную, если включить сессионную опцию. Чтобы выяснить, что произойдет при попытке вставить значение identity без включения свойства Identity Insert, выполните код в листинге 1.

Листинг 1: Попытка вставить значение identity

CREATE TABLE Widget(WidgetID INT NOT NULL IDENTITY, 
WidgetName NVARCHAR(50), WidgetDesc NVARCHAR(200)); 
INSERT INTO Widget 
VALUES (110,'MyNewWidget','New widget to test insert');

Вставка значения 110 в столбец identity, наряду со значениями остальных столбцов, в таблицу Widget вернет ошибку, показанную ниже:

В сообщении об ошибке ясно говорится, что вы не можете явно вставлять значение identity, если не указываете список столбцов в операторе INSERT, и свойство IDENTITY_INSERT для таблицы Widget не установлено в ON.

Свойство IDENTITY_INSERT является сессионным, и оно управляет тем, может ли вставляться значение identity или нет. По умолчанию значение этого свойство равно OFF, но оно может быть включено для таблицы Widget с помощью кода в листинге 2.

Листинг 2: Включение свойства IDENTITY_INSERT

SET IDENTITY_INSERT Widget ON;

После включения свойства IDENTITY_INSERT для таблицы Widget можно выполнить код в листинге 3 и не получить ошибки.

Листинг 3: Код со списком столбцов, требуемым для вставки значения identity

INSERT INTO Widget(WidgetID,WidgetName,WidgetDesc) 
VALUES (110,'MyNewWidget','New widget to test insert');

Только одна таблица в сессии может иметь включенным свойство INDENTITY_INSERT в одно и то же время. Если вам потребуется вставить значения identity более чем в одну таблицу, вам сначала нужно выключить свойство INDENTITY_INSERT для первой таблицы, используя код в листинге 4, до включения свойства INDENTITY_INSERT для другой таблицы.

Листинг 4: Выключение сессионного свойства INDENTITY_INSERT

SET IDENTITY_INSERT Widget OFF;

Следует соблюдать осторожность при вставке значений identity вручную. SQL Server не требует от значений identity уникальности. По этой причине вам нужно позаботиться при вставке значений identity вручную, чтобы не вставить значение identity, которое уже существует.

Избежать дублирующих значений identity

Дубликаты значений identity могут возникнуть в таблице при вставке значений identity или повторной установке значения identity. Наличие дубликатов значений identity не обязательно плохо, если нет требования к уникальности этих значений. Если все значения identity должны быть различными, то это требование должно поддерживаться созданием PRIMARY KEY, ограничения UNIQUE или индекса UNIQUE.

Использование функции IDENTITY

SQL Server предоставляет функцию IDENTITY для определения столбца identity при создании новой таблицы с помощью оператора SELECT с предложением INTO. Функция IDENTITY подобна, но не идентична свойству IDENTITY, которое используется в операторах CREATE или ALTER TABLE. Функция IDENTITY может использоваться только в операторе SELECT, содержащем предложение INTO, который создает и заполняет новую таблицу.

Ниже приведен синтаксис функции IDENTITY:

IDENTITY (data_type [ , seed , increment ] ) AS column_name

data-type - допустимый числовой тип данных, который поддерживает целые значения, отличный от bit или decimal.
seed - определяет первое значение identity, которое будет вставлено в таблицу.
increment - целое значение, которое будет прибавляться к значению seed для каждой добавленной строки.
column_name - имя столбца identity, который будет создан в новой таблице.

Для демонстрации работы функции IDENTITY выполните код в листинге 5.

Листинг 5: Использование функции IDENTITY в команде SELECT INTO

USE AdventureWorks2019; 
GO 
SELECT IDENTITY(int, 90000, 1) AS Special_ProductId, 
Name AS Special_Name, 
ProductNumber, 
ListPrice 
INTO Production.SpecialProduct 
FROM Production.Product 
WHERE Name like '%LL Road Frame%Black%'; 
-- Вывод новой таблицы 
SELECT * FROM Production.SpecialProduct;

Вывод кода в листинге 5 показан ниже.

Результаты показывают, что столбец с именем Special_ProductID является столбцом identity, который был создан при помощи функции IDENTITY. Первая строка получила значение seed. Каждое значение identity для последующих строк вычислялось прибавлением значения increment к значению identity текущей вставленной строки.

Иногда вам может потребоваться программным образом выяснить значения seed и increment или последнее вставленное значение в столбец identity. Для получения подобной информации SQL Server предоставляет несколько функций.

Для получения значения seed, вы можете использовать функцию IDENT_SEED. Эта функция использует следующий синтаксис:

IDENT_SEED ( 'table_or_view' )

Если вы меняли значение identity с помощью команды DBCC CHECKIDENT, то эта функция вернет исходное значение seed, назначенное столбцу identity при его начальном создании.

Парная функция с именем IDENT_INCR, которая позволяет получить значение приращения (increment), имеет следующий синтаксис:

IDENT_INCR ( 'table_or_view' )

Чтобы увидеть обе эти функции в действии, выполните код в листинге 6.

Листинг 6: Получение исходных значений seed и increment

SELECT IDENT_SEED('Production.SpecialProduct') AS OriginalSeed, 
IDENT_INCR('Production.SpecialProduct') AS IncrementValue;

Вот результат выполнения этого кода:

Здесь видно, что OriginalSeed и IncrementValue являются теми же, что и аргументы, которые использовались при создании таблицы SpecialProduct в коде из листинга 5.

Вам также может понадобиться знать последнее значение identity, вставленное в таблицу. Обычно это требуется, когда у вас есть две таблицы со связью "родитель-потомок", и дочернюю запись нужно привязать к родительской записи, используя значение identity родительской записи. Есть три различных способа вернуть значение identity последней вставленной записи, которые мы тут рассмотрим: @@IDENTITY, IDENT_CURRENT и SCOPE_IDENTITY.

@@IDENTITY

Системная функция @@IDENTITY возвращает последнее вставленное значение identity. Если последний оператор вставки вставил несколько значений identity, то только последнее значение из них возвращается этой функцией. Если никаких новых значений identity не было вставлено в данной сессии, то функция вернет значений NULL. Если срабатывает триггер на вставку, и этот триггер в свою очередь вставляет строку в таблицу, которая содержит столбец identity, то будет возвращено значение identity, вставленное триггером.

SCOPE_IDENTITY

Функция SCOPE_IDENTITY так же возвращает последнее вставленное значение identity, как и @@IDENTITY, но с одним отличием. Разница состоит в том, что функция SCOPE_IDENTITY возвращает значение identity для последнего оператора INSERT, выполненного в той же сессии и области действия (scope). Напротив, функция @@IDENTITY возвращает последнее вставленное значение независимо от области действия.

Для лучшего понимания того, как влияет область действия на значение identity, возвращаемое этими двумя функциями, выполните код в листинге 7.

Листинг 7: Код, показывающий разницу между SCOPE_IDENTITY и @@IDENTITY

DROP TABLE IF EXISTS TestTable1, TestTable2; 
CREATE TABLE TestTable1( 
ID INT IDENTITY(1,1), 
InsertText1 VARCHAR(100) 
); 
CREATE TABLE TestTable2( 
ID INT IDENTITY(100,100), 
InsertText2 VARCHAR(100) 
); 
GO 
CREATE TRIGGER MyTrigger ON TestTable1 AFTER INSERT AS 
BEGIN 
INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 1'); 
INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 2'); 
END 
GO 
INSERT INTO TestTable1(InsertText1) VALUES ('Original Insert'); 
GO 
-- Возвращаем значения Identity 
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Код в листинге 7 сначала вставляет одну запись в таблицу TestTable1 в текущей области действия, затем еще 2 записи вставляются в таблицу TestTable2 в другой области действия, когда срабатывает триггер. После вставки и срабатывания триггера на вставку выполняется оператор SELECT, чтобы показать значения, возвращаемые функциями @@IDENTITY и SCOPE_IDENTITY(). Вывод показан ниже.

Следовательно, если вы хотите узнать последнее значение identity независимо от области действия, вы можете использовать @@IDENTITY. Если вам нужно знать последнее значение identity, вставленное в текущей области действия, вам нужно использовать функцию SCOPE_IDENTITY(). На представленных результатах видно, что функция @@IDENTITY вернула значение 200. Это произошло потому, что @@IDENTITY возвращает последнее вставленное значение вне зависимости от области действия. Значение identity для второй записи было вставлено в таблицу TestTable2 триггером "после вставки". Функция SCOPE_IDENTITY() вернула значение 1, это значение identity было присвоено, когда запись вставлялась в TextTable1 в той же области действия.

Имейте в виду, что обе функции @@IDENTITY и SCOPE_IDENTITY() возвращают последнее вставленное значение identity, оставляя без внимания таблицу, куда это значение было вставлено. Если вам нужно знать последнее значение identity, вставленное в конкретную таблицу, вам следует использовать функцию IDENT_CURRENT().

IDENT_CURRENT

Функция IDENT_CURRENT() возвращает последнее значение identity, вставленное в конкретную таблицу, вне зависимости от сессии или области действия, когда это было сделано. С помощью функции IDENT_CURRENT() вы можете легко определить последнее значение identity, созданное для конкретной таблицы, как показано в коде листинга 8.

Листинг 8: Определение последних значений identity, вставленных в таблицы TestTable1 и TestTable2

SELECT IDENT_CURRENT('TestTable1') AS IdentityForTestTable1, 
IDENT_CURRENT('TestTable2') AS IdentityForTestTable2;

Выполнение кода в листинге 2 дает следующие результаты:

Функции @@IDENTITY и SCOPE_IDENTITY() не требуют передачи имени таблицы в качестве параметра, поэтому нелегко идентифицировать, из какой таблицы пришло значение identity. Напротив, IDENT_CURRENT() требует передать имя таблицы. Следовательно, если вы хотите знать последнее значение identity, вставленное в конкретную таблицу вне зависимости от сессии и области действия, вам следует обратить внимание на функцию IDENT_CURRENT().

При вставке множества строк в таблицу со столбцом identity не гарантировано, что каждая строка получит последовательные значения в столбце identity. Это может произойти, когда в то же время другие пользователи вставляют строки. Если вам действительно нужны последовательные значения identity, убедитесь, что ваш код использует эксклюзивную блокировку для таблицы или уровень изоляции SERIALIZE.

Вы также можете обнаружить, что значения identity не всегда присваиваются последовательно. Одной из причин этого является откат транзакции. При откате транзакций любые значения identity, которые были отменены, не будут повторно использоваться. Еще одной причиной появления зазоров является способ, которым SQL Server кэширует значения identity в целях повышения производительности.

Кэширование identity для повышения производительности

Чтобы найти следующее значение identity, SQL Server требуются некоторые ресурсы машины, чтобы заглянуть внутрь и найти это значение. Поэтому для оптимизации производительности и экономии ресурсов машины SQL Server кэширует имеющиеся значения identity. Кэшируя имеющиеся значения identity, SQL Server не нужно вычислять следующее доступное значение идентификатора при вставке новой строки.

Кэширование identity было введено в SQL Server 2012. Проблема с кэшированием identity состоит в том, что когда SQL Server неожиданно падает, он теряет значения, сохраняемые во внутреннем кэше. При потере кэшированных значений эти значения identity больше никогда не будут использоваться. Это может привести к созданию зазора в значениях identity.

Новая опция конфигурации базы данных с именем IDENTITY_CACHE была введена в SQL Server 2017, чтобы помочь решить проблему с зазорами, которые могут вызываться кэшированием. Опция IDENTITY_CACHE включена по умолчанию, но может быть выключена (OFF). При выключении опции SQL Server не кэширует значения identity; тем самым они не будут потеряны при крушении или неожиданной остановке SQL Server. Конечно, отключение кэширования identity приведет к ухудшению производительности.

Чтобы проверить установку IDENTITY_CACHE для базы данных, выполните код из листинга 9.

Листинг 9: Вывод установки IDENTITY_CACHE для текущей базы данных

SELECT * FROM sys.database_scoped_configurations 
WHERE NAME = 'IDENTITY_CACHE';

Вывод выполнения кода из листинга 9 на SQL Server 2017 показан ниже.

Видно, что значение IDENTITY_CACHE установлено в 1, что означает, что кэширование identity включено. Чтобы запретить кэширование identity для текущей базы данных, выполните код из листинга 10.

Листинг 10: Выключение кэширования identity

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;

Если вы обнаружите множество зазоров в ваших значениях identity, и это является проблемой, вы можете отключить кэширование identity.

Недостатки столбцов identity

• Только один столбец identity можно определить на таблицу.
• Столбец identity нельзя изменить или удалить после его создания.
• Столбцы identity не являются уникальными по умолчанию. Чтобы обеспечить их уникальность, необходимо определить первичный ключ, ограничение уникальности или уникальный индекс.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись