SQL-Ex blog

Решение

Первое решение, которое приходит на ум, это добавить столбец identity в таблицу, если она еще не имеет такого столбца. Посмотрим на этот подход, а также на то, как сделать это с помощью простого оператора UPDATE.

Использование столбца identity для инкрементирования значения на 1

Для этого примера мы создадим таблицу (для имитации реально существующей таблицы), загрузим туда 100000 записей, а затем изменим структуру таблицы, добавив столбец identity с приращением 1.

CREATE TABLE accounts ( fname VARCHAR(20), lname VARCHAR(20)) 

GO 

INSERT accounts VALUES ('Fred', 'Flintstone') 

GO 100000 

SELECT TOP 10 * FROM accounts 

GO

ALTER TABLE accounts ADD id INT IDENTITY(1,1) 

GO 

SELECT TOP 10 * FROM accounts 

GO

Статистика по времени и вводу/выводу показывает, что было выполнено 23К логических чтений, и все выполнение заняло 48 секунд.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 17 ms.
Table 'accounts'. Scan count 1, logical reads 23751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 6281 ms, elapsed time = 48701 ms.

SQL Server Execution Times:
CPU time = 6281 ms, elapsed time = 48474 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

Использование переменных для обновления и инкрементирования значения на 1

В этом примере мы создаем подобную таблицу, загружаем в неё 100000 записей, после чего изменяем таблицу, добавляя столбец INT и выполняя обновление.

CREATE TABLE accounts2 ( fname VARCHAR(20), lname VARCHAR(20)) 

GO 

INSERT accounts2 VALUES ('Barney', 'Rubble') 

GO 100000 

SELECT TOP 10 * FROM accounts2 

GO

После создания таблицы и загрузки данных мы добавляем в таблицу столбец INT, который не является столбцом identity.

ALTER TABLE accounts2 ADD id INT 

GO 

SELECT TOP 10 * FROM accounts2 

GO 

На этом шаге мы собираемся обновить таблицу и для каждой обновляемой строки мы изменяем переменную на 1, а также обновляем столбец id в таблице. Это видно здесь (SET @id = id = @id + 1), где мы делаем значение @id и столбец id равными текущему значению @id плюс 1.

DECLARE @id INT 

SET @id = 0 

UPDATE accounts2 

SET @id = id = @id + 1 

GO 

SELECT * FROM accounts2 

GO 

Ниже можно увидеть результаты, где каждая запись получает приращение 1.



Статистика по времени и вводу/выводу показывает около 26К логических чтений и 4,8 секунды на выполнение.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 247 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'accounts2'. Scan count 1, logical reads 26384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 4781 ms, elapsed time = 4856 ms.

(100000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

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

Использование переменных для обновления с значением приращения 10

Пусть теперь нам нужен инкремент 10, а не 1. Мы можем выполнить обновление, как мы делали это выше, но использовать значение 10 для приращения id каждой записи.

Для чистоты эксперимента я сначала сделаю значением столбца id NULL для всех записей, а затем выполню обновление.

UPDATE accounts2 SET id = NULL

GO 

DECLARE @id INT 

SET @id = 0 

UPDATE accounts2 

SET @id = id = @id + 10

GO 

SELECT * FROM accounts2 

GO

Ниже видно, что значения id теперь инкрементируются на 10, а не на 1. Вы можете использовать в качестве инкремента любое желаемое значение.

Предупреждение: возможно появление дубликатов

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

-- использование MAXDOP = 1 - автор Steve Ash

-- обновление выполняется при использовании только одного процессора, 

-- чтобы избежать проблемы с дубликатами

DECLARE @id INT 

SET @id = 0 

UPDATE accounts2 

SET @id = id = @id + 1 

OPTION ( MAXDOP 1 )

GO 

-- использование уровня изоляции SERIALIZABLE - автор Tillman Dickson

-- это означает, что другие транзакции не могут модифицировать данные, которые читаются

-- текущей транзакций, пока текущая транзакция не завершится.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

  DECLARE @id INT 

  SET @id = 0

  UPDATE accounts2  

  SET @id = id = @id + 1

COMMIT TRANSACTION 

Другой подход к обновлению последовательных значений

Вот еще один предложенный подход.

-- обновление строк с помощью CTE - автор Ervin Steckl 

;WITH a AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn, id

FROM accounts2

) 

UPDATE a SET id=rn

OPTION (MAXDOP 1)

Заключение

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