Can I create a new column with default and a foreign key as a metadata-only operation?

Question

It is possible to create a new column with a default value as a metadata-only operation (not a size-of-data operation):

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL 
    DEFAULT 0;

But is it possible to also create such a column that references another table as a metadata-only operation?

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL 
    DEFAULT 0 
    REFERENCES dbo.MyThing(MyThingId);

In theory it's possible because there should only be one value to check in the referenced table. However, in my example, the foreign key creation seems to be a size-of-data operation.

Answer 1

You can do this, but only if you don't check the FK on creation. The Foreign Key will be enforced for subsequent INSERT and UPDATEs, but won't be trusted by the Query Optimizer. EG:

drop table if exists MyReallyBigTable
drop table if exists MyThing
go
select o.* into dbo.MyReallyBigTable
from sys.objects o, sys.columns c

go
create table MyThing(MyThingId int primary key )
insert into MyThing(MyThingId) values (0)

set statistics io on
go
ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL 
    DEFAULT 0 
    --REFERENCES dbo.MyThing(MyThingId);
go
alter table MyReallyBigTable
with nocheck
add constraint  fk_MyReallyBigTable_MyThing 
foreign key (MyThingId) references MyThing(MyThingId)
set statistics io off

You can come back later when you have time for a table scan and check all the rows to make the FK trusted:

select name, is_not_trusted 
from sys.foreign_keys
go
alter table MyReallyBigTable with check check constraint fk_MyReallyBigTable_MyThing
go
select name, is_not_trusted 
from sys.foreign_keys