SQL-Ex blog

CREATE TABLE [dbo].[TestIntIdentity](

  [cpuID] [int] IDENTITY(1,1) NOT NULL,

  [logTime] [datetime] NOT NULL DEFAULT GetDate()

) ON [PRIMARY];

Свойство IDENTITY может использоваться со всеми целочисленными типами данных за исключением типа bit или типа данных decimal. Итак, сколько строк вы можете вставить в таблицу? Это зависит от используемого типа данных.

• Максимальное значение BIGINT составляет 9,223,372,036,854,775,807


• Максимальное значение INT составляет 2,147,483,647


• Максимальное значение SMALLINT составляет 32,767


• Максимальное значение TINYINT составляет 255

Что произойдет, когда номера закончатся? Давайте рассмотрим пример с использованием SMALLINT.

-- SMALLINT включает 32767 значений

CREATE TABLE [dbo].[TestSmallIntIdentity](

 [cpuID] [smallint] IDENTITY(1,1) NOT NULL,

 [logTime] [datetime] NOT NULL DEFAULT GetDate()

) ON [PRIMARY];

GO

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT * FROM TestSmallIntIdentity;

SELECT count(*) FROM TestSmallIntIdentity;

GO

К этому моменту у нас в таблице находится одна строка. Давайте теперь заполним её почти полностью.

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

GO 32765

-- Выполняем его 32765 раз

SELECT * FROM TestSmallIntIdentity;

SELECT count(*) FROM TestSmallIntIdentity;

GO

Помните, что указывая число после оператора GO, мы задаем количество выполнений предыдущего оператора. В данном случае оператор insert выполняется 32765 раз, с учетом одного предыдущего оператора мы получим 32766 строк в нижеприведенной таблице.



Давайте теперь взглянем на эту таблицу, используя отчет Identity Column Usage Report из проекта Database Health Reports.



Теперь главный вопрос. В таблице имеется 32766 элементов, мы знаем, что может содержаться 32767, что произойдет, когда мы вставим еще 2?

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT count(*) FROM TestSmallIntIdentity;

GO

Это срабатывает, и счет становится 32767, что дальше. Вставим еще одну строку...

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT count(*) FROM TestSmallIntIdentity;

GO

Это сообщение SQL Server говорит о том, что столбец identity заполнен.
Ошибка арифметического переполнения при конвертации IDENTITY к типу данных smallint.
Произошло арифметическое переполнение.

Как это исправить?

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

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

1. Создать другую таблицу со столбцом IDENTITY целого типа большего размера, в данном случае BIGINT или INT.


2. Затем выбрать все строки из этой таблицы в новую таблицу, исключая первичный ключ.


3. Удалить исходную таблицу.


4. Переименовать новую таблицу, дав ей имя исходной.

Решение является более сложным, если имеются вешние ключи. Вам потребуется также удалить ограничение внешнего ключа, включить INDENTITY_INSERT, чтобы сделать вставку, выключить INDENTITY_INSERT, затем создать заново внешние ключи. Вот последовательность действий:

1. Создать другую таблицу с целым типом данных большего размера для столбца IDENTITY, в данном случае BIGINT или INT.


2. Отключить все внешние ключи, связанные с исходной таблицей.


3. Включить identity_insert для новой таблицы.


4. Затем выбрать все строки из исходной таблицы в новую таблицу, включая столбец identity.


5. Удалить исходную таблицу.


6. Переименовать новую таблицу, дав ей имя исходной таблицы.


7. Выключить IDENTITY_INSERT для новой таблицы.


8. Повторно включить все внешние ключи.

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

Как этого избежать?

Есть два варианта при изначальном создании таблицы.

• Использовать целочисленный тип данных большего размера.


• Начинать identity с наименьшего значения для данного типа данных.


• Для SQL Server 2012 вы можете рассмотреть возможность использовать объект SEQUENCE вместо IDENTITY.

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

Второй вариант установки начального минимального значения, доступного для последовательности.

• Минимальное значение для BIGINT - -9,223,372,036,854,775,808


• Минимальное значение для INT - -2,147,483,648


• Минимальное значение для SMALLINT - -32,768


• Минимальное значение для TINYINT - 0

Действительно, ничего нельзя добавить к TINYINT, поскольку его минимальное значение равно 0, но для трех других типов данных вы удваиваете ёмкость ваших значений identity, устанавливая их начальное значение минимальным, а не 1.

Давайте рассмотрим следующий пример, установив начальное значение в -32768 вместо 1.

-- SMALLINT должен содержать значения от -32768 до 32767

CREATE TABLE [dbo].[TestSmallIntIdentity2](

[cpuID] [smallint] IDENTITY(-32768,1) NOT NULL,

[logTime] [datetime] NOT NULL DEFAULT GetDate()

) ON [PRIMARY];

GO

INSERT INTO TestSmallIntIdentity2 DEFAULT VALUES;

SELECT * FROM TestSmallIntIdentity2;

SELECT count(*) FROM TestSmallIntIdentity2;

GO

После вставки одной строки увидим следующий вывод:



Видно, что первое вставленное значение identity равно -32768. Что произойдет, если вставить 37268 значений.

INSERT INTO TestSmallIntIdentity2 DEFAULT VALUES;

GO 32768

-- Выполнить 32,768 раз

-- посмотреть последние вставленные

SELECT TOP 10 *

FROM TestSmallIntIdentity2

ORDER BY cpuID DESC;

SELECT count(*) FROM TestSmallIntIdentity2;

Получаем следующий результат:



Где можно увидеть, что после вставки 32769 строк мы получаем значение IDENTITY 0, а не переполнение.

Давайте теперь посмотрим на ту же таблицу, используя отчет Identity Column Usage Report из проекта Database Health Reports.

Выводы

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