Можно ли заставить SQL Server не пропускать значения в автоинкрементируемых полях?

@Scandal777 · Регистрация: 26.09.2014

Студворк — интернет-сервис помощи студентам

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

Допустим есть какая-нибудь мифическая таблица:

T-SQL
1
2
3
4
5
CREATE TABLE SomeTable
(
    LINK INT PRIMARY KEY INDENTITY,
    Name VARCHAR(100) NULL
)

Затем я добавил туда 3 записи. LINK'и соответственно у них 1, 2 и 3. Затем я удалил записи с LINK'ами 2 и 3. Добавляю еще одну запись, но у нее LINK = 4. Можно ли как-то заставить sql server не пропускать номера? Мне хотелось бы, чтобы следующая созданная запись была с линком 2, а не 4.

Если такого сделать нельзя, то не могли бы вы подсказать как это можно сделать другими способами и можно ли вообще?

@invm · 24.04.2016, 17:20

Какая большая беда случится, если будут пропуски в значениях?

@Scandal777 · 24.04.2016, 17:53 **[ТС]**

Сообщение от invm

Какая большая беда случится, если будут пропуски в значениях?

Такое требование. Мне тоже оно кажется глупым, но как говорится: "клиент всегда прав".

@invm · 24.04.2016, 18:06

Если было 1, 2, 3. Удалили 2. Каким должен быть следующий номер?

@Scandal777 · 24.04.2016, 18:18 **[ТС]**

Было 1, 2, 3. Удалили 2. С Identity след.номером будет 4, а нужен - 2!

@invm · 24.04.2016, 20:20

Сообщение от Scandal777

Было 1, 2, 3. Удалили 2. С Identity след.номером будет 4, а нужен - 2!

Дурдом

Штатных средств для такого изврата нету.
Могу предложить:
1. Не удалять строки физически, а помечать их как удаленные. Соответственно при вставке находить такие помеченные строки и использовать их повторно.
2. Искать запросом разрывы нумерации и вычислять доступные значения. Гораздо менее эффективно, чем п.1 и потребует уровня изоляции транзакций serializable.

@Claster · 25.04.2016, 09:22

Не лучший вариант как-то так, но этот пример для установки значения что бы шли по порядку

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE #SomeTable
(
    LINK INT PRIMARY KEY IDENTITY,
    Name VARCHAR(100) NULL
);
 
INSERT INTO #SomeTable (NAME)
SELECT 'A'
UNION 
SELECT 'B'
UNION 
SELECT 'C'
 
DELETE FROM #SomeTable
WHERE LINK = 3
 
DECLARE @maxID int = (SELECT MAX(LINK) FROM #SomeTable)
 
DBCC CHECKIDENT ('#SomeTable', RESEED, @maxID); --Установить значение для IDENTITY
 
INSERT INTO #SomeTable (NAME)
SELECT 'C'

@pincet · 25.04.2016, 12:57

Claster,
вот как-то если сразу 100 клиентов попытаются вставить, что будет?
ЗЫ Было когда-то давно и у меня ощущение, что только один клиент всегда работает с записью.....

@Claster · 25.04.2016, 13:11

Сообщение от pincet

Claster,
вот как-то если сразу 100 клиентов попытаются вставить, что будет?
ЗЫ Было когда-то давно и у меня ощущение, что только один клиент всегда работает с записью.....

Будет полный крах =)

@texnik-san · 25.04.2016, 17:37

Сообщение от Scandal777

Такое требование. Мне тоже оно кажется глупым, но как говорится: "клиент всегда прав".

Клиент не может ничего требовать в отношении полей, выполняющих сугубо техническую функцию - генерировать уникальный неповторяющийся идентификатор строки. А это единственная функция полей с автонумерацией.

Клиент может что-то требовать в отношении выдачи запросов, но во внутренее устройство таблицы он вмешиваться не должен.

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

@MyEternity · 25.04.2016, 21:24

Сообщение от Scandal777

Такое требование. Мне тоже оно кажется глупым, но как говорится: "клиент всегда прав".

Identity нельзя.
Если клиент очень хочет (т.е. настолько дурак) чтобы все было "красиво" хотя вообще непонятно, зачем ему это - используйте вместо Identity - Uniqueidentifier (guid). Чувства "эстетичности" клиента не будут ущемлены

@iap · 26.04.2016, 10:21

Сообщение от texnik-san

генерировать уникальный неповторяющийся идентификатор строки. А это единственная функция полей с автонумерацией.

IDENTITY не гарантирует уникальность.
Уникальность гарантирует уникальный индекс.

@texnik-san · 26.04.2016, 12:24

iap, вы слова "гЕНЕРировать" и "гАРАНТировать" различаете?

@iap · 26.04.2016, 13:08

Сообщение от texnik-san

iap, вы слова "гЕНЕРировать" и "гАРАНТировать" различаете?

Вот в этом примере:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
IF OBJECT_ID(N'tempdb..#T','U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T(F INT NOT NULL IDENTITY);
 
INSERT #T DEFAULT VALUES;
INSERT #T DEFAULT VALUES;
 
DBCC CHECKIDENT(#T, RESEED, 1) WITH NO_INFOMSGS;
 
INSERT #T DEFAULT VALUES;
 
SELECT * FROM #T;
 
IF OBJECT_ID(N'tempdb..#T','U') IS NOT NULL DROP TABLE #T;

третий INSERT сГЕНЕРирует уникальное значение поля [#T].F?

@Scandal777 · 26.04.2016, 14:18 **[ТС]**

Сообщение от texnik-san

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

Ну вы же понимаете, что на деле это не так, верно? Клиент формирует техническое задание (ТЗ). Если совсем ничего не понимает, то ему помогает наш аналитик (но это не наш случай). В любом договоре есть пункт, согласно которому все, что написано в ТЗ, должно быть реализовано. Любые изменения в ТЗ по совместному согласованию сторон с оформление документа-дополнения к ТЗ. Таким образом мы обязаны реализовать то, что просил заказчик.

Уболтали заказчика изменить требование на следующее. Т.к. проблему нужно было решить быстро и искать наилучшую реализацию времени особо не было, поэтому решили пока топорно (может кому-то понадобится):
1. первичным ключом таблицы сделали поле LINK INT PRIMARY KEY (без IDENTITY).
2. значение первого незанятого номера для LINK вычисляет скалярка.
Идея в скалярке следующая:
1. выбираем все LINK'и и сортируем по возрастанию (блого их в таблице будет немного)
2. определяем самый маленький LINK в этой последовательности
3. создаем временную табличку с двумя полями LINK INT и N_Index INT IDENTITY ("здесь найденное в п.2 мин.значение", 1) и загоняем в нее все LINK'и из пункта 1.
Получается что-то типа:
LINK | N_Index
------------------
96 96
97 97
98 98
99 99
115 100
123 101
124 102
4. выбираем первый попавшийся LINK, где LINK > N_Index, увеличиваем значение LINK'a в строке до него на 1 и возвращаем. Это и есть следующий свободный индекс.

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

Добавлено через 7 минут
4-ый пункт не так написал. Сам уже забыл после выходных

4. выбираем первую попавшуюся запись, где LINK > N_Index, и возвращаем ее N_Index. Это и есть следующий свободный индекс. Вот так правильно было.

@iap · 26.04.2016, 14:33

Сообщение от Scandal777

Решение топорное и наверняка есть куда улучшать, но сделать нужно было быстро и отгрузить заказчику хотфикс.

Надо было просто поискать на правильном форуме. Там и ссылка есть:

http://social.technet.microsof... erver.aspx

@Scandal777 · 26.04.2016, 14:40 **[ТС]**

Сообщение от iap

Надо было просто поискать на правильном форуме. Там и ссылка есть:

http://social.technet.microsof... erver.aspx

Использовать последовательности мне идея приходила. Но они на удаление записи вообще никак не реагируют. Ну или по крайней мере у меня не получилось их заставить работать как требуется. Т.е. было 1,2,3,4,5. Текущее значение - 5, следующее 6. Кто-то удалил 2,3. А текущее и следующее по-прежнему 5 и 6.

@iap · 26.04.2016, 14:51

Сообщение от Scandal777

Использовать последовательности мне идея приходила. Но они на удаление записи вообще никак не реагируют. Ну или по крайней мере у меня не получилось их заставить работать как требуется. Т.е. было 1,2,3,4,5. Текущее значение - 5, следующее 6. Кто-то удалил 2,3. А текущее и следующее по-прежнему 5 и 6.

Вы точно статью по ссылке прочитали?
Там вообще-то про пользовательскую реализацию нумерации речь идёт.
И об использовании пропущенных значений тоже...

@cygapb-007 · 26.04.2016, 15:34

Scandal777, Что-то я не понял...

Когда вы удаляете номер в середине - дырка должна остаться или должна сразу же заполниться изменением остальных номеров?

Если должна заполниться сразу - просто ROW_NUMBER()OVER(ORDER BY PrimaryKey) дает всегда однозначную (если нумеровать перед фильтрацией) непрерывную последовательность чисел (для текущего состояния таблицы)

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

Новые блоги и статьи Все статьи Все блоги /
http://iceja.net/ математические сервисы iceja 20.01.2026 Обновила свой сайт http:/ / iceja. net/ , приделала Fast Fourier Transform экстраполяцию сигналов. Однако предсказывает далеко не каждый сигнал (см ограничения http:/ / iceja. net/ fourier/ docs ). Также. . .	http://iceja.net/ сервер решения полиномов iceja 18.01.2026 Выкатила http:/ / iceja. net/ сервер решения полиномов (находит действительные корни полиномов методом Штурма). На сайте документация по API, но скажу прямо VPS слабенький и 200 000 полиномов. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь постоянного тока с R, L, C, k(ключ), U, E, J. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа, решает её и находит переходные токи и напряжения на элементах схемы. . . .	Восстановить юзерскрипты Greasemonkey из бэкапа браузера damix 15.01.2026 Если восстановить из бэкапа профиль Firefox после переустановки винды, то список юзерскриптов в Greasemonkey будет пустым. Но восстановить их можно так. Для этого понадобится консольная утилита. . .
Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .	Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	24.04.2016, 17:20
	Какая большая беда случится, если будут пропуски в значениях? 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	24.04.2016, 18:06
	Если было 1, 2, 3. Удалили 2. Каким должен быть следующий номер? 0

@Scandal777 2 / 2 / 0 Регистрация: 26.09.2014 Сообщений: 45
	24.04.2016, 18:18 [ТС]
	Было 1, 2, 3. Удалили 2. С Identity след.номером будет 4, а нужен - 2! 0

@pincet 1654 / 1153 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	25.04.2016, 12:57
	Claster, вот как-то если сразу 100 клиентов попытаются вставить, что будет? ЗЫ Было когда-то давно и у меня ощущение, что только один клиент всегда работает с записью..... 0

@texnik-san шапоклякистка 8-го дня 3681 / 2241 / 391 Регистрация: 26.06.2015 Сообщений: 4,647 Записей в блоге: 1
	26.04.2016, 12:24
	iap, вы слова "гЕНЕРировать" и "гАРАНТировать" различаете? 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	26.04.2016, 15:34
	Scandal777, Что-то я не понял... Когда вы удаляете номер в середине - дырка должна остаться или должна сразу же заполниться изменением остальных номеров? Если должна заполниться сразу - просто ROW_NUMBER()OVER(ORDER BY PrimaryKey) дает всегда однозначную (если нумеровать перед фильтрацией) непрерывную последовательность чисел (для текущего состояния таблицы) Если должна остаться - так и скажите, что пока новых данных добавлено не будет, ни о какой непрерывной нумерации говорить нельзя - а значит нефиг и для новых выдумывать свою нумерацию 1