Переназначение идентификаторов в поле типа не-IDENTITY в базе данных SQL Server

Question

ПРЕДУПРЕЖДЕНИЕ. Эта история о горе содержит примеры запахов кода, плохих дизайнерских решений и технического долга.

Если вы знакомы с принципами SOLID, практикуете TDD и тестируйте свою работу, НЕ ЧИТАЙТЕ НА. Если только вы не хотите хихикать над чьим-то несчастьем и злорадствовать в своей собственной удивительности, зная, что вы никогда не оставите такую ​​монументальную кучу дерьма для своих преемников.

Итак, если вы сидите удобно, тогда я начну.

В этом приложении, которое я унаследовал и поддерживаю, а также исправляю ошибки в течение последних 7 месяцев, у меня осталась УДАРНАЯ шаровая ягода от разработчика, которая ушла 6 с половиной месяцев назад. Да, через 2 недели после того, как я начал.

Тем не мение. В этом приложении мы имеем clients, employees а также visits столы.

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

TypeID    Description     NextRef
   1      Employees       804
   2      Clients         1708
   3      Visits          56783

Когда приложение создает новые строки для Employees выглядит в AppNewRef Таблица, получает значение, использует это значение для идентификатора, а затем обновляет NextRef колонка. То же самое для Clients, а также Visits и все остальные таблицы, чьи NextID использовать хранится здесь.

Да, я знаю, нет автоматической нумерации IDENTITY столбцы в этой базе данных. Все под предлогом "когда это было приложение Access". Эти идентификаторы хранятся в коде (VB6) как longs. Итак, возможно до 2 миллиардов 147 миллионов записей. Хорошо, это, кажется, работает довольно хорошо. (кроме того, что приложение обновляет и заботится о блокировке / обновлении и т. д., а не о базе данных)

Итак, наши пользователи довольно счастливо создают Employees, Clients, Visits и т. д. Visits ID постоянно увеличивается на несколько десятков за раз. Тогда проблемы случаются. Наши клиенты вызывают сбои в базе данных при создании пакетов посещений, потому что сервер прекрасно работает, а приложение перестает отвечать на запросы. Поэтому они убивают приложение, используя диспетчер задач, вместо того, чтобы терпеливо ждать. Конечно, приложение заблокировано.

Перейдите к ранее в этом году, и разработчик Тим (настоящее имя. Здесь нет защиты виновных) начинает модифицировать код для поэтапного пакетного обновления, чтобы пользовательский интерфейс оставался "отзывчивым". Затем наступает апрель, и он работает над своим уведомлением (вы можете представить сцену сейчас, не так ли?), И он уходит, чтобы закончить обновления.

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

Незаметные для Тима (настоящее имя, помните) и меня (который начал работу за две недели до отъезда Тима) и другого нового разработчика, который начал работу через неделю после этого, идентификаторы в таблице посещений начинают совершать огромные скачки вверх. Под огромным я подразумеваю 10000, 20000, 30000 одновременно. Иногда несколько сотен тысяч.

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

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

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

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

Я думаю о том, чтобы добавить новый столбец к каждой из таблиц, которые имеют ссылку на внешний ключ для этих идентификаторов посещений (неправильный внешний ключ, эти ограничения не существуют в этой базе данных). Этот новый столбец может хранить старое (текущее) значение идентификатора посещения (о, просто чтобы запутать вещи; в некоторых таблицах он называется EventID и на некоторых это называется VisitID).

Затем для каждой из других таблиц, которые относятся к этому VisitID, обновите до нового значения.

Идеи? Предложения? Фрагменты T-SQL в помощь всем с благодарностью получили.

Answer 1

Вариант первый:

Явно ограничьте все ваши отношения с внешним ключом и установите их ON UPDATE CASCADE,

Это будет означать, что всякий раз, когда вы меняете идентификатор, внешние ключи будут автоматически обновляться.

Тогда вы просто запускаете что-то вроде этого...

WITH
  resequenced AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY id) AS newID,
    *
  FROM
    yourTable
)
UPDATE
  resequenced
SET
  id = newID

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

UPDATE yourTable SET id = -id

Вариант второй:

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

Тогда сделай что-то вроде...

CREATE TABLE temp AS
  newID INT IDENTITY (1,1),
  oldID INT
)

INSERT INTO temp (oldID) SELECT id FROM yourTable

/* Do this once for the table you are re-identifiering              */
/* Repeat this for all fact tables holding that ID as a foreign key */
UPDATE
  factTable
SET
  foreignID = temp.newID
FROM
  temp
WHERE
  foreignID = temp.oldID

Затем повторно примените любые существующие отношения внешнего ключа.

Это довольно страшный вариант. Если вы забыли обновить таблицу, вы просто потеряли свои данные. Но вы можете дать это temp таблица гораздо более приятное имя и сохранить его.

Удачи. И пусть Господь помилует вашу душу. И Тим, если ты когда-нибудь встретишь его в темной аллее.

Answer 2

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

select min(number) from visits right join numbers on visits.id = numbers.number

Таким образом вы заполняете все пробелы, не меняя другие таблицы.

но я бы просто переделал всю базу данных.