Using varchar (MAX) vs TEXT on SQL Server
Właśnie przeczytałem, że typ danych VARCHAR(MAX)
(który może przechowywać blisko 2GB danych char) jest zalecanym zamiennikiem dla typu danych TEXT
w SQL Server 2005 i następnych wersjach SQL SERVER.
Jeśli chcę wyszukać wewnątrz kolumny dowolny ciąg znaków, która operacja jest szybsza?
-
Używając klauzuli
LIKE
przeciwko kolumnieVARCHAR(MAX)
?WHERE COL1 LIKE '%search string%'
-
Używając Kolumny
TEXT
i umieść indeks pełnego tekstu/Katalog w tej kolumnie, a potem szukać używając klauzuliCONTAINS
?WHERE CONTAINS (Col1, 'MyToken')
5 answers
Typ VARCHAR(MAX)
jest zamiennikiem TEXT
. Podstawowa różnica polega na tym, że typ TEXT
będzie zawsze przechowywał dane w obiekcie blob, podczas gdy typ VARCHAR(MAX)
będzie próbował przechowywać dane bezpośrednio w wierszu, chyba że przekroczy ograniczenie 8k i w tym momencie zapisze je w obiekcie blob.
Użycie instrukcji LIKE jest identyczne pomiędzy dwoma typami danych. Dodatkową funkcjonalnością VARCHAR(MAX)
jest to, że może być również używany z =
i GROUP BY
, Jak każda inna kolumna VARCHAR
. Jednak jeśli masz dużo danych, będziesz miał ogromny problem z wydajnością przy użyciu tych metod.
W odniesieniu do Jeśli należy użyć LIKE
do wyszukiwania, lub jeśli należy użyć indeksowania pełnego tekstu 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ć indeks pełnego tekstu.
LIKE
jest prostszy do wdrożenia i często nadaje się do niewielkich ilości danych, ale ma bardzo słabą wydajność przy dużych danych ze względu na niemożność użycia indeksu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-05 22:36:51
Dla dużego tekstu, indeks pełnego tekstu jest znacznie szybszy. Ale możesz indeks pełnotekstowy varchar(max)
też.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-05 22:37:20
Nie można przeszukiwać pola tekstowego bez konwersji 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'
To daje błąd:
The data types text and varchar are incompatible in the equal to operator.
Gdzie Nie:
declare @table table (a varchar(max))
Co ciekawe, LIKE
nadal działa, czyli
where a like '%a%'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-09 12:46:45
- Definicja Podstawowa
TEXT
i VarChar(MAX)
są nie-Unicode dużym typem danych o zmiennej długości, który może przechowywać maksymalnie 2147483647 znaków nie-Unicode (tj. maksymalna pojemność pamięci wynosi: 2 GB).
- którego użyć?
Zgodnie z MSDN link Microsoft sugeruje, aby unikać korzystania z danych tekstowych i zostanie on usunięty w przyszłych wersjach Sql Server. VARCHAR (Max) jest sugerowanym typem danych dla przechowywanie dużych wartości łańcuchowych zamiast tekstowego typu danych.
- Przechowywanie w wierszu lub poza wierszem
Dane z kolumny typu Text
są przechowywane poza wierszem w osobnych stronach danych LOB. Wiersz w zakładce Dane tabeli będzie miał tylko 16-bajtowy wskaźnik do zakładki dane LOB, w której znajdują się rzeczywiste dane. Podczas gdy dane z kolumny typu Varchar(max)
są przechowywane w wierszu, jeśli są mniejsze lub równe 8000 bajtów. Jeśli wartość kolumny VARCHAR (max) przekracza 8000 bajtów, to Wartość kolumny VARCHAR (max) jest przechowywana w osobnych zakładkach danych LOB, a wiersz będzie miał tylko 16-bajtowy wskaźnik do zakładki danych LOB, w której znajdują się rzeczywiste dane. Więc In-Row
Varchar(Max) jest dobry do wyszukiwania i wyszukiwania.
- Obsługiwane / Nieobsługiwane Funkcje
Niektóre funkcje łańcuchowe, operatory lub konstrukcje, które nie działają na kolumnie typu tekstowego, ale działają na kolumnie typu VarChar (Max).
-
=
równa Operator na kolumnie typu VarChar (Max) -
Group by klauzula na kolumnie typu VarChar (Max)
- rozważania o systemie IO
Jak wiemy, wartości kolumn typu VarChar (Max) są zapisywane poza wierszem tylko wtedy, gdy długość wartości do zapisania w wierszu jest większa niż 8000 bajtów lub nie ma wystarczającej ilości miejsca w wierszu, w przeciwnym razie będzie ona zapisywana w wierszu. Więc jeśli większość wartości przechowywanych w kolumnie VARCHAR (Max) jest duża i przechowywana poza wierszem zachowanie pobierania danych będzie prawie podobne do tego w kolumnie typu tekstowego.
Ale jeśli większość wartości przechowywanych w kolumnach typu VARCHAR(Max) jest wystarczająco mała, aby przechowywać je w wierszu. Wtedy odzyskanie danych, do których nie są dołączone kolumny LOB wymaga większej ilości stron danych do odczytu, ponieważ wartość kolumny LOB jest zapisana w wierszu w tej samej zakładce Dane, w której są zapisywane wartości kolumn inne niż LOB. Ale jeśli zapytanie select zawiera kolumnę LOB to wymaga mniejszej liczby stron do odczytu w celu pobrania danych w porównaniu z kolumnami typu tekstowego.
Wniosek
Użyj typu danych VarChar(MAX)
zamiast TEXT
dla dobrej wydajności.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-20 18:58:03
Jeśli używasz MS Access (szczególnie starszych wersji, takich jak 2003), musisz użyć TEXT
datatype na serwerze SQL Server, ponieważ MS Access nie rozpoznaje nvarchar(MAX)
jako pola Memo w Access, podczas gdy TEXT
jest rozpoznawane jako pole Memo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-05 22:37:56