Korzystanie z varchar (MAX) vs TEXT na SQL Server


195

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


1
Ten post jest również pomocny: stackoverflow.com/questions/564755/…
Jake

25
Najważniejszą wzmianką w tym poście jest link do dokumentacji MSDN pokazujący, że TEXTi NTEXT(i IMAGE) są przestarzałe.
Brian

Odpowiedzi:


315

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.


12
Nie wiedziałem, że będzie przechowywany na stronie o wielkości 8k, a poza stroną, jeśli będzie większy. Bardzo fajny.
Brain2000

3
Twoja ostatnia linia jest częściowo błędna. LIKE nie może używać indeksu TYLKO, jeśli symbol wieloznaczny znajduje się na początku przeszukiwanego ciągu.
SouravA,

1
Czy to nie jest problem, aby zmienić pole z tekstu na varchar (max) z istniejącej tabeli z danymi?
user1531040

17

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


16

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%'

11
+1 tylko za wypowiedzenie losowego wyniku głosowania! Doprowadza mnie do szaleństwa, gdy ludzie głosują na mnie bez komentarza, naprawdę potrzebują życia.
Tom Stickel

3
Powodem, dla którego popełnił negatywne zdanie, jest to, że z tego, co pamiętam z rzeczy, które musiałem zrobić, nie jest to uzasadniony argument przy udzielaniu odpowiedzi na pytanie techniczne. Pomyśl o ludziach (takich jak ja w tej chwili) próbujących dowiedzieć się, dlaczego powinniśmy użyć varchar(n)lub text, i przełamać tę odpowiedź. Czy uważasz, że w środowisku zawodowym spieranie się z niejasnymi stwierdzeniami pomoże rozwiązać problem? Cały post na StackOverflow powinien być widziany przez tysiące ludzi, działających w konsekwencji!
Anwar

3
@ Zeratops lol, ta odpowiedź ma 6 lat, byłem raczej zielony, więc kiedy to napisałem. oczyściłem sformułowania, żeby być bardziej rzeczowy.
DForck42,

9
  • 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


5

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.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.