Jak zamienić indeks nieklastrowany na klastrowany
Po co nam klucz klastrowany?
Znów pojawia się artykuł wzięty prosto z życia. Przypuśćmy, że istnieje sobie baza, w której niektóre tabele nie mają indeksu klastrowanego. Obserwujemy sobie plany wykonania i widzimy, że pojawiają się tam niepotrzebne operacje RID Lookup. Dlaczego niepotrzebne? Ano dlatego, że wyszukiwanie działa na kluczu głównym, na którym założone jest identity. Zapytanie najpierw odnajduje identyfikator w indeksie nieklastrowanym, a potem dopiero, korzystając z RIDa namierza pozostałe dane z rekordu. W przypadku klucza klastrowanego dane przechowywane są razem z kluczem i SQL Server nie musi wykonywać żadnych dodatkowych instrukcji dostępu do danych. Jak zatem taki klucz zmienić? Odpowiedź wbrew pozorom nie jest taka prosta. Nie ma uniwersalnej metody ALTER TABLE, ani ALTER INDEX. Trzeba to zrobić "na piechotę". Pomocny może być następujący skrypt:
DECLARE @inputSchema nvarchar(128) = 'dbo'
DECLARE @schema nvarchar(128)
DECLARE @table nvarchar(128)
DECLARE @key nvarchar(128)
DECLARE @columns nvarchar(256)
DECLARE @object_id int
DECLARE @index_id int
SELECT @schema=S.name, @table=T.name, @key=K.name,
@object_id=T.[object_id], @index_id=K.unique_index_id
FROM sys.key_constraints K
JOIN sys.tables T ON K.[parent_object_id]=T.[object_id]
JOIN sys.schemas S ON S.[schema_id]=T.[schema_id]
WHERE K.[type]='PK' AND S.name=@inputSchema AND T.name=@inputTable
SELECT @columns=COALESCE(@columns+',', '')+name FROM sys.index_columns I
JOIN sys.columns C ON C.[object_id]=@object_id AND C.column_id=I.column_id
WHERE I.[object_id]=@object_id AND I.index_id=@index_id
SELECT 'ALTER TABLE '+
@schema+'.'+@table+' DROP CONSTRAINT '+@key
SELECT 'ALTER TABLE '+
@schema+'.'+@table+' ADD CONSTRAINT '+@key+
' PRIMARY KEY CLUSTERED ('+@columns+')'
Jest to bardzo uboga wersja skryptu. Co on robi? Po pierwsze, pobiera informacje o istniejącym kluczu głównym, który z założenia powinien być klastrowany (jeżeli jest nieklastrowany, też zadziała). Po drugie, tworzy listę kolumn, które w tym kluczu się znajdują. Te i inne informacje pobrane przy okazji pozwalają na wygenerowanie instrukcji służących do usunięcia klucza nieklastrowanego i wstawienie na jego miejsce klucza klastrowanego.
Co się może nie udać?
Najczęstszym błędem jest obecność kluczy obcych wskazujących na tę tabelę. SQL Server wyświetli nam wtedy następujący komunikat:
The constraint 'Klucz' is being referenced by table 'TabelaObca', foreign key constraint 'Klucz_Obcy'.
Wygenerowanie skryptu radzącego sobie z tym problemem jest już nieco trudniejsze i zostanie opisane w odddzielnym artykule. Zobacz jak zamienić indeks nieklastrowany na klastrowany, gdy kolumna znajduje się w kluczach obcych.
Kategoria:IndeksySQL Server
Brak komentarzy - bądź pierwszy