Spis treści:

Kategoria:IndeksySQL Server


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 @inputTable nvarchar(128) = 'Tabela'
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.tablesON 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.columnsON 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:

Msg 3725, Level 16, State 0, Line 1
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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?