Korzystanie z varchar (MAX) vs TEXT na SQL Server

Właśnie przeczytałem, że VARCHAR(MAX)typ danych (który może przechowywać blisko 2 GB danych char) jest zalecanym zamiennikiem TEXTtypu danych w wersjach SQL Server 2005 i Next SQL SERVER.

Jeśli chcę wyszukać w kolumnie dowolny ciąg znaków, która operacja jest szybsza?

1. Używasz LIKEklauzuli przeciwko VARCHAR(MAX)kolumnie?

   WHERE COL1 LIKE '%search string%'

2. Za pomocą TEXTkolumny i umieść w niej pełny indeks / katalog pełnotekstowy, a następnie wyszukaj za pomocą CONTAINSklauzuli?

   WHERE CONTAINS (Col1, 'MyToken')

Ten VARCHAR(MAX)typ zastępuje TEXT. Podstawowa różnica polega na tym, że TEXTtyp zawsze będzie przechowywać dane w obiekcie blob, podczas gdy VARCHAR(MAX)typ będzie próbował przechowywać dane bezpośrednio w wierszu, chyba że przekroczy ograniczenie 8k, i w tym momencie przechowuje je w obiekcie blob.

Użycie instrukcji LIKE jest identyczne między dwoma typami danych. Dodatkowa funkcjonalność VARCHAR(MAX)daje Ci to, że jest to również może być używany z =i GROUP BYjak każda inna VARCHARkolumna może być. Jeśli jednak masz dużo danych, będziesz mieć ogromny problem z wydajnością przy użyciu tych metod.

W odniesieniu do tego, czy należy LIKEwyszukiwać, czy korzystać z indeksowania pełnotekstowego i CONTAINS. To pytanie jest takie samo niezależnie od VARCHAR(MAX)lub TEXT.

Jeśli szukasz dużej ilości tekstu, a wydajność jest kluczowa, powinieneś użyć indeksu pełnotekstowego .

LIKE jest prostszy do wdrożenia i często nadaje się do małych ilości danych, ale ma wyjątkowo niską wydajność z dużymi danymi z powodu niemożności użycia indeksu.

W przypadku dużego tekstu indeks pełnotekstowy jest znacznie szybszy. Ale możesz również indeksować pełny tekst varchar(max) .

Nie można przeszukiwać pola tekstowego bez konwersji go z tekstu na varchar.

declare @table table (a text)
insert into @table values ('a')
insert into @table values ('a')
insert into @table values ('b')
insert into @table values ('c')
insert into @table values ('d')


select *
from @table
where a ='a'

Daje to błąd:

The data types text and varchar are incompatible in the equal to operator.

Wheras to nie:

declare @table table (a varchar(max))

Co ciekawe, LIKEnadal działa, tj

where a like '%a%'

• Podstawowa definicja

TEXTi VarChar(MAX)są typami danych o dużej długości o zmiennej długości, które nie obsługują kodu Unicode, i które mogą przechowywać maksymalnie 2147483647 znaków bez kodu Unicode (tzn. maksymalna pojemność to 2 GB).

• Którego użyć?

Zgodnie z linkiem MSDN Microsoft sugeruje, aby unikać używania typu danych Text i zostanie on usunięty w przyszłych wersjach Sql Server. Varchar (Max) jest sugerowanym typem danych do przechowywania dużych wartości ciągu zamiast typu danych Text.

• Pamięć w rzędzie lub poza rzędem

Dane Textkolumny typu są przechowywane poza wierszem na osobnych stronach danych LOB. Wiersz na stronie danych tabeli będzie miał tylko 16-bajtowy wskaźnik do strony danych LOB, na której znajdują się rzeczywiste dane. Podczas gdy dane Varchar(max)typu kolumna jest przechowywana w rzędzie, jeśli jest mniejsza lub równa 8000 bajtów. Jeśli wartość kolumny Varchar (maks.) Przekracza 8000 bajtów, wówczas wartość kolumny Varchar (maks.) Jest przechowywana na osobnych stronach danych LOB, a wiersz będzie miał tylko 16-bajtowy wskaźnik do strony danych LOB, na której znajdują się rzeczywiste dane. Tak więc In-RowVarchar (Max) jest dobry do wyszukiwania i pobierania.

• Obsługiwane / nieobsługiwane funkcje

Niektóre funkcje łańcuchowe, operatory lub konstrukcje, które nie działają na kolumnie typu Tekst, ale działają na kolumnie typu VarChar (Max).

1. = Równa się operatorowi w kolumnie typu VarChar (Max)

2. Grupuj według klauzuli w kolumnie typu VarChar (Max)

   • Uwagi dotyczące systemu IO

Jak wiemy, wartości kolumn typu VarChar (Max) są przechowywane poza wierszem tylko wtedy, gdy długość wartości, która ma być w nim zapisana, jest większa niż 8000 bajtów lub nie ma wystarczającej ilości miejsca w wierszu, w przeciwnym razie będzie przechowywana to w rzędzie. Jeśli więc większość wartości przechowywanych w kolumnie VarChar (Max) jest duża i przechowywana poza wierszem, zachowanie pobierania danych będzie prawie podobne do zachowania w kolumnie Typ tekstu.

Ale jeśli większość wartości przechowywanych w kolumnach typu VarChar (Max) jest wystarczająco mała, aby przechowywać w rzędzie. Następnie wyszukiwanie danych, w których nie uwzględniono kolumn LOB, wymaga odczytania większej liczby stron danych, ponieważ wartość kolumny LOB jest przechowywana w wierszu na tej samej stronie danych, na której przechowywane są wartości kolumn inne niż LOB. Ale jeśli wybrane zapytanie zawiera kolumnę LOB, wymaga mniejszej liczby stron do odczytania w celu pobrania danych w porównaniu do kolumn typu Tekst.

Wniosek

Użyj VarChar(MAX)typu danych zamiast TEXTdobrej wydajności.

Źródło

Jeśli używasz MS Access (szczególnie starszych wersji, takich jak 2003), musisz użyć TEXTtypu danych na SQL Server, ponieważ MS Access nie rozpoznaje nvarchar(MAX)pola Nota w programie Access, ale TEXTjest rozpoznawane jako pole Nota.