I ran a small benchmark - in this case the approach with a UDF runs almost 100 times slower.
The overhead of an FK in CPU time = 375 ms - 297 ms = 78 ms
The overhead of an UDF in CPU time = 7750 ms - 297 ms = 7453 ms
Here's the Sql code...
-- set up an auxiliary table Numbers with 128K rows:
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<128000 BEGIN
INSERT INTO dbo.Numbers(n)
SELECT n + @i FROM dbo.Numbers;
SET @i = @i * 2;
END;
GO
-- the tables
CREATE TABLE dbo.Animals
(AnimalId INT NOT NULL IDENTITY PRIMARY KEY,
AnimalType TINYINT NOT NULL, -- 1: Mammal, 2:Reptile, etc..
Name VARCHAR(30))
GO
ALTER TABLE dbo.Animals
ADD CONSTRAINT UNQ_Animals UNIQUE(AnimalId, AnimalType)
GO
CREATE FUNCTION dbo.GetAnimalType(@AnimalId INT)
RETURNS TINYINT
AS
BEGIN
DECLARE @ret TINYINT;
SELECT @ret = AnimalType FROM dbo.Animals
WHERE AnimalId = @AnimalId;
RETURN @ret;
END
GO
CREATE TABLE dbo.Mammals
(AnimalId INT NOT NULL PRIMARY KEY,
SomeOtherStuff VARCHAR(10),
CONSTRAINT Chk_AnimalType_Mammal CHECK(dbo.GetAnimalType(AnimalId)=1)
);
GO
--- populating with UDF:
INSERT INTO dbo.Animals
(AnimalType, Name)
SELECT 1, 'some name' FROM dbo.Numbers;
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
INSERT INTO dbo.Mammals
(AnimalId,SomeOtherStuff)
SELECT n, 'some info' FROM dbo.Numbers;
results are:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Mammals'. Scan count 0, logical reads 272135,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 7750 ms, elapsed time = 7830 ms.
(131072 row(s) affected)
--- populating with FK:
CREATE TABLE dbo.Mammals2
(AnimalId INT NOT NULL PRIMARY KEY,
AnimalType TINYINT NOT NULL,
SomeOtherStuff VARCHAR(10),
CONSTRAINT Chk_Mammals2_AnimalType_Mammal CHECK(AnimalType=1),
CONSTRAINT FK_Mammals_Animals FOREIGN KEY(AnimalId, AnimalType)
REFERENCES dbo.Animals(AnimalId, AnimalType)
);
INSERT INTO dbo.Mammals2
(AnimalId,AnimalType,SomeOtherStuff)
SELECT n, 1, 'some info' FROM dbo.Numbers;
results are:
SQL Server parse and compile time:
CPU time = 93 ms, elapsed time = 100 ms.
Table 'Animals'. Scan count 1, logical reads 132, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table 'Mammals2'. Scan count 0, logical reads 275381, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 383 ms.
-- populating without any integrity:
CREATE TABLE dbo.Mammals3
(AnimalId INT NOT NULL PRIMARY KEY,
SomeOtherStuff VARCHAR(10)
);
INSERT INTO dbo.Mammals3
(AnimalId,SomeOtherStuff)
SELECT n, 'some info' FROM dbo.Numbers;
results are:
SQL Server parse and compile time:
CPU time = 1 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 66 ms.
Table 'Mammals3'. Scan count 0, logical reads 272135, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 303 ms.
(131072 row(s) affected)
The overhead of an FK in CPU time = 375 ms - 297 ms = 78 ms
The overhead of an UDF in CPU time = 7750 ms - 297 ms = 7453 ms