Testing foreign key effects on insert performance

fk.sql

USE testkeys;
GO

CREATE TABLE parent (
    id INT NOT NULL,
    filler CHAR(96) DEFAULT (REPLICATE('A', 96))
);

ALTER TABLE dbo.parent
ADD CONSTRAINT ParentPK PRIMARY KEY CLUSTERED (id);


CREATE TABLE child (
    id INT IDENTITY(1,1) NOT NULL,
    parent_id INT NOT NULL
);

ALTER TABLE dbo.child
ADD CONSTRAINT ChildPK PRIMARY KEY CLUSTERED (id);

ALTER TABLE dbo.child
ADD CONSTRAINT ParentFK FOREIGN KEY (parent_id)
    REFERENCES dbo.parent(id);






DELETE FROM dbo.child;
DELETE FROM dbo.parent;


INSERT INTO parent (id)
SELECT number
FROM    master..spt_values
WHERE type = 'P'

/* verify the page count. this step is optional */
DBCC IND('testkeys', 'parent', 1);




SET NOCOUNT ON;

DECLARE @start DATETIME2(7), @end DATETIME2(7),
        @max INT = 1000000, @batch_size INT = 5000,
        @counter INT = 0 ;

WHILE @counter < @max
BEGIN
    SET @start = SYSDATETIME();

    WITH x AS (
        SELECT  number 
        FROM    master..spt_values 
        WHERE   type = 'P'
    )
    INSERT INTO dbo.child (parent_id)
    SELECT  TOP (@batch_size)
            FLOOR(RAND(CAST(CAST(NEWID() AS VARBINARY(MAX)) AS INT)) * 2048)
    FROM    x AS x1
            CROSS APPLY x AS x2

    SET @end = SYSDATETIME();

    PRINT DATEDIFF(ms, @start, @end);

    SET @counter += @batch_size;
END
GO



ALTER TABLE dbo.child
DROP CONSTRAINT ParentFK;

TRUNCATE TABLE dbo.child;
TRUNCATE TABLE dbo.parent;

INSERT INTO parent (id)
SELECT number
FROM    master..spt_values
WHERE type = 'P';



DECLARE @start DATETIME2(7), @end DATETIME2(7),
        @max INT = 1000000, @batch_size INT = 5000,
        @counter INT = 0 ;

WHILE @counter < @max
BEGIN
    SET @start = SYSDATETIME();

    WITH x AS (
        SELECT  number 
        FROM    master..spt_values 
        WHERE   type = 'P'
    )
    INSERT INTO dbo.child (parent_id)
    SELECT  TOP (@batch_size)
            FLOOR(RAND(CAST(CAST(NEWID() AS VARBINARY(MAX)) AS INT)) * 2048)
    FROM    x AS x1
            CROSS APPLY x AS x2

    SET @end = SYSDATETIME();

    PRINT DATEDIFF(ms, @start, @end);

    SET @counter += @batch_size;
END
GO