I decided to run a quick experiment to see if I could find any limits. Here is the code I used to create a table with lots of foreign keys:
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'QotD')
ALTER DATABASE QotD SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE QotD;
CREATE DATABASE QotD;
DECLARE @Base nvarchar(MAX) = N'',
@Ref nvarchar(MAX) = N'CREATE TABLE dbo.Ref(PKCol int NOT NULL, ';
DECLARE @max-2 int = 1000,
@i int = 1;
WHILE @i < @max-2
SET @Base += N'CREATE TABLE dbo.T' + CAST(@i AS nvarchar(20))
+ N'(Col' + CAST(@i AS nvarchar(20)) + N' int NOT NULL PRIMARY KEY); ';
SET @Ref += N'Col' + CAST(@i AS nvarchar(20))
+ N' int NOT NULL DEFAULT(1) REFERENCES dbo.T' + CAST(@i AS nvarchar(20)) + N', ';
SET @i += 1;
SET @Ref += N'PRIMARY KEY(PKCol));'
-- Execute the above, then select the below and execute to check time, or get estimated plan for fun
SET STATISTICS TIME ON;
INSERT INTO dbo.Ref DEFAULT VALUES;
SET STATISTICS TIME OFF;
By changing the value of @max-2, I could specify the number of foreign keys. It will create lots of tables, but you can just drop the QotD database afterwards.
I didn't see a noticeable performance difference between 250 and 260. I noticed a slight slowdown at 500, and a severe slowdown at 750. At a 1000, the query failed with error 701: insufficient system memory in resourcepool 'default' to run the query. I got the same error when requesting the estimated plan, so in spite of the error message, I think that the compilation failed. More memory (I have 8GB installed, 6GB available to SQL Server) might have helped.
I was impressed to see that Management Studio was able to render the graphical plan for up to 250 foreign keys - SQL Sentry Plan Explorer already crashed at "only" 200 foreign keys.