Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
Всем доброго времени суток!

Подскажите, пожалуйста, в какой-нибудь версии MS SQL Server-а командами "alter table alter column" можно для существующего поля первичного ключа с кластерным индексом выставить свойство IDENTITY ?
Или такое возможно только через пересоздание таблицы?
20 окт 17, 06:37    [20884393]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Командой можно
20 окт 17, 08:10    [20884461]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Можно даже пола добавить, тоже командой
20 окт 17, 08:11    [20884462]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5657
Mike_za
Командой можно

Какой, если не секрет? sp_rename не предлагать.
20 окт 17, 09:29    [20884640]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
o-o
Guest
You can't alter the existing columns for identity.

You have 2 options,

1) Create a new table with identity & drop the existing table
2) Create a new column with identity & drop the existing column

Details here:
Adding an identity to an existing column
20 окт 17, 09:58    [20884788]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
лолл
Member

Откуда:
Сообщений: 450
Начинучка,

Если не хотите заморачиваться с пересозданием колонки, то можно создать последовательность CREATE SEQUENCE,
а на поле создать default-constraint с NEXT VALUE FOR для этой последовательности
20 окт 17, 11:24    [20885155]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Eleanor
Member

Откуда:
Сообщений: 2643
Начинучка,

В этой же ссылке
o-o
Details here:
Adding an identity to an existing column

во втором ответе написано, как это сделать быстрее через ALTER TABLE...SWITCH, если у вас очень большая таблица.
Но через "alter table alter column" нельзя.
20 окт 17, 12:02    [20885325]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
лолл,

напомните, пожалуйста, последовательности с какой версии сервера доступны?
20 окт 17, 12:37    [20885462]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
Eleanor,

у меня ещё тип данных у колонки должен поменяться. Вроде как при SWITCH такое не допускается?
20 окт 17, 12:39    [20885465]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
o-o
Guest
Начинучка
лолл,

напомните, пожалуйста, последовательности с какой версии сервера доступны?

>= 2012
20 окт 17, 12:44    [20885481]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Eleanor
Member

Откуда:
Сообщений: 2643
Начинучка,

Да, тип поменять не получится:
ALTER TABLE SWITCH statement failed because column 'id' has data type int... which is different from its type bigint...
20 окт 17, 12:45    [20885482]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
o-o,

спасибо!
20 окт 17, 12:47    [20885492]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
Eleanor,

жаль. а так было бы красивое решение... :)
20 окт 17, 12:47    [20885495]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
o-o
Guest
Начинучка
Eleanor,

у меня ещё тип данных у колонки должен поменяться. Вроде как при SWITCH такое не допускается?

еще бы.
только почему вы свои хотелки не оглашаете сразу все оптом?
у вас вопрос не как свойство IDENTITY добавить существующему полю,
а как поменять тип поля, а заодно навесить IDENTITY.
и щас окажется, что меняете вы int на bigint,
так что ждет вас перелив в новую заранее созданную таблицу
20 окт 17, 12:48    [20885502]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Можно же всё это сделать через интерфейс Management Studio.
Или просто получить скрипт для представления о том, как это делает Management Studio.
20 окт 17, 13:14    [20885655]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
o-o
Начинучка
Eleanor,

у меня ещё тип данных у колонки должен поменяться. Вроде как при SWITCH такое не допускается?

еще бы.
только почему вы свои хотелки не оглашаете сразу все оптом?
у вас вопрос не как свойство IDENTITY добавить существующему полю,
а как поменять тип поля, а заодно навесить IDENTITY.
и щас окажется, что меняете вы int на bigint,
так что ждет вас перелив в новую заранее созданную таблицу


йез , и добро пожаловать в клуб
20 окт 17, 14:49    [20886102]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
Ролг Хупин,

Что за клуб? Клуб несчастных разгребателей ошибок проектирования? Тоже есть в сопровождении системы, выросшие за пределы, установленные недальновидными разработчиками-проектировщиками? Есть успешный опыт решения подобного рода задач? Вариантами решения на уровне идеи поделиться можите?
21 окт 17, 12:56    [20888363]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
iii2
Member

Откуда:
Сообщений: 202
Начинучка
Ролг Хупин,

Что за клуб? Клуб несчастных разгребателей ошибок проектирования? Тоже есть в сопровождении системы, выросшие за пределы, установленные недальновидными разработчиками-проектировщиками? Есть успешный опыт решения подобного рода задач? Вариантами решения на уровне идеи поделиться можите?

Установите начальное значение поля identity равным минимальной отрицательной величине, и сможете еще столько же времени пинать балду, пока автоинкремент не доползёт до нуля и ид не начнет задваиваться.
:-)
22 окт 17, 16:53    [20890030]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
Начинучка
Ролг Хупин,

Что за клуб? Клуб несчастных разгребателей ошибок проектирования? Тоже есть в сопровождении системы, выросшие за пределы, установленные недальновидными разработчиками-проектировщиками? Есть успешный опыт решения подобного рода задач? Вариантами решения на уровне идеи поделиться можите?


да, есть.
0. Простого пути нет.
1. Один путь указал коллега выше - двинуться в -бесконечность и получить запас времени, хотя это не решение.
2. Еще один путь указал коллега о-о еще выше, но все зависит от версии сервера, объема данных.
3. Еще один путь я использовал: создал двойники таблиц с идентити + сделал изменение в логике
4. Дерзайте

зы. туда же
http://www.dbdelta.com/add-or-remove-identity-property-from-an-existing-column-efficiently/
22 окт 17, 17:45    [20890069]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
iii2
Member

Откуда:
Сообщений: 202
И, кстати, приложения при переходе на bigint протестировать не забудьте.
А то они могут получать идентификаторы и хранить у себя. Например, чтобы манипулировать связанными записями.
23 окт 17, 08:42    [20890861]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Начинучка
Guest
Ролг Хупин,

В случае с новой таблицей примерный алгоритм такой:
1) Создается новая таблица с правильной структурой полей (индексы сразу навешивать или после наполнения таблицы данными?).
2) Настраивается механизм регистрации изменений в исходной таблице (думается триггер на DML по исходной таблице + буферная таблица для хранения идентификаторов изменившихся строк)
3) Переливаются данные из исходной таблицы в новую (сейчас индексы воссоздаем?)
4) старая таблица как-то блокируется на запись (как?)
5) Синхронизируются таблицы, в новую доливаются изменения, возникшие в старой за время переливки данных
6) Скриптуются и удаляются все внешние ключи, ссылающиеся на исходную-старую
7) Новая таблица обвешивается триггерами, выдаются необходимые гранты пользователям.
8) Таблицы меняются местами (старую переименовываем во что угодно, новую называем именем старой)
9) Восстанавливаются внешние ключи на теперь уже новую таблицу

Ничего не забыл?

А что произойдет с зависимыми объектами после замены таблиц местами? Триггеры на других таблицах, пишущие или читающие исходную? Процедуры, функции? Их нужно принудительно перекомпилировать или сервер сам догадается что к чему?
23 окт 17, 10:34    [20891234]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
Select *  into  old from  new ;

FK - на new disable (drop)

-- Drop table new ; 
CREATE TABLE new ( identity , все сотальное  ) ;

set indetity inseret new on 


Insert into new 
Select * from  old ;

set indetity_inseret new off

create indexes on new
FK - на OLD enable


ps если страшно дрпоать - ну можно new2 - потом переименвать
Если таблица реально большая - возможны варинаты вставки по частям - но тогда чуть посложней
все это в монопольном режиме - если никто др. не лазит
потренировался 1 раз на бакапе на локалхост и все
а вот если вдруг надо на лету - тогда задача сложней будет
23 окт 17, 13:49    [20892295]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
Начинучка
Ролг Хупин,

В случае с новой таблицей примерный алгоритм такой:
1) Создается новая таблица с правильной структурой полей (индексы сразу навешивать или после наполнения таблицы данными?).
2) Настраивается механизм регистрации изменений в исходной таблице (думается триггер на DML по исходной таблице + буферная таблица для хранения идентификаторов изменившихся строк)
3) Переливаются данные из исходной таблицы в новую (сейчас индексы воссоздаем?)
4) старая таблица как-то блокируется на запись (как?)
5) Синхронизируются таблицы, в новую доливаются изменения, возникшие в старой за время переливки данных
6) Скриптуются и удаляются все внешние ключи, ссылающиеся на исходную-старую
7) Новая таблица обвешивается триггерами, выдаются необходимые гранты пользователям.
8) Таблицы меняются местами (старую переименовываем во что угодно, новую называем именем старой)
9) Восстанавливаются внешние ключи на теперь уже новую таблицу

Ничего не забыл?

А что произойдет с зависимыми объектами после замены таблиц местами? Триггеры на других таблицах, пишущие или читающие исходную? Процедуры, функции? Их нужно принудительно перекомпилировать или сервер сам догадается что к чему?


Если позволяет количество данных и время - создаете новую таблицу, переносите все данные, индексы, ключи, зависимости, триггеры. Старая перименовывается, в общем - выше есть ссылки.
23 окт 17, 15:47    [20892885]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
iii2
Member

Откуда:
Сообщений: 202
Начинучка
Ролг Хупин,

В случае с новой таблицей примерный алгоритм такой:
1) Создается новая таблица с правильной структурой полей (индексы сразу навешивать или после наполнения таблицы данными?).

Если индексы навешивать сразу, то их нужно отключить на момент вставки. Кроме кластерного, разумеется.
2) Настраивается механизм регистрации изменений в исходной таблице (думается триггер на DML по исходной таблице + буферная таблица для хранения идентификаторов изменившихся строк)

У вас высоконагруженная система, и вы хотите как то перенести основную часть данных, а потом донакатить изменения?
А у вас поля timestamp случайно нету в таблице?
3) Переливаются данные из исходной таблицы в новую (сейчас индексы воссоздаем?)

После заливки with tablock включаем индексы.
4) старая таблица как-то блокируется на запись (как?)

Триггер нарисуйте. Который генерировал бы ошибку и сообщение "Изменения блокированы до окончания ремонтных работ.
5) Синхронизируются таблицы, в новую доливаются изменения, возникшие в старой за время переливки данных

Так нету, говорите, поля timestamp?
6) Скриптуются и удаляются все внешние ключи, ссылающиеся на исходную-старую
7) Новая таблица обвешивается триггерами, выдаются необходимые гранты пользователям.
8) Таблицы меняются местами (старую переименовываем во что угодно, новую называем именем старой)
9) Восстанавливаются внешние ключи на теперь уже новую таблицу

Ничего не забыл?

Констрейнты ещё б доверенными сделать бы.
А что произойдет с зависимыми объектами после замены таблиц местами? Триггеры на других таблицах, пишущие или читающие исходную? Процедуры, функции? Их нужно принудительно перекомпилировать или сервер сам догадается что к чему?

Вью вроде бы нужно сделать Alter.
Кстати, а у вас материализованных вью не висит на табличках?

Мы столкнулись с такой же проблемой после 8 лет эксплуатации системы.
И, прикинув все риски и степень геморроя (кстати, еще раз говорю - оттестируйте клиентские приложения! Они тоже подвержены этой болезни. Могут хранить у себя ключи строк для извлечения связанных записей), так вот... прикинув степень геморроя, решили переложить проблему на плечи будущих поколений. И установили начальное значение в –2147483648.
Решили, что еще лет 7-8 нам хватит для подумать.
24 окт 17, 22:29    [20897002]     Ответить | Цитировать Сообщить модератору
 Re: Поменять существующее поле на IDENTITY  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33273
Блог
iii2
Вью вроде бы нужно сделать Alter.


sp_refreshview
sp_refreshsqlmodule
24 окт 17, 23:29    [20897156]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить