Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Изменение типа полей таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
Доброго времени суток!
Имеется таблица tb1
+ tb1

CREATE TABLE tb1(
	[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
	...
	[field1] float NOT NULL,
	...
	[field10] float NOT NULL,
	[field11] bigint NOT NULL,
	[catalogId] bigint NULL,
	FOREIGN KEY (masterId) REFERENCES tbCatalog(Id) 
) 


в которой > 10 млн записей.
Требуется поменять тип всех float полей на decimal(28, 9), и еще 1 с bigint на int.
В таблице всего пару индексов (по первичному и внешнему ключам).
Что отработает быстрее, замены типов командой alter column или
создание новой таблицы - заполнение - удаление старой - переименование
и почему?
Насколько сильна зависимость от того, происходит расширение типа или его сжатие, от количества индексов (при условии, что типы индексированных и участвующих в индексах полей заменять не требуется)?
7 окт 15, 14:41    [18247065]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
Второй вариант будет быстрее хотя бы потому, что исходную таблицу придется просканить всего один раз.
7 окт 15, 14:49    [18247134]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
Шамиль Фаридович,

перелив таблицы обычно лучше по времени, если предполагается изменить типы более трех несжатых столбцов или более одного сжатого. Мои эмпирические правила.
7 окт 15, 15:05    [18247287]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
o-o
Guest
и если бы только скорость.
каждое из изменений с float на decimal
это гарантированное *добавление* нового столбца.
т.е. у уже залитых данных вместо как бы 10 полей float будут 10 прежних float (DROPPED) и 10 новых decimal.
хорошая прибавка к пенсии имеющемуся размеру таблицы
7 окт 15, 15:20    [18247441]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
o-o,
вы это серьезно?
вы хотите сказать, что на каждой странице данных останутся фантомные(более) значения неиспользуемые значения старого типа?
это характерно для любой операции изменения типа столбца, или только для float -> decimal?
вообще, для ответа на вопрос мне нужно знать "механику" alter column, не подкинете ссылку?
7 окт 15, 16:31    [18248035]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
o-o
Guest
Шамиль Фаридович
o-o,
вы это серьезно?
вы хотите сказать, что на каждой странице данных останутся фантомные(более) значения неиспользуемые значения старого типа?

это самое.
можно своими глазами увидеть, используя dbcc ind + dbcc page.
но можно и самому посчитать.
смотрите, создаем таблицу с размером строки 8055 (макс. допустимая длина 8060)
и с полем типа float как у вас:
create table tb(c1 char(8000), c2 char(40), f float); -- float 8 bytes; Record Size = 8055
insert into tb (f) values (1.)

по идее, у меня еще 5 байт есть, чтоб от float размера 8 перейти к decimal(28,9) размера 13.
пробуем:
alter table tb
alter column f decimal(28,9) -- 13 bytes

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'tb' failed because the minimum row size would be 8068, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

странно, да, перебор на 8 байт, похоже, он не 5 собирается добавить, а все 13.
но это легко проверить.
попробуем изменить таблицу, добавив char(13), он точно занимает 13 байт:
alter table tb
add c13 char(13)

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'tb' failed because the minimum row size would be 8068, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

абсолютно та же ошибка с перебором, предполагаемая новая строка будет все те же 8068 байт.
потому что переходя от float к decimal(28,9) он просто создает поле нужного размера и туда копирует значение.
старое да, будет "фантом".
но ужимать его никто не собирается.
------------------------------------------------
ну и про смену bigint на int.
пока таблицу не перестроите, вы не только не получите уменьшения размера имеющегося.
все ваши вновь добавленные строки будут добавляться прежней длины, как будто там по-прежнему bigint.
тоже проверяется через те же dbcc ind + dbcc page,
но еще можно и в книге найти:

K.Delaney
Some changes to a table’s structure require that the data be examined but not modified.
For example, when you change the nullability property to disallow NULLs,
SQL Server must first ensure that the existing rows have no NULLs.
A variable-length column can be shortened when all the existing data is within the new limit,
so the existing data must be checked.
If any rows have data longer than the new limit specified in ALTER TABLE, the command fails.
Be aware that for a huge table, this can take some time.
Changing a fixed-length column to a shorter type, such as changing an int column to smallint or changing a char(10) to char(8),
also requires examining all the data to verify that all existing values can be stored in the new type.
However, even though the new data type takes up fewer bytes, the rows on the physical pages aren’t modified.
If you have created a table with an int column, which needs 4 bytes in each row, all rows will use the full 4 bytes.
After altering the table to change the int to smallint, you are restricted in the range of data values you can insert,
but the rows continue to use 4 bytes for each value, instead of the 2 bytes that smallint requires.
You can verify this by using the DBCC PAGE command. Changing a char(10) to char(8) displays similar behavior,
and the rows continue to use 10 bytes for that column, but the data inserted is restricted to be no more than 8 bytes long.
It’s not until the table is rebuilt that the char(10) columns are actually re-created to become char(8).

Microsoft SQL Server 2012 Internals
7 окт 15, 17:16    [18248380]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
o-o,
спасибо за исчерпывающий ответ!
7 окт 15, 17:32    [18248507]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
o-o
Guest
кстати, по поводу цитаты.
называется, "доверяй, но проверяй".
про changing the nullability, мягко говоря, наврано.
вот тут мы это дело разоблачали:
Поменять NULLability столбца
т.е. и в случае null --> not null создается новый столбец рядышком...
но про bigint --> int -- правда.
и про float --> decimal(28,9) -- тоже.
вот картинка для float --> decimal(28,9)

К сообщению приложен файл. Размер - 94Kb
7 окт 15, 17:45    [18248614]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
o-o
Поменять NULLability столбца
т.е. и в случае null --> not null создается новый столбец рядышком...

Но как написано в указанном топе не всегда, а только для типов переменной длины, хотя столбцы
DROPPED = NULL появляются при каждой смене NULL на NOT NULL, пусть Record Size и не изменяется

Поигрался я с вашими тестовыми стендами (правда вешал на таблицу кластеризованный индекс), и пришел к выводам :
1. Rebuild кластеризованного индекса решает проблему фантомов и фрагментации ("лишнего" места, занимаемого фантомом)
2. Сжатие типа (bigint -> int) не приводит к увеличению Record size и появлению фантомов, но и место не освобождается (впрочем, вы про это писали), а вот varchar(10) -> varchar(9) создает фантом.
3. Расширение типа фиксированной длины дает фантом, переменной длины - нет.
8 окт 15, 14:14    [18252983]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
o-o
Guest
Шамиль Фаридович
o-o
Поменять NULLability столбца
т.е. и в случае null --> not null создается новый столбец рядышком...

Но как написано в указанном топе не всегда, а только для типов переменной длины, хотя столбцы
DROPPED = NULL появляются при каждой смене NULL на NOT NULL, пусть Record Size и не изменяется

ну да.
но я и не претендую на описание всех случаев здесь и сейчас
(да и в той теме как раз проверены оба случая),
+ у меня нет супер-репутации и книги я не издаю.
а вот так безоговорочно написать в известной книге,
да еще именно про "лучший" случай -- не шибко корректно.
Шамиль Фаридович
Rebuild кластеризованного индекса решает проблему фантомов и фрагментации ("лишнего" места, занимаемого фантомом)

ну как бы это понятно и было упомянуто.
но в свете вашего-то вопроса,
ребилд это и есть перелив старого в новое.
спрашивается, почему не сделать это сразу
вместо того, чтобы сперва 10 раз просканить таблицу (как давно уже написал Гавриленко)
ради каждого ALTER TABLE ALTER COLUMN
+ потом все равно взять и перелить, уже под кодовым названием "ребилд"?
8 окт 15, 14:35    [18253182]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
o-o,
я уже и не собираюсь делать ALTER COLUMN.
После полученной от вас инфы, я ужаснулся тому, что может происходить в БД заказчиков!
И лишний раз порадовался тому, что мы выпускали джобы на еженедельное перестроение индексов.
8 окт 15, 16:19    [18254033]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
o-o
Guest
Шамиль Фаридович
я уже и не собираюсь делать ALTER COLUMN.

и правильно
8 окт 15, 16:24    [18254065]     Ответить | Цитировать Сообщить модератору
 Re: Изменение типа полей таблицы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
С изменением типов надо быть осмотрительным, т.к. после этого может перестать работать клиент. Также, чтобы не переливать всю таблицу, можно использовать add/drop/rename column. Смотрите по ситуации (размер и скорость работы журнала, например).
8 окт 15, 16:38    [18254155]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить