Foreign keys impact on inserts

In my previous post, I’ve demonstrated how Foreign keys can help the optimizer make better decisions when creating an execution plan. However, I’ve also mentioned that FKs make inserts and updates run slower. In this short post I’ll show the actual impact the FK has on these actions.

Why does the insert run slower ?

A foreign key is basically a constraint. It limits the data in the referencing column to a list of values found in the referenced table. This makes SQL Server check the data before inserting it, to make sure the constraint is not violated. This check takes time, but how much time ?

To demonstrate I’ve created 2 tables: NameList (populated with 100 Ids and names) and DataTable that will reference the NameList table.

At first I’ve created the DataTable without the FK.

Transact-SQL

-- Create the list table CREATE TABLE dbo.NameList ( Id INT NOT NULL , Name VARCHAR(10) NOT NULL , CONSTRAINT pk_NameId_c PRIMARY KEY CLUSTERED (Id) ); INSERT INTO dbo.NameList(Id, Name) SELECT TOP 100 ROW_NUMBER()OVER(ORDER BY T1.name) , 'Name_'+CAST(ROW_NUMBER()OVER(ORDER BY T1.name) AS VARCHAR(4)) FROM sys.all_columns AS T1 CROSS JOIN sys.all_columns AS T2; GO -- Create data table CREATE TABLE dbo.DataTable ( NameId INT NOT NULL ); GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

-- Create the list table

CREATE TABLE

dbo.NameList

(

Id INT NOT NULL ,

Name VARCHAR(10) NOT NULL ,

CONSTRAINT pk_NameId_c PRIMARY KEY CLUSTERED (Id)

);