我运行了一个小型基准测试-在这种情况下，使用UDF的方法运行速度几乎慢了100倍。
FK在CPU时间中的开销= 375 ms-297 ms = 78 ms
UDF在CPU时间中的开销= 7750毫秒-297毫秒= 7453毫秒
这是Sql代码…
-设置一个具有128K行的辅助表编号：
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)GODECLARE @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
-桌子
CREATE TABLE dbo.Animals(AnimalId INT NOT NULL IDENTITY PRIMARY KEY,AnimalType TINYINT NOT NULL, -- 1: Mammal, 2:Reptile, etc..Name VARCHAr(30))GOALTER TABLE dbo.AnimalsADD ConSTRAINT UNQ_Animals UNIQUE(AnimalId, AnimalType)GOCREATE FUNCTION dbo.GetAnimalType(@AnimalId INT)RETURNS TINYINTASBEGINDECLARE @ret TINYINT;SELECt @ret = AnimalType FROM dbo.Animals WHERe AnimalId = @AnimalId;RETURN @ret;ENDGOCREATE TABLE dbo.Mammals(AnimalId INT NOT NULL PRIMARY KEY,SomeOtherStuff VARCHAr(10),ConSTRAINT Chk_AnimalType_Mammal CHECK(dbo.GetAnimalType(AnimalId)=1));GO
-–用UDF填充：
INSERT INTO dbo.Animals (AnimalType, Name)SELECt 1, 'some name' FROM dbo.Numbers;GOSET STATISTICS IO onSET STATISTICS TIME onGOINSERT INTO dbo.Mammals(AnimalId,SomeOtherStuff)SELECt n, 'some info' FROM dbo.Numbers;
结果是：
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)
-用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;
结果是：
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.
-没有任何完整性地填充：
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;
结果是：
SQL Server解析和编译时间：CPU时间= 1毫秒，经过的时间= 1毫秒。
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)
FK在CPU时间中的开销= 375 ms-297 ms = 78 ms
UDF在CPU时间中的开销= 7750 ms-297 ms = 7453 ms
