Will FK validation in SQL Server always use the index specified in sys.foreign_keys?
I can't find anything discussing the case where there are multiple possible indexes that could be used to backup a FK constraint.
It seems from the test below that at FK creation time the FK gets bound to a specific index and this will always be used to validate the FK constraint irrespective of whether a new better index gets added later.
Can any one point to any resources confirming or denying this?
CREATE TABLE T1(
T1_Id INT IDENTITY(1,1) PRIMARY KEY CLUS开发者_运维知识库TERED NOT NULL,
Filler CHAR(4000) NULL,
)
INSERT INTO T1 VALUES ('');
CREATE TABLE T2(
T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
Filler CHAR(4000) NULL,
)
/*Execution Plan uses clustered index - There is no NCI*/
INSERT INTO T2 VALUES (1,1)
ALTER TABLE T1 ADD CONSTRAINT
UQ_T1 UNIQUE NONCLUSTERED(T1_Id)
/*Execution Plan still use clustered index even after NCI created*/
INSERT INTO T2 VALUES (1,1)
SELECT fk.name,
ix.name,
ix.type_desc
FROM sys.foreign_keys fk
JOIN sys.indexes ix ON ix.object_id = fk.referenced_object_id
AND ix.index_id = fk.key_index_id
WHERE fk.name = 'FK'
ALTER TABLE T2 DROP CONSTRAINT FK
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)
/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)
SELECT fk.name,
ix.name,
ix.type_desc
FROM sys.foreign_keys fk
JOIN sys.indexes ix ON ix.object_id = fk.referenced_object_id
AND ix.index_id = fk.key_index_id
WHERE fk.name = 'FK'
DROP TABLE T2
DROP TABLE T1
Martin, I apologize that this isn't much of an answer, but since you made me curious as well, I did some digging. The information that I can share is:
It is unlikely in current versions, including Denali, that an alternative would ever be considered in this situation.
精彩评论