Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
MrVoid Member Откуда: Сообщений: 333 |
Всем добрый день! вопрос. Сейчас делаю перенос данных в SQL Server из Access, и все столбцы там NULL. После переноса планируется сделать некоторые столбцы обязательными, т.е. NOT NULL, после того, как работники занесут все данные. -- Сначала NULL create table dbo.test ( id int identity constraint pk_test primary key not null, val varchar(100) null ); -- Потом NOT NULL alter table dbo.test alter column val varchar(100) not null; Вопрос такой. Какие изменения произойдут при изменении c NULL на NOT NULL? В "SQL Server 2008 Internals" я прочитал: "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 make sure there are no NULLs in the existing rows." Не будут ли таких сюрпризов, как при изменении длины? "Another negative side effect of altering tables happens when a column is altered to increase its length. In this case, the old column is not actually replaced. Rather, a new column is added to the table, and DBCC PAGE shows you that the old data is still there." Просто данных очень много, и хотелось бы знать точно, какие именно изменения произойдут при изменении NULLability столбца. Заранее спасибо! |
18 апр 14, 11:12 [15900052] Ответить | Цитировать Сообщить модератору |
NOT NULL
Guest |
в вашей цитате черным по белому написано "изменение NULL - > NOT NULL вызывает поиск NULL значений, но не вызывает изменений данных." Сам поиск может быть не быстрым (скан всей таблицы), если нет подходящего индекса. На это время на таблицу будет наложена schema modification блокировка, которая полностью блокирует доступ к таблице. |
||
18 апр 14, 12:01 [15900365] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Спасибо за ответ! Сечас запустил скрипт из книги: SELECT c.name AS column_name, column_id, max_inrow_length, pc.system_type_id, leaf_offset FROM sys.system_internals_partition_columns pc JOIN sys.partitions p ON p.partition_id = pc.partition_id JOIN sys.columns c ON column_id = partition_column_id AND c.object_id = p.object_id WHERE p.object_id=object_id('dbo.test'); Результаты: 1. NULL
2. NOT NULL
Как видно из результата, изменился сдвиг leaf_offset. Но что это может означать? sys.system_internals_partition_columns недокументирован, но из книги видно, что это сдвиг столбца. Почему тогда он поменялся?) |
||||||||||||||||||||||||||||||||||
18 апр 14, 12:21 [15900499] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
А каждой записи есть маска NULL-вых полей http://www.sqlpassion.at/archive/2011/06/29/the-mystery-of-the-null-bitmap-mask/ |
||
18 апр 14, 12:32 [15900573] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Glory Сейчас картина проясняется, но непонятно, почему сдвиг отрицательный? |
18 апр 14, 13:02 [15900754] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8316 |
Вы хотите об этом поговорить? Что Вас беспокоит? |
18 апр 14, 14:01 [15901174] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Владислав Колосов Если не затруднит, просто объясните, почему сдвиг у val отрицательный, а у id положительный? |
18 апр 14, 14:16 [15901264] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8316 |
Поверьте, что это никак не повлияет на работоспособность приложений. К сожалению, я не могу удовлетворить Ваш академический интерес. |
18 апр 14, 14:35 [15901388] Ответить | Цитировать Сообщить модератору |
Knyazev Alexey Member Откуда: Екб -> Мск Сообщений: 10234 Блог |
create table dbo.test ( id int identity constraint pk_test primary key not null, val varchar(100) null ); /* column_name leaf_offset ----------- ----------- id 4 val -1 */ alter table dbo.test alter column val varchar(100) not null; /* column_name leaf_offset ----------- ----------- id 4 val -2 */ alter table dbo.test alter column val varchar(100) null; alter table dbo.test alter column val varchar(100) not null; /* column_name leaf_offset ----------- ----------- id 4 val -3 */ alter table dbo.test alter column val varchar(100) null; alter table dbo.test alter column val varchar(100) not null; /* column_name leaf_offset ----------- ----------- id 4 val -4 */ |
18 апр 14, 15:17 [15901759] Ответить | Цитировать Сообщить модератору |
Knyazev Alexey Member Откуда: Екб -> Мск Сообщений: 10234 Блог |
create table dbo.t ( a int , b bigint , c char , d varchar , f binary , g varbinary ); К сообщению приложен файл. Размер - 5Kb |
18 апр 14, 15:27 [15901872] Ответить | Цитировать Сообщить модератору |
daw Member Откуда: Муром -> Москва Сообщений: 7381 |
ну, собственно, да. при изменении null/not null добавляется еще одна копия столбца, так же, как и при изменении максимальной длины. у меня, правда, получилось, что не всегда:
leaf_offset ожидаемо означает смещение, по которому можно найти начало столбца. для столбцов с переменной длиной оно для каждой строки может быть разным, поэтому указывается просто порядковый номер столбца (среди var-столбцов) с минусом. |
|
18 апр 14, 15:47 [15902013] Ответить | Цитировать Сообщить модератору |
daw Member Откуда: Муром -> Москва Сообщений: 7381 |
daw, упс, ошибся чуток. добавляется каждый раз, когда с null на not null меняем. |
18 апр 14, 15:50 [15902036] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37143 |
|
||
18 апр 14, 15:57 [15902102] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Всем спасибо за ответы! Получается, что всё-таки создаётся новый столбец? |
18 апр 14, 16:53 [15902429] Ответить | Цитировать Сообщить модератору |
Knyazev Alexey Member Откуда: Екб -> Мск Сообщений: 10234 Блог |
create table dbo.t ( a varchar ); select left( c.name, 5) as column_name , leaf_offset from sys.system_internals_partition_columns pc join sys.partitions p on p.partition_id = pc.partition_id left join sys.columns c on column_id = partition_column_id and c.object_id = p.object_id where p.object_id in ( object_id('dbo.t') ); /* column_name leaf_offset ----------- ----------- a -1 */ alter table t alter column a varchar not null; select left( c.name, 5) as column_name , leaf_offset from sys.system_internals_partition_columns pc join sys.partitions p on p.partition_id = pc.partition_id left join sys.columns c on column_id = partition_column_id and c.object_id = p.object_id where p.object_id in ( object_id('dbo.t') ); /* column_name leaf_offset ----------- ----------- NULL -1 a -2 */ |
||
18 апр 14, 17:21 [15902571] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Knyazev Alexey Спасибо за ответ! Теперь всё понятно. :) |
19 апр 14, 12:11 [15904442] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Что создаётся, зачем создаётся. Есть физическая структура страниц таблицы, есть внутренние метаданные, и есть внешняя логическая структура. Что показывает скрипт от Knyazev Alexey я не догоняю. Проделав изрядное количество ALTER над колонкой (NULL <-> NOT NULL) в итоге таблица стала недоступной из-за предела размера 8060. Что хотели сделать разрабы 2008 скуля я так и не понял. Индейское племя Нахуа негодуэ. Вот когда делается ALTER COLUMN [X] NOT NULL DEFAULT (Y), то панимаете всё пучком - структура таблы не меняется, только метаданные, команда срабатывает мгновенно. А в этой ситуации зачем создавать новую колонку? Получается двойное изменение кластерного не обойти? (ALTER + REBUILD) Получается поставить тупой CONSTRAINT [NN_Column] CHECK ([Column] NOT NULL) намного эффективнее. Привет MS SQL 4.0 #спасибомикрософтзаэто |
||
21 апр 14, 00:22 [15908453] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
простите, а что это за синтаксис: ALTER COLUMN [X] NOT NULL DEFAULT (Y) это точно с ADD COLUMN не попутали? можно было не 100 раз менять (NULL <-> NOT NULL), одного бы хватило. создать сразу таблицу, куда вторая "такая же" колонка явно не влезет и смотреть, будет или нет выделять новые страницы под изменения: create table dbo.t (id int, col varchar(8000)); insert into dbo.t values (1, REPLICATE('a', 8000)), (2, REPLICATE('b', 8000)); dbcc ind ('db6', 'dbo.t', -1); -- 127 iam In-row data -- 126 In-row data -- 148 In-row data alter table dbo.t; alter column col varchar(8000) not null; dbcc ind ('db6', 'dbo.t', -1); -- 127 iam -- 126 In-row data -- 148 In-row data -- 173 iam Row-overflow data -- 151 Row-overflow data -- 154 Row-overflow data ну можно еще и добить посмотреть сами страницы: dbcc traceon (3604); go dbcc page ('db6', 1, 126, 3); и убедиться, что добавил новую колонку, она не влезла, ее выпихнули в Row-overflow data и на нее указатель поместили: Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 id = 1 Slot 0 Column 67108865 Offset 0x11 Length 0 Length (physical) 8000 DROPPED = NULL col = [BLOB Inline Root] Slot 0 Column 2 Offset 0x1f51 Length 24 Length (physical) 24 Level = 0 Unused = 0 UpdateSeq = 1 TimeStamp = 1195704320 Link 0 Size = 8000 RowId = (1:151:0) |
21 апр 14, 02:04 [15908663] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47047 |
ALTER TABLE [TableName] ALTER COLUMN [ColumnName] .....; |
||
21 апр 14, 09:08 [15908989] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
iap, сама поставилась, и явно ПОТОМ, иначе бы не сработало :) |
21 апр 14, 10:31 [15909332] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Зашибись просто. Значит, автор книги соврала, что "when you change the nullability property to disallow NULLs, changes to a table’s structure require that the data be examined but not modified". Автор делает акцент на том, что именно изменение длины поля оставляет не удаляет старое поле. А тут получается, что изменение NULLability делает то же самое. Интересно, а это где-нибудь документировано? |
21 апр 14, 11:11 [15909588] Ответить | Цитировать Сообщить модератору |
Knyazev Alexey Member Откуда: Екб -> Мск Сообщений: 10234 Блог |
если поле увеличиваем, то оно не пересоздаётся...а вот если уменьшаем или меняем тип... |
21 апр 14, 11:42 [15909799] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
Knyazev Alexey Алексей, извините, но как-то не вяжется Ваш вывод с автором, который говорит, что "Another negative side effect of altering tables happens when a column is altered to increase its length. In this case, the old column is not actually replaced." Это во-первых. А во-вторых, я вопрос задавал про NULLability, а не про длину поля. ) |
21 апр 14, 11:47 [15909839] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
о, мы не одни заметили, вот у товарища на большой таблице смена NULL на NOT NULL привела к росту лога на > 30 Gb, это явно не "скромное изменение метаданных". Why does ALTER COLUMN to NOT NULL cause massive log file growth? ...But it's clear that - while both the update operation and the addition of the NOT NULL constraint undeniably write to the page - the latter does so in an entirely different way. It seems to actually change the structure of the record, rather than just fiddle with bits, by swapping out the nullable column for a non-nullable column. Why it has to do that, I'm not quite sure - a good question for the storage engine team, I guess. |
21 апр 14, 11:48 [15909840] Ответить | Цитировать Сообщить модератору |
MrVoid Member Откуда: Сообщений: 333 |
o-o Вот это то, что нужно! Спасибо за ссылку. :) |
21 апр 14, 11:50 [15909854] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |