Usando varchar (MAX) vs TEXT no SQL Server

Acabei de ler que o tipo de dados VARCHAR(MAX) (que pode armazenar perto de 2 GB de dados char) é a substituição recomendada para o tipo de dados TEXT nas versões SQL Server 2005 e Next SQL SERVER.

Se eu quiser procurar dentro de uma coluna por qualquer string, qual operação é mais rápida?

  1. Usando uma cláusula LIKE em uma coluna VARCHAR(MAX) ?

    WHERE COL1 LIKE '%search string%'

  2. Usando a coluna TEXT e colocar um índice de texto completo / catálogo nesta coluna e, em seguida, pesquisar usando a cláusula CONTAINS ?

    WHERE CONTAINS (Col1, 'MyToken')

O tipo VARCHAR(MAX) é um substituto para o TEXT . A diferença básica é que um tipo TEXT sempre armazenará os dados em um blob enquanto o tipo VARCHAR(MAX) tentará armazenar os dados diretamente na linha, a menos que exceda a limitação de 8k e, nesse ponto, armazena-os em um blob.

Usando a instrução LIKE é idêntico entre os dois tipos de dados. A funcionalidade adicional VARCHAR(MAX) é que ela também pode ser usada com = e GROUP BY como qualquer outra coluna VARCHAR pode ser. No entanto, se você tiver muitos dados, você terá um grande problema de desempenho usando esses methods.

Em relação a se você deve usar o LIKE para procurar, ou se você deve usar o Full Text Indexing e CONTAINS . Esta questão é a mesma, independentemente de VARCHAR(MAX) ou TEXT .

Se você está pesquisando grandes quantidades de texto e desempenho é fundamental, então você deve usar um índice de texto completo .

LIKE é mais simples de implementar e geralmente é adequado para pequenas quantidades de dados, mas tem um desempenho extremamente ruim com dados grandes devido à sua incapacidade de usar um índice.

Para texto grande, o índice de texto completo é muito mais rápido. Mas você também pode indexar o índice de texto completo varchar(max) .

Você não pode pesquisar um campo de texto sem convertê-lo de texto para 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' 

Isso dá um erro:

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

Onde isso não acontece:

 declare @table table (a varchar(max)) 

Curiosamente, o LIKE ainda funciona, ou seja

 where a like '%a%' 
  • Definição Básica

TEXT e VarChar(MAX) são tipos de dados de caracteres de tamanho variável não-Unicode, que podem armazenar no máximo 2147483647 caracteres não-Unicode (ou seja, a capacidade máxima de armazenamento é: 2 GB).

  • Qual deles usar?

De acordo com o link do MSDN, a Microsoft está sugerindo evitar o uso do tipo de dados Texto e ele será removido em versões futuras do Sql Server. Varchar (Max) é o tipo de dados sugerido para armazenar os valores de string grandes em vez do tipo de dados Text.

  • Armazenamento In-Row ou Out-of-Row

Os dados de uma coluna do tipo Text são armazenados fora da linha em páginas de dados LOB separadas. A linha na página de dados da tabela terá apenas um ponteiro de 16 bytes para a página de dados do LOB, na qual os dados reais estão presentes. Enquanto Data de uma coluna do tipo Varchar(max) é armazenada em linha se for menor ou igual a 8000 bytes. Se o valor da coluna Varchar (max) estiver cruzando os 8000 bytes, o valor da coluna Varchar (max) será armazenado em páginas de dados LOB separadas e a linha terá apenas um ponteiro de 16 bytes na página de dados LOB onde os dados reais estão presentes. Então Varchar In-Row (Max) é bom para buscas e recuperação.

  • Funcionalidades Suportadas / Não Suportadas

Algumas das funções de cadeia de caracteres, operadores ou as construções que não funcionam na coluna Tipo de texto, mas funcionam na coluna do tipo VarChar (Max).

  1. = Igual ao operador na coluna do tipo VarChar (Max)
  2. Agrupar por cláusula na coluna do tipo VarChar (Max)

    • Considerações sobre IO do sistema

Como sabemos que os valores da coluna tipo VarChar (Max) são armazenados fora de linha apenas se o comprimento do valor a ser armazenado for maior que 8000 bytes ou se não houver espaço suficiente na linha, caso contrário ele será armazenado em linha. Portanto, se a maioria dos valores armazenados na coluna VarChar (Max) forem grandes e armazenados fora de linha, o comportamento de recuperação de dados será quase semelhante ao da coluna Tipo de texto.

Mas se a maioria dos valores armazenados em colunas do tipo VarChar (Max) forem pequenos o suficiente para serem armazenados em linha. Em seguida, a recuperação dos dados em que as colunas LOB não estão incluídas requer o maior número de páginas de dados a serem lidas, pois o valor da coluna LOB é armazenado em linha na mesma página de dados em que os valores da coluna não-LOB são armazenados. Mas, se a consulta de seleção include a coluna LOB, ela precisará de um número menor de páginas para leitura para a recuperação de dados, em comparação com as colunas Tipo de texto.

Conclusão

Use o tipo de dados VarChar(MAX) vez de TEXT para um bom desempenho.

Fonte

Se estiver usando o MS Access (especialmente versões mais antigas como 2003), você será forçado a usar o tipo de dados TEXT no SQL Server, pois o MS Access não reconhece nvarchar(MAX) como um campo Memo no Access, enquanto TEXT é reconhecido como um campo Memo.