"Бездырочная" генерация нового ID в SQL

[Vitaly]

Имеется таблица А на SQL2000/2005, в которой PRIMARY KEY должен генерироваться вручную, с заполнением так-называемых дырок в ID значениях, созданных удалением существующих записей.

Кто знает как осуществить запрос в SQL на получения такого нового значения для ID?

Интересует какой-нибудь вариант без специфики платформы, т.е. общего плана SQL, чтобы и с Microsoft JET тоже работал...

[YellowMan]

Дружеский совет - не делайте вы этого.

Если уж надумали делать то наверное быстрее всего будет вот так:

select top 1 t.id+1 from my_table t left join my_table t1 on t1.id=t.id+1
where t1.id is null

Оберните все это в транзакцию с уровнем не ниже repeatable reads.
Это если ИД числовые и приращаются по-порядку и неважно какое именно первое "свободное" ИД выпадет.

PRIMARY KEY - это немного другая вещь...

[magician]

а чем Unique не нравицца ?

и IMHO заполнение дырок может в последствии привести к ооочень неприятным последствиям ...

а Primary key на один филд положен чтоли и только на тот, чо генерить хочешь ?

[Vitaly]

Пока тут ответ созревал, я уже почти и придумал сам:

SELECT MIN([ID] - 1) FROM A WHERE [ID] - 1 NOT IN (SELECT [ID] FROM A) AND [ID] - 1 > 0

- возращает ID из первой "дырки" что существует, либо NULL, если дырок вообще нет, а тогда можно использовать SELECT MAX([ID]) + 1 FROM A, что в совокупности и даст требуемое решение. Осталось только довести это до совершенства, превратив два запроса в один. Как сделать? - тоже вопрос...

Вообще, часть AND [ID] - 1 > 0 конечно не обязательна, по скольку просто вернет 0, если дырок не нашел, что тоже не валидное ID. Валидное ID должно получится по концовке в диапазоне [1, ∞)

Vitaly добавил 19.04.2007 в 16:05

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

а чем Unique не нравицца ?

А что он даст? Этот операнд также работает лишь над массивом существующих записей...

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

а Primary key на один филд положен чтоли и только на тот, чо генерить хочешь ?

Да, на один, что генерится.

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

и IMHO заполнение дырок может в последствии привести к ооочень неприятным последствиям ...

Какие последствия?

Vitaly добавил 19.04.2007 в 16:09
А насчет AND [ID] - 1 > 0 необязательно, это я зря. Когда в таблице нет ни одной записи, то вернет уже не 0 а NULL, что в качестве sub-query приведет к неоднозначности, т.е. оно там нужно.

[YellowMan]

а чем Unique не нравицца ?

Жень, а что это? identity ?
Про последствия можно писать долго и подробно, а так же и про использование самодельных генераций вместо identity - вот только стоит ли оно того?

[magician]

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

Жень, а что это? identity ?
Про последствия можно писать долго и подробно, а так же и про использование самодельных генераций вместо identity - вот только стоит ли оно того?

она самая
с 2005 появилась

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

Виталик - та немного общибок в Select
начиная с синтаксиса и заканчивая тем, что он вернет тебе все дырки + все остальное начиная с последнего значения ID+1 и заканчивая max возможным значением


IMHO - не делай эти грабли ...может прилететь так, что не поймаешь

[Vitaly]

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

Про последствия можно писать долго и подробно, а так же и про использование самодельных генераций вместо identity - вот только стоит ли оно того?

Долго и нундно не надо Не уверен что в моем случае есть чего боятся, но коли-уже напужали, так закиньте хоть парочку аргументов, чтобы хоть не на пустом месте боятся чего...., а то так SQL-fobia начнет развиваться...

И если кто подскажет, как те два запроса в один объеденить, that'd be grand

Vitaly добавил 19.04.2007 в 16:27

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

Виталик - та немного общибок в Select
начиная с синтаксиса и заканчивая тем, что он вернет тебе все дырки + все остальное начиная с последнего значения ID+1 и заканчивая max возможным значением

Не может быть, я уже это тестировал, работает. Смотри - там-же в начале SELECT MIN стоит, откуда при этом несколько значений? Всегда одно!

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

IMHO - не делай эти грабли ...может прилететь так, что не поймаешь

- говорю-же, кончайте пужать, по делу пишите что и как....

[magician]

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

Не может быть, я уже это тестировал, работает. Смотри - там-же в начале SELECT MIN стоит, откуда при этом несколько значений? Всегда одно!

ок, прокосячил - признаю,
но сделай дырку от 1 до 100 потом забей ID выше 100
Select вернет 99

[Vitaly]

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

Cделай дырку от 1 до 100 потом забей ID выше 100Select вернет 99

Да, есть такое, но зато потом вернет 98, потом 97...т.е. дырки-то будут заполнятся нормально, пока не закончаться, т.е. результат конечный такой-же что и требовался, т.е. не критично... т.е. это вопрос о том как лучше бублик есть - снаружи или изнутри...

[magician]

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

- говорю-же, кончайте пужать, по делу пишите что и как....

начиная с бизнесс логики - есть 150% уверенность , что никто другой не джойнить свои таблички в коде втихую к этой по ID и что целостность и консистентность 150% не может быть нарущена никаким куском кода ?
жах - бывает такое ...


про SELECT MAX([ID]) + 1 FROM A ... Виталик ...если у тя 2000 юзеров подсадят неслабо ресурс выгребанием дырок ... то ты стремишься к тому, что на одну и ту же транзакцию может попасть одна и таже дырка или Max ... что приведет ....к потере данных ...а в бизнес приложениях - к потере денех ...а оно те нада ?

[YellowMan]

Долго и нундно не надо Не уверен что в моем случае есть чего боятся, но коли-уже напужали, так закиньте хоть парочку аргументов, чтобы хоть не на пустом месте боятся чего...., а то так SQL-fobia начнет развиваться...

Парочку - пожалуйста.
1) identity использует latch чтобы удержать схаванное значение, а ручная генерация - транзакцию с минимум repeatable reads, а по-хорошему надо бы serialize на протяжении всей транзакции. Тут как-раз раздолье для фантомов. За подробностями - в БОЛ.
2) Те ваши запросы для поиска - голимый scan. Мой еще можно приспособить для seek если построить лишнюю колонку и прикидывать интервалы.
Скан в сочетании с 1) - смерть среди айсбергов.

она самая
с 2005 появилась

Не совсем осознал чего там появилось?

[Vitaly]

Ок, тогда стоило пояснить: одновременный доступ от 2-х 3-х пользователей, не больше. Вся база уже и так построена на SELECT MAX([ID]) + 1 логике, и мне уже деваться некуда, просто пытаюсь как-то улучшить положение с введением новых таблиц.

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

[magician]

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

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

в куда ?

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

чем предыдущий разработчики и объясняли генерацию ID в -ручную...

контрактники недоучки ?

[Vitaly]

1. Программный бэкап из SQL2000/2005 в Microsoft JET (MS-Access) базу данных

2. Разные ситуации бывают - то текучка, то головой работать лень, а то и недоучка... Оригинальный разработчик - Россиянин, Microsoft-туды-его за-Certified... скорее всего на этом форуме тоже лазит, так-что имя с фамилией называть не буду...

[Yapi]

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

1. Программный бэкап из SQL2000/2005 в Microsoft JET (MS-Access) базу данных

2. Разные ситуации бывают - то текучка, то головой работать лень, а то и недоучка... Оригинальный разработчик - Россиянин, Microsoft-туды-его за-Certified... скорее всего на этом форуме тоже лазит, так-что имя с фамилией называть не буду...

Влезу тупым рылом. Если скомбинировать?

В начале транзакции вставляется запись в таблицу-генератор с identity. Это identity внутри транзакции читается и возвращается/вставляется ручками куда нужно.
Да, потом транзакция если можно откатывается, то есть таблица-генератор всегда пустая.

И волки целы и овцы завтракали.