Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
 Поменять NULLability столбца  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
NOT NULL
Guest
MrVoid
Всем добрый день!
вопрос. Сейчас делаю перенос данных в 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 столбца.
Заранее спасибо!


в вашей цитате черным по белому написано "изменение NULL - > NOT NULL вызывает поиск NULL значений, но не вызывает изменений данных."
Сам поиск может быть не быстрым (скан всей таблицы), если нет подходящего индекса.
На это время на таблицу будет наложена schema modification блокировка, которая полностью блокирует доступ к таблице.
18 апр 14, 12:01    [15900365]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
MrVoid
Member

Откуда:
Сообщений: 333
NOT NULL
"изменение NULL - > NOT NULL вызывает поиск NULL значений, но не вызывает изменений данных."
На это время на таблицу будет наложена schema modification блокировка, которая полностью блокирует доступ к таблице.

Спасибо за ответ!
Сечас запустил скрипт из книги:
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
column_name column_id max_inrow_length system_type_id leaf_offset
id 1 4 56 4
val 2 100 167 -1

2. NOT NULL
column_name column_id max_inrow_length system_type_id leaf_offset
id 1 4 56 4
val 2 100 167 -2

Как видно из результата, изменился сдвиг leaf_offset. Но что это может означать? sys.system_internals_partition_columns недокументирован, но из книги видно, что это сдвиг столбца. Почему тогда он поменялся?)
18 апр 14, 12:21    [15900499]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Glory
Member

Откуда:
Сообщений: 104751
MrVoid
Почему тогда он поменялся

А каждой записи есть маска NULL-вых полей
http://www.sqlpassion.at/archive/2011/06/29/the-mystery-of-the-null-bitmap-mask/
18 апр 14, 12:32    [15900573]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
MrVoid
Member

Откуда:
Сообщений: 333
 		Glory
Сейчас картина проясняется, но непонятно, почему сдвиг отрицательный?
18 апр 14, 13:02    [15900754]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9160
 		Вы хотите об этом поговорить? Что Вас беспокоит?
18 апр 14, 14:01    [15901174]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
MrVoid
Member

Откуда:
Сообщений: 333
 		Владислав Колосов
Если не затруднит, просто объясните, почему сдвиг у val отрицательный, а у id положительный?
18 апр 14, 14:16    [15901264]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9160
 		Поверьте, что это никак не повлияет на работоспособность приложений. К сожалению, я не могу удовлетворить Ваш академический интерес.
18 апр 14, 14:35    [15901388]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
 
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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
 
create table dbo.t
(
      a int
    , b bigint
    , c char
    , d varchar
    , f binary
    , g varbinary
);


К сообщению приложен файл. Размер - 5Kb
18 апр 14, 15:27    [15901872]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
 		ну, собственно, да. при изменении null/not null добавляется еще одна копия столбца, так же, как и при изменении максимальной длины. у меня, правда, получилось, что не всегда:

+

use tempdb;

create table t (c varchar(30) null)
insert into t values ('aaa')
insert into t values ('bbb')
go
dbcc traceon (3604)
create table #dbcc_ind (PageFID int, PagePID int, IAMFID int, IAMPID int, ObjectID int, IndexID int
                        , PartitionNumber int, PartitionID bigint, iam_chain_type nvarchar(max), PageType int
                        , IndexLevel int, NextPageFID int, NextPagePID int, PrevPageFID int, PrevPagePID int
                       )

insert into #dbcc_ind
exec('dbcc ind (''tempdb'', t, 1)')

declare @PageID int
set @PageID = (select PagePID from #dbcc_ind where PageType = 1)

dbcc page ('tempdb', 1, @PageID, 1)
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.t');
-- c	1	30	167	-1

print ' alter not null ----------------------------------------------------------------------------------------------------'
alter table t alter column c varchar(30) not null

dbcc page ('tempdb', 1, @PageID, 1)
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.t');
-- c	1	30	167	-2

print ' alter null ----------------------------------------------------------------------------------------------------'
alter table t alter column c varchar(30) null

dbcc page ('tempdb', 1, @PageID, 1)
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.t');
-- c	1	30	167	-2

print ' alter not null ----------------------------------------------------------------------------------------------------'
alter table t alter column c varchar(30) not null

dbcc page ('tempdb', 1, @PageID, 1)
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.t');
-- c	1	30	167	-3

print ' alter null ----------------------------------------------------------------------------------------------------'
alter table t alter column c varchar(30) not null

dbcc page ('tempdb', 1, @PageID, 1)
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.t');
-- c	1	30	167	-3

print ' alter not null ----------------------------------------------------------------------------------------------------'
alter table t alter column c varchar(30) not null

dbcc page ('tempdb', 1, @PageID, 1)
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.t');
-- c	1	30	167	-3

go
drop table t
go
drop table #dbcc_ind;



leaf_offset ожидаемо означает смещение, по которому можно найти начало столбца. для столбцов с переменной длиной оно для каждой строки может быть разным, поэтому указывается просто порядковый номер столбца (среди var-столбцов) с минусом.
18 апр 14, 15:47    [15902013]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
 		daw,

упс, ошибся чуток. добавляется каждый раз, когда с null на not null меняем.
18 апр 14, 15:50    [15902036]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
NOT NULL
Сам поиск может быть не быстрым (скан всей таблицы), если нет подходящего индекса.
А если есть подходящий индекс, то его придется снести, ибо нельзя альтерить поля в индексе.
18 апр 14, 15:57    [15902102]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
MrVoid
Member

Откуда:
Сообщений: 333
 		Всем спасибо за ответы! Получается, что всё-таки создаётся новый столбец?
18 апр 14, 16:53    [15902429]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
MrVoid
Получается, что всё-таки создаётся новый столбец?


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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
MrVoid
Member

Откуда:
Сообщений: 333
 		Knyazev Alexey
Спасибо за ответ! Теперь всё понятно. :)
19 апр 14, 12:11    [15904442]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
MrVoid
Теперь всё понятно
А мне нифига не понятно.
Что создаётся, зачем создаётся. Есть физическая структура страниц таблицы, есть внутренние метаданные, и есть внешняя логическая структура.

Что показывает скрипт от 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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
o-o
простите, а что это за синтаксис: ALTER COLUMN [X] NOT NULL DEFAULT (Y)
это точно с ADD COLUMN не попутали?
alter table dbo.t;
alter column col varchar(8000) not null;
Точка с запятой зачем?
ALTER TABLE [TableName] ALTER COLUMN [ColumnName] .....;
21 апр 14, 09:08    [15908989]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
o-o
Guest 		iap,
сама поставилась, и явно ПОТОМ, иначе бы не сработало :)
21 апр 14, 10:31    [15909332]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
 		если поле увеличиваем, то оно не пересоздаётся...а вот если уменьшаем или меняем тип...
21 апр 14, 11:42    [15909799]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Поменять NULLability столбца  [new]
MrVoid
Member

Откуда:
Сообщений: 333
 		o-o
Вот это то, что нужно! Спасибо за ссылку. :)
21 апр 14, 11:50    [15909854]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить