HoneyHoney

同事问了一个问题，在drop default constraint之后，是否会引起大的IO。答案是，No。

其实两年我就问过这个问题，结果还是被无情地打脸了，忘记完了。
https://dba.stackexchange.com/questions/90771/default-value-stays-after-i-dropped-the-default-constraint

长话短说，在每次创建default constraint的时候，在system_internals_partition_columns表内都会记录default value，从而实现所谓的metadata change。即sql server 2012以后，增加一个NOT NULL WITH DEFAULT CONSTRAINT是metadata change。随后删除这个constraint并不会改变system_internals_partition_columns里面值，也不会引起大的IO。


http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null
);

insert into dbo.LobData(ID) 
values (1);


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 610871, 3); 
GO


ALTER TABLE LobData ADD DATA VARCHAR(20) NOT NULL constraint df_lobdata default('yes')

insert into dbo.LobData(ID) 
values (2);

select * from LobData

ALTER TABLE LOBDATA DROP CONSTRAINT df_lobdata;


ALTER TABLE LobData ADD constraint df_lobdata default('no') for data;

insert into dbo.LobData(ID) 
values (3);

select * from LobData


select pc.* 
from sys.system_internals_partitions p
join sys.system_internals_partition_columns pc on p.partition_id = pc.partition_id
where p.object_id = object_id('lobdata');