Zmień indeks odniesienia dla klucza obcego

Mam coś takiego:

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)
)

Ze względu na wydajność (i impas) stworzyłem nowy indeks na T1

CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)

Ale jeśli sprawdzę, do którego indeksu odwołuje się FK, nadal odwołuje się do indeksu klastrowego

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');

Jeśli porzucę ograniczenie i utworzę ponownie, odwołuje się on do indeksu nieklastrowanego, ale prowadzi to do ponownego sprawdzenia wszystkich t2 FK.

Czy istnieje sposób, aby to zmienić, więc FK_T2_T1 używa IX_T1_Id zamiast PK_T1 bez upuszczania FK i blokowania tabeli podczas sprawdzania FK?

Dzięki!

Po dalszych poszukiwaniach znalazłem ten artykuł

W przeciwieństwie do zwykłego zapytania, nie pobierze nowego indeksu z powodu aktualizacji statystyk, utworzenia nowego indeksu, a nawet ponownego uruchomienia serwera. Jedyny sposób, w jaki jestem świadomy powiązania FK z innym indeksem, to upuszczenie i ponowne utworzenie FK, pozwalając mu automatycznie wybrać indeks bez opcji ręcznego sterowania nim.

Po czym, chyba że ktoś może powiedzieć inaczej, będę musiał szukać okna czasowego na wykonanie tego zadania.

Dzięki

Po przeczytaniu MS DOCS tutaj .

Aby zmodyfikować klucz obcy

Aby zmodyfikować ograniczenie klucza OBCEGO za pomocą języka Transact-SQL, należy najpierw usunąć istniejące ograniczenie klucza OBCEGO, a następnie utworzyć je ponownie z nową definicją. Aby uzyskać więcej informacji, zobacz Usuwanie relacji klucza obcego i Tworzenie relacji klucza obcego.

W twoim przypadku uważam, że dodaj nowy FK i usuń stary. Aby wyłączyć skanowanie, możesz użyć NO CHECKopcji

--DROP TABLE T2
--DROP TABLE T1


CREATE TABLE T1 (
    [Id] INT,
    [NAME] varchar(100), CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED (id))

CREATE TABLE T2 (
    t2_id int,
    T1_Id INT NOT NULL
    ,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)


CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)


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');



╔══════════╦════════════╦═════════════════╦══════════╗
║ index_id ║ index_name ║ index_type_desc ║ fk_name  ║
╠══════════╬════════════╬═════════════════╬══════════╣
║        1 ║ PK_T1      ║ CLUSTERED       ║ FK_T2_T1 ║
║        2 ║ IX_T1_Id   ║ NONCLUSTERED    ║ NULL     ║
╚══════════╩════════════╩═════════════════╩══════════╝




 ALTER TABLE T2
    WITH NOCHECK 
    ADD CONSTRAINT [FK_T2_T1_NEW] FOREIGN KEY(T1_Id)
    REFERENCES T1(Id)

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');


╔══════════╦════════════╦═════════════════╦══════════════╗
║ index_id ║ index_name ║ index_type_desc ║   fk_name    ║
╠══════════╬════════════╬═════════════════╬══════════════╣
║        1 ║ PK_T1      ║ CLUSTERED       ║ FK_T2_T1     ║
║        2 ║ IX_T1_Id   ║ NONCLUSTERED    ║ FK_T2_T1_NEW ║
╚══════════╩════════════╩═════════════════╩══════════════╝   

ALTER TABLE T2  
DROP CONSTRAINT FK_T2_T1 

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');


╔══════════╦════════════╦═════════════════╦══════════════╗
║ index_id ║ index_name ║ index_type_desc ║   fk_name    ║
╠══════════╬════════════╬═════════════════╬══════════════╣
║        1 ║ PK_T1      ║ CLUSTERED       ║ NULL         ║
║        2 ║ IX_T1_Id   ║ NONCLUSTERED    ║ FK_T2_T1_NEW ║
╚══════════╩════════════╩═════════════════╩══════════════╝

Sprawdź, czy to działa, próbuję dodać jeszcze jeden FK, aby nowy był połączony z utworzonym nowym indeksem i upuścił stary FK. Wiem, że pytanie nie polega na usunięciu istniejącego, ale sprawdzeniu, czy ta opcja ci pomoże.

Ponadto, zgodnie z komentarzami Maxa Vernona: „opcja Z NOCHECK zapobiegnie zaufaniu klucza obcego przez optymalizator. W pewnym momencie będziesz musiał zmienić klucz obcy, aby był zaufany za pomocą ALTER TABLE ... Z KONTROLĄ ”

NOCHECKBędą ignorowane tylko w momencie tworzenia, ale do egzekwowania integralności contraint masz uruchomić to w pewnym momencie.