I have something like this:
CREATE TABLE T1 (
Id INT
...
,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id]
)
CREATE TABLE T2 (
....
,T1_Id INT NOT NULL
,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)
For performance (and deadlock) reasons I created a new index on T1
CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)
But if I check to which Index references the FK, keeps referencing to the clustered index
select
ix.index_id,
ix.name as index_name,
ix.type_desc as index_type_desc,
fk.name as fk_name
from sys.indexes ix
left join sys.foreign_keys fk on
fk.referenced_object_id = ix.object_id
and fk.key_index_id = ix.index_id
and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');
If I drop the constraint and create again it references the nonclustered index, but this lead to check all t2 FK again.
Is there a way to change this so the FK_T2_T1 uses IX_T1_Id instead of PK_T1 without dropping the FK and locking the table on FK checking?
Thanks!
Best Answer
Well, after continue searching I found this article
Whereupon, unless someone can say otherwise, I will have to look for a time window to perform this task.
Thanks