Dlaczego nie można indeksować po polu varchar(max)

14-Sie-2011

Zacznijmy od tego co to jest varchar(max). Dawno temu, w wersji SQL 2000 kiedy chciałeś umieścić w tabeli duże pole z zawartością tekstową mogłeś skorzystać z pola typu text. W Accessie zaś odpowiednikiem varchar(max) jest typ nota. Zazwyczaj definiując tabelę i jej kolumny, określasz rozmiar przechowywanych danych oraz sposób kodowania np varchar(20) lub nvarchar(20), kiedy jednak wiesz, że w polu ma być przechowywane coś dużego, to odgadywanie tej wielkości jest problematyczne… może varchar(100), a może varchar(1000)….

W takim przypadku można zdecydować się na przechowywanie w polu po prostu dowolnie dużej dopuszczalnej wielkości i to jest właśnie varchar(max). To właściwie dlaczego nie wykorzystywać by w swoich tabelach samych typów varchar(max)? Przecież dzięki temu użytkownicy nigdy by nie mieli problemów ze zmieszczeniem swoich danych w tabelach!

Już odpowiadam. Nie należy tego robić ze względu na sposób przechowywania tych danych w pliku bazy danych. Każda tabela jest pewną fizyczną strukturą, bądź to stosem (heap) bądź indeksem (zbalansowanym drzewem). Nieważne czy jest to stos, czy też indeks dane znajdują się w stronach. Strona ba rozmiar 8KB, z czego część jest rezerwowana na sprawy „systemowe” i do wykorzystania zostaje 8060 bajtów. Rekord nie może się rozciągać na więcej stron niż jedna, dlatego, jeżeli chcesz w nim przechowywać „coś dużego” musisz to zrobić poza strukturą rekordu, poza stosem czy indeksem.

Wpisanie, że kolumna ma typ varchar(max) powoduje, że w rekordzie jest przechowywany jedynie 24-bitowy wskaźnik do innej struktury, która w sobie zawiera te „duże dane”. Serwer SQL nie byłby serwerem SQL, gdyby te duże dane nie były przechowywane również w postaci struktury czy indeksu. Tak więc ów duży obiekt (zwany też LOB lub BLOB) jest dzielony na mniejsze kawałki i układany w postaci stosu gdzieś w bazie danych, a w rekordzie pozostaje wskaźnik do niego.

Indeksowanie powinno się odbywać w oparciu o dane rekordu, które w tym przypadku znajdują się poza rekordem i dlatego nie można na podstawie takiej kolumny indeksować. Stąd też podczas próby poindeksowania wg pola o typie varchar(max) otrzymujesz:

Msg 1919, Level 16, State 1, Line 1

Column ‚name’ in table ‚TestTable’ is of a type that is invalid for use as a key column in an index.

Maksymalny rozmiar przechowywanych danych w polu varchar(max) to 2^31.

Dodaj komentarz:

Autor: Rafał Kraik