sql – 是否可以向外键添加逻辑约束？

Animals
^^^^^^^
AnimalId INT PK NOT NULL IDENTITY
AnimalType Tinyint nOT NULL -- 1: MAMMal,2:reptile,etc..
Name

MAMMals
^^^^^^^
AnimalId INT PK FK NOT NULL
numberOfMamMaryGlads Tinyint nOT NULL

我运行了一个小基准测试 – 在这种情况下,使用UDF的方法运行速度差了近100倍.

cpu时间中FK的开销= 375 ms – 297 ms = 78 ms

cpu时间中UDF的开销= 7750 ms – 297 ms = 7453 ms

这是sql代码……

– 设置128K行的辅助表数字：

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

– 表格

create table dbo.Animals
(AnimalId int nOT NULL IDENTITY PRIMary KEY,AnimalType Tinyint nOT NULL,-- 1: MAMMal,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

—使用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;

结果是：

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,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,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,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,lob read-ahead reads 0.
Table 'MAMMals2'. Scan count 0,logical reads 275381,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,'some info' FROM dbo.numbers;

结果是：
sql Server解析和编译时间：
cpu时间= 1 ms,经过时间= 1 ms.

sql Server Execution Times:
   cpu time = 0 ms,elapsed time = 66 ms.
Table 'MAMMals3'. Scan count 0,lob read-ahead reads 0.

sql Server Execution Times:
   cpu time = 297 ms,elapsed time = 303 ms.

(131072 row(s) affected)

cpu时间中FK的开销= 375 ms – 297 ms = 78 mscpu时间中UDF的开销= 7750 ms – 297 ms = 7453 ms