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

Откуда:
Сообщений: 153
Мальчики, привет.

Есть большая таблица, 200Гб.

В ней есть поле типа int.

в таблице есть и кластерный и некластерный индекс.

Скажите что будет происходить если сделать alter table и поменять int на bigint?
30 мар 17, 12:44    [20350421]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
Кластерный индекс как раз по этому полю int построен.

Я так понимаю что он при изменении типа пересоздатся?
30 мар 17, 12:46    [20350428]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
a.tarannikova,

если не ошибаюсь, то правильная схема

1. Добавляете колонку BigInt
2. Переливаете в неё значения
3. Убиваете кластерный, а возможно и все в порядке от некластерного к калстерному
4. Переименовуете новую
5. убиваете старую
6. Строите индексы

коллеги поправят, надеюсь :)

и да, вы нормально писать не умеете?
30 мар 17, 12:49    [20350446]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Если порядок полей изменится, то возможны разного рода неприятности в коде, который был на это не рассчитан... :)
30 мар 17, 12:53    [20350460]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
aleksrov
Member

Откуда:
Сообщений: 948
TaPaK,

Нет, вроде не так.
Меняете сам столбец, будете наложена блокировка схемы SCH-M. В это SQL сам создает новую колонку и переливает туда данные, при этом старое занимает место, но после ребилда оно будет освобождено.
30 мар 17, 12:55    [20350470]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
TaPaK
a.tarannikova,

если не ошибаюсь, то правильная схема

1. Добавляете колонку BigInt
2. Переливаете в неё значения
3. Убиваете кластерный, а возможно и все в порядке от некластерного к калстерному
4. Переименовуете новую
5. убиваете старую
6. Строите индексы

коллеги поправят, надеюсь :)



А если alter table сделать что будет?

Сколько ориентировочно рессурсов понадобится (место в БД, генерация логов)?
30 мар 17, 12:57    [20350480]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
У меня такое ощущение что такое не пройдет, когда по колонке кластерный индекс построен:

ALTER TABLE MyTable ALTER COLUMN column1 bigint;  
30 мар 17, 13:10    [20350558]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
o-o
Guest
a.tarannikova
У меня такое ощущение что такое не пройдет, когда по колонке кластерный индекс построен:

ALTER TABLE MyTable ALTER COLUMN column1 bigint;  

удивительно правильное ощущение
30 мар 17, 13:16    [20350587]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
лолл
Member

Откуда:
Сообщений: 450
a.tarannikova,

проводите эксперименты, но только на копии базы
30 мар 17, 13:17    [20350588]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
LSV
Если порядок полей изменится, то возможны разного рода неприятности в коде, который был на это не рассчитан... :)

За звёздочки руки ломать надо, но так бывает, что некому :)
30 мар 17, 13:20    [20350608]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
o-o
a.tarannikova
У меня такое ощущение что такое не пройдет, когда по колонке кластерный индекс построен:

ALTER TABLE MyTable ALTER COLUMN column1 bigint;  

удивительно правильное ощущение


Как-то так:

Msg 5074, Level 16, State 1, Line 1
объект "PK$MyTable@column1" зависит от столбец "column1".
Msg 4922, Level 16, State 9, Line 1
Ошибка ALTER TABLE ALTER COLUMN column1, так как один или несколько объектов обращаются к данному столбцу.



Как это обойти?

Дропнуть индекс?
30 мар 17, 13:29    [20350662]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
aleksrov
Member

Откуда:
Сообщений: 948
a.tarannikova,

Так можно делать если индекса нет (я ваше 2 сообщение просмотрел), иначе:
My approach would be this:

1.create a new table with identical structure - except for the ID column being BIGINT IDENTITY instead of INT IDENTITY
----[ put your server into exclusive single-user mode here; user cannot use your server from this point on ]----
2.find and disable all foreign key constraints referencing your table
3.turn SET IDENTITY_INSERT (your new table) ON
4.insert the rows from your old table into the new table
5.turn SET IDENTITY_INSERT (your new table) OFF
6.delete your old table
7.rename your new table to the old table name
8.update all table that have a FK reference to your table to use BIGINT instead of INT (that should be doable with a simple ALTER TABLE ..... ALTER COLUMN FKID BIGINT)
9.re-create all foreign key relationships again
10.now you can return your server to normal multi-user usage again
Это из гугла
30 мар 17, 13:31    [20350676]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
aleksrov
2.find and disable all foreign key constraints referencing your table


там их очень много, хотелось бы без этого обойтись
30 мар 17, 13:35    [20350699]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
o-o
Guest
aleksrov
put your server into exclusive single-user mode here; user cannot use your server from this point on

давайте из-за каждой таблицы сервер в сингл юзер отправлять
30 мар 17, 13:35    [20350702]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
aleksrov
Member

Откуда:
Сообщений: 948
o-o,

Это был копи паст.
30 мар 17, 13:36    [20350711]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
Кстати индекс не удаляется когда он PK

Инструкция DROP INDEX неприменима к индексам, созданным при указании ограничений параметров PRIMARY KEY и UNIQUE.
30 мар 17, 13:37    [20350714]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
лолл
Member

Откуда:
Сообщений: 450
a.tarannikova
Кстати индекс не удаляется когда он PK

Инструкция DROP INDEX неприменима к индексам, созданным при указании ограничений параметров PRIMARY KEY и UNIQUE.


ALTER TABLE DROP CONSTRAINT...
30 мар 17, 13:40    [20350733]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
a.tarannikova
Кстати индекс не удаляется когда он PK

Инструкция DROP INDEX неприменима к индексам, созданным при указании ограничений параметров PRIMARY KEY и UNIQUE.


DROP CONSTRAINT выполнилось.
30 мар 17, 13:40    [20350735]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
o-o
Guest
aleksrov
o-o,

Это был копи паст.

так и гражданочка могла сделать копипаст...если б прав хватило, конечно
30 мар 17, 13:44    [20350753]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
msLex
Member

Откуда:
Сообщений: 8730
Владислав Колосов
За звёздочки руки ломать надо, но так бывает, что некому :)


За atler table switch, который чувствителен" к порядку полей, тоже руки ломать, или ноги?


По сабжу


при "переливки" int->bigint в рамках текущей таблицы есть не иллюзорный шанс получить массовые pagesplit-ы, поэтому один из самых быстрых способов это создание новой таблицы и переливка с tablock-ом(по возможности, не full модели).
30 мар 17, 13:45    [20350759]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
o-o
Guest
msLex
поэтому один из самых быстрых способов это создание новой таблицы и переливка с tablock-ом(по возможности, не full модели).

... и с order by
30 мар 17, 13:50    [20350800]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
сложно сказать сколько займёт переливка 200ГБ + логи и весь остальной ад
30 мар 17, 13:53    [20350812]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
А SQL Management Studio какой скрипт делает, если там просто тип у поля поменять?
Для начала его не выполнять, а просто посмотреть...
30 мар 17, 13:54    [20350822]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
лолл
Member

Откуда:
Сообщений: 450
Владислав Колосов
LSV
Если порядок полей изменится, то возможны разного рода неприятности в коде, который был на это не рассчитан... :)

За звёздочки руки ломать надо, но так бывает, что некому :)


Со звездочками вполне можно жить. Я как-то писал DDL-триггер, который при ALTER VIEW, TABLE, FUNCTION из зависимостей вытягивал все вьюхи со звездочками и генерировал скрипт на обновление автоматически.
30 мар 17, 13:54    [20350825]     Ответить | Цитировать Сообщить модератору
 Re: Раскажите что будет если сделать alter table ?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
iap
А SQL Management Studio какой скрипт делает, если там просто тип у поля поменять?
Для начала его не выполнять, а просто посмотреть...

по моему он всегда хочет через пересоздание
30 мар 17, 13:56    [20350839]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить