SQL-Ex blog
Заполнение столбца SQL Server последовательным номерами без использования identity
Добавил Sergey Moiseenko on Суббота, 3 сентября. 2022
Проблема
Есть таблица базы данных, которая уже содержит много данных. Необходимо добавить в эту таблицу новый столбец, который имел бы последовательную нумерацию. Помимо добавления столбца, также необходимо заполнить существующие записи инкрементным счетчиком. Какие для этого имеются варианты?
Решение
Первое решение, которое приходит на ум, это добавить столбец 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 = + 1), где мы делаем значение @id и столбец id равными текущему значению @id плюс 1.
DECLARE @id INT
SET @id = 0
UPDATE accounts2
SET @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 /> GO
DECLARE @id INT
SET @id = 0
UPDATE accounts2
SET @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 = + 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 = + 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 /> OPTION (MAXDOP 1)
Заключение
Когда вы создали столбец identity, у вас нет простого способа перенумеровать значения для каждой строки. Подход на основе обновления позволяет это делать по мере необходимости простым выполнением запроса и изменением значений. Этот подход работает для всех версий SQL Server, а вариант с CTE — для SQL Server 2005 и выше.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Вывод своего столбца в SQL Server
Подскажите, пожалуйста, как в SQL можно вывести столбик, включающий в себя заданное количество строк со своим текстом (текст должен быть статическим). Поясню, необходимо чтобы слева от столбца (см. скриншот) появился ещё один столбец в котором были 4 строчки с пояснениями к каждой цифре: 
Заранее благодарен! UPD: Надо что-то наподобие как на скрине ниже, только текст в каждой строчке свой. Тут вывел просто через (SELECT ‘Один’), но таким способом не получается вывести более 1 разного текста, т.е. (SELECT (‘Один’, ‘Два’, ‘Три’, ‘Четыре’)) не работает 
UPD 2: Вот, то, что должно получиться на выходе — нарисовал в пейнте. Проблема в том, что первый столбец надо сделать на SQL. Текст Один, Два, Три, Четыре должен быть в самом запросе. 
Отслеживать
задан 12 июл 2013 в 9:02
528 2 2 золотых знака 11 11 серебряных знаков 25 25 бронзовых знаков
4 ответа 4
Сортировка: Сброс на вариант по умолчанию
Во-первых объясните по-человечески как у вас устроена таблица, желательно в виде SQL. Во-вторых объясните чего вы в конечном итоге хотите добиться, что бы из этой таблицы просто выбирались две строки, это просто:
SELECT column1, clomun2 FROM table LIMIT n; Если вы хотите, что бы у вас значения выбирались из разных таблиц, в этом тоже нет ничего сложного:
SELECT table1.column, table2.column1 FROM table1, table2 И наконец, если вы хотите просто дописать несколько слов на вывод, зачем тут лепить SQL я не знаю.
Добавить в существующую таблицу новый столбец и заполнить данными
Здравствуйте. Подскажите, как к существующей таблице с введенными данными добавить новый столбец и заполнить его информацией?
1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE [dbo].[Сотрудники]( [СотрудникID] [int] IDENTITY(1,1) NOT NULL, [ФИО] [varchar](30) NOT NULL, [Должность] [varchar](20) NOT NULL, [Телефон] [dbo].[phone] NULL, CONSTRAINT [PK_Менеджеры] PRIMARY KEY CLUSTERED ( [СотрудникID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Следует добавить обязательный столбец «ДатаПриемки» и заполнить ее датами.
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Как добавить результаты запроса в существующую таблицу
Как мне добавить результаты запроса в таблицу zakaz в столбец sena_zakaz ? SELECT.
Заполнить NULL столбец данными
Есть таблица из ~100 записей,в которой есть столбец(дата рождения), где все значения=NULL. Как.
Как заполнить новую таблицу данными.
Как заполнить новую таблицу данными, которые есть запрос к другой таблице средствами SQL? Т.е.
Как запросом заполнить таблицу данными из других таблиц?
Доброго времени суток. Попал билет по базам данным. В нем задание 3 таблицы и суть задание.
Регистрация: 24.01.2017
Сообщений: 228
Добавить столбец с помощью ALTER TABLE как nullable;
заполнить данными как нужно;
изменить столбец с помощью ALTER TABLE на NOT NULL
Регистрация: 09.07.2018
Сообщений: 7
Так вот как заполнить с помощью кода, а не графического интерфейса?
Таким образом у меня ошибку выдает:
1 2 3 4
insert into Сотрудники (ДатаПриемки) values ('2018-12-12'), ('2018-12-12'), ('2018-12-12') GO
Регистрация: 24.01.2017
Сообщений: 228
У вас таблица уже создана, а значит надо делать UPDATE а не INSERT.
1 2 3
UPDATE [dbo].[Сотрудники] SET [ДатаПриемки] = '2018-12-12' WHERE [ДатаПриемки] IS NULL
названия колонок и таблиц надо писать in english
Регистрация: 09.07.2018
Сообщений: 7
Добавлено через 9 минут
А чтобы вставить разные значения, а не одну и ту же дату, как можно написать?
А то просто получается, что все значения NULL заполняются одной и той же датой.
P.S. Это просто пример я плохой привел с одинаковыми датами.
4214 / 3054 / 582
Регистрация: 21.01.2011
Сообщений: 13,205
Сообщение от Nekto992 
вставить разные значения, а не одну и ту же дату
Так кто же знает, какие даты тебе нужны?
Регистрация: 09.07.2018
Сообщений: 7
Да любые, просто разобраться хочу
Если писать так,
1 2 3
UPDATE [dbo].[Сотрудники] SET [ДатаПриемки] = '2018-12-12' WHERE [ДатаПриемки] IS NULL
то одна и та же дата заполнит все строки.
А как правильно так чтобы у каждой строки было свое индивидуальное значение.
например:
в 1-й — ‘2018-12-12’
во 2-й — ‘2017-01-05’
в 3-й — ‘2016-03-07’
и т.д.
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
Помогаю со студенческими работами здесь
Как в процедуре создать таблицу и заполнить ее данными из других таблиц, по определенному условию
Подскажите пожалуйста, я новичок в SQL, и не совсем понимаю, как можно создать такую процедуру, в.
Как создать таблицу в SQL не прописывая каждый раз новый столбец в SELECT?
Как создать таблицу в SQL не прописывая каждый раз новый столбец в SELECT? Таблица вот такого.
Добавить столбец в существующую таблицу
Возникла необходимость в рабочей базе добавить столбец "Описание" в таблицу "Клиенты". Надо.
Нужно добавить новый правый столбец в таблицу и присвоить ему заголовок
Пожалуйста, киньте фрагмент кода VBA Excel нужно добавить новый правый столбец в таблицу и.
ALTER TABLE в SQL
Когда вы начнете использовать таблицу после ее создания, вы можете обнаружить, что забыли какой-нибудь столбец или указали неверное имя столбца.
В такой ситуации можно использовать оператор `ALTER TABLE`, чтобы изменить существующую таблицы — с помощью добавления, изменения или удаления столбца в таблице.
Рассмотрим таблицу shippers в нашей базе данных. Ее структура выглядит следующим образом:
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(60) | NO | | NULL | | +--------------+-------------+------+-----+---------+----------------+
Мы будем использовать таблицу shippers во всех дальнейших примерах с ALTER TABLE .
Как добавить новый столбец
Предположим, что нам нужно расширить существующую таблицу shippers , добавив еще один столбец. Давайте разберемся, как это сделать с помощью SQL-команд.
ALTER TABLE имя_таблицы ADD имя_столбца тип_данных ограничения;
Следующий оператор добавляет новый столбец fax в таблицу shippers .
ALTER TABLE shippers ADD fax VARCHAR(20);
Если вы посмотрите на структуру таблицы с помощью команды DESCRIBE shippers; после выполнения приведенной выше команды, то увидите следующее:
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(60) | NO | | NULL | | | fax | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+
Примечание. Если вы хотите добавить NOT NULL -столбец в существующую таблицу, то нужно указать явное значение по умолчанию. Это значение используется для заполнения нового столбца для каждой строки, которая уже существует в таблице.
Примечание. При добавлении нового столбца в таблицу, если не указано ни NULL , ни NOT NULL , столбец обрабатывается так, как если бы было указано NULL .
По умолчанию MySQL добавляет новые столбцы в конец. Если вы хотите добавить новый столбец после определенного столбца, используйте условие AFTER , как показано ниже:
mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;
В MySQL существует еще одно условие — FIRST , которое можно использовать для добавления нового столбца на первое место в таблице. Просто замените AFTER на FIRST в предыдущем примере и тогда столбец fax добавится в начало таблицы shippers .
Как изменить расположение столбца
Если вы уже создали таблицу в MySQL, но вас не устраивает существующее положение столбцов в ней, вы можете изменить его в любое время с помощью такого синтаксиса:
ALTER TABLE имя_таблицы
MODIFY имя_столбца определение_столбца AFTER имя_столбца;
Следующий оператор помещает столбец fax после столбца shipper_name в таблице shippers :
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
Как изменить расположения столбца
Если вы уже создали таблицу в MySQL, но вас не устраивает существующее положение столбцов, его можно изменить в любое время, используя следующий синтаксис:
ALTER TABLE имя_таблицы
MODIFY имя_столбца определение_столбца AFTER имя_столбца;
Следующий оператор помещает столбец fax после столбца shipper_name в таблице shippers :
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
Как добавить ограничения
В текущем виде у таблицы shippers есть одна серьезная проблема. Если вы вставите записи с дублирующимися телефонными номерами, она не помешает вам это сделать, что не очень хорошо, ведь телефонные номера должны быть уникальными.
Это легко исправить, добавив ограничение UNIQUE к столбцу phone . Основной синтаксис для добавления этого ограничения к существующим столбцам таблицы выглядит так:
ALTER TABLE table_name ADD UNIQUE (column_name. );
Следующий оператор добавляет ограничение UNIQUE к столбцу phone .
mysql> ALTER TABLE shippers ADD UNIQUE (phone);
Если вы попытаетесь вставить дубликат телефонного номера после выполнения оператора, то получите ошибку.
Аналогично, если вы создали таблицу без PRIMARY KEY , можно добавить его с помощью следующего выражения:
ALTER TABLE имя_таблицы ADD PRIMARY KEY (имя_столбца. );
А вот этот оператор добавляет ограничение PRIMARY KEY к столбцу shipper_id , если он не определен.
mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);
Как удалить столбец
Базовый синтаксис для удаления столбца из существующей таблицы выглядит следующим образом:
ALTER TABLE имя_таблицы DROP COLUMN имя_столбца;
Следующий оператор удалит наш недавно добавленный столбец fax из таблицы shippers .
mysql> ALTER TABLE shippers DROP COLUMN fax;
После выполнения оператора, структура таблицы будет выглядеть так:
+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(20) | NO | UNI | NULL | | +--------------+-------------+------+-----+---------+----------------+
Как изменить тип данных столбца
В SQL Server можно изменить тип данных столбца с помощью выражения ALTER , как показано ниже:
ALTER TABLE имя_таблицы ALTER COLUMN имя_таблицы новый_тип_данных;
Однако MySQL не поддерживает синтаксис ALTER COLUMN . Там используется альтернативное выражение MODIFY , которое изменяет столбец:
ALTER TABLE имя_таблицы MODIFY имя_столбца новый_тип_данных;
Следующий оператор изменяет текущий тип данных столбца phone в таблице shippers с VARCHAR на CHAR и длину с 20 на 15.
mysql> ALTER TABLE shippers MODIFY phone CHAR(15);
Аналогично можно использовать выражение MODIFY для переключения допущения нулевых значений в столбце таблицы MySQL. Это реализуется при помощи повторного определения столбца и добавления ограничения NULL или NOT NULL в конце, как показано ниже:
mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;
Как переименовать таблицу
Основной синтаксис для переименования существующей таблицы в MySQL выглядит следующим образом:
ALTER TABLE текущее_имя_таблицы RENAME новая_имя_таблицы;
Например, следующий оператор переименует таблицу shippers в shipper .
mysql> ALTER TABLE shippers RENAME shipper;
Такого же результата можно добиться с помощью оператора RENAME TABLE :
mysql> RENAME TABLE shippers TO shipper;
СodeСhick.io — простой и эффективный способ изучения программирования.
2023 © ООО «Алгоритмы и практика»