Create foreign key on table with large size

Question

When I use the following SQL Server command to add foreign key

ALTER TABLE [TABLE_A]
WITH CHECK ADD CONSTRAINT [FK_TABLE_A_TABLE_B]
FOREIGN KEY ([COLUMN_A]) REFERENCES [TABLE_B] ([COLUMN_A])

it will run very slow on tables with large size. Is there any faster/optimized way to add foreign key on table with large size?

Thanks!

Answer 1

There is no online way to add a foreign key but you can do two things to make it faster:

1. Create the two minimal indexes on the columns involved online. That reduces the amount of data to be read and it enables a merge join plan. You can use the "Showplan XML" profiler event to obtain the plan and validate it.
2. Pull the indexes into memory: select count(*) from TABLE_A with (index(IX_TABLE_A_COLUMN_A)).

That reduces the time it takes to build a (trusted) foreign key.