sql-server - length - text sql server

Usar varchar(MAX) vs TEXT en SQL Server

Question

Acabo de leer que el tipo de datos VARCHAR(MAX) (que puede almacenar cerca de 2 GB de datos de char) es el reemplazo recomendado para el tipo de datos TEXT en las versiones SQL Server 2005 y Next SQL SERVER.

Si deseo buscar dentro de una columna cualquier cadena, ¿qué operación es más rápida?

1. ¿Usando una cláusula LIKE contra una VARCHAR(MAX) ?

   WHERE COL1 LIKE ''%search string%''

2. ¿Utiliza la columna TEXT y coloca un Índice / Catálogo de texto completo en esta columna, y luego busca usando la cláusula CONTAINS ?

   WHERE CONTAINS (Col1, ''MyToken'')

Answer 1

El tipo VARCHAR(MAX) es un reemplazo para TEXT . La diferencia básica es que un tipo de TEXT siempre almacenará los datos en un blob mientras que el tipo VARCHAR(MAX) intentará almacenar los datos directamente en la fila a menos que supere la limitación de 8k y en ese punto lo almacena en un blob.

El uso de la declaración LIKE es idéntico entre los dos tipos de datos. La funcionalidad adicional que VARCHAR(MAX) le brinda es que también se puede usar con = y GROUP BY como cualquier otra columna VARCHAR . Sin embargo, si tiene muchos datos, tendrá un gran problema de rendimiento con estos métodos.

Con respecto a si debe usar LIKE para buscar, o si debe usar Indización de texto completo y CONTAINS . Esta pregunta es la misma independientemente de VARCHAR(MAX) o TEXT .

Si está buscando grandes cantidades de texto y el rendimiento es la clave, entonces debe usar un Índice de texto completo .

LIKE es más fácil de implementar y, a menudo, es adecuado para pequeñas cantidades de datos, pero tiene un rendimiento extremadamente bajo con datos de gran tamaño debido a su incapacidad para usar un índice.

Answer 2

No puede buscar un campo de texto sin convertirlo de texto a 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''

Esto da un error:

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

Cuando esto no:

declare @table table (a varchar(max))

Curiosamente, LIKE todavía funciona, es decir

where a like ''%a%''

Answer 3

Para texto grande, el índice de texto completo es mucho más rápido. Pero también puedes indexar varchar(max) texto completo .

Answer 4

Si utiliza MS Access (especialmente versiones anteriores como 2003), se ve obligado a utilizar el tipo de datos TEXT en SQL Server, ya que MS Access no reconoce nvarchar(MAX) como un campo Memo en Access, mientras que TEXT se reconoce como un campo Memo.

Answer 5

• Definición básica

TEXT y VarChar(MAX) son tipos de datos de caracteres de longitud variable no Unicode grandes, que pueden almacenar un máximo de 2147483647 caracteres no Unicode (es decir, la capacidad máxima de almacenamiento es: 2 GB).

• ¿Cuál usar?

Según el enlace MSDN, Microfost sugiere evitar el uso del tipo de datos Text y se eliminará en versiones futuras de Sql Server. Varchar (Max) es el tipo de datos sugerido para almacenar los valores de cadena grandes en lugar del tipo de datos de texto.

• Almacenamiento dentro de la fila o fuera de la fila

Los datos de una columna de tipo Text se almacenan fuera de la fila en páginas de datos LOB separadas. La fila en la página de datos de la tabla solo tendrá un puntero de 16 bytes en la página de datos LOB donde están presentes los datos reales. Mientras que los Datos de una columna de tipo Varchar(max) se almacenan en filas si es menor o igual a 8000 bytes. Si el valor de la columna Varchar (max) está cruzando los 8000 bytes, el valor de la columna Varchar (max) se almacena en páginas de datos LOB separadas y la fila solo tendrá un puntero de 16 bytes en la página de datos LOB donde están presentes los datos reales. Así que In-Row Varchar (Max) es bueno para búsquedas y recuperación.

• Funcionalidades admitidas / no admitidas

Algunas de las funciones de cadena, los operadores o las construcciones que no funcionan en la columna Tipo de texto, pero sí funcionan en la columna de tipo VarChar (Máx).

1. = Igual al operador en la columna de tipo VarChar (Max)

2. Cláusula Group by en la columna de tipo VarChar (Max)

   • Consideraciones de IO del sistema

Como sabemos que los valores de columna tipo VarChar (Max) se almacenan fuera de la fila solo si la longitud del valor que se almacenará en ella es mayor que 8000 bytes o no hay suficiente espacio en la fila, de lo contrario se almacenará En la fila. Entonces, si la mayoría de los valores almacenados en la columna VarChar (Max) son grandes y están almacenados fuera de la fila, el comportamiento de recuperación de datos será casi similar al de la columna Tipo de texto.

Pero si la mayoría de los valores almacenados en las columnas de tipo VarChar (Max) son lo suficientemente pequeños para almacenarlos en filas. Luego, la recuperación de los datos donde las columnas LOB no están incluidas requiere más cantidad de páginas de datos para leer, ya que el valor de la columna LOB se almacena en filas en la misma página de datos donde se almacenan los valores de columnas que no son LOB. Pero si la consulta de selección incluye una columna LOB, entonces requiere menos cantidad de páginas para leer para la recuperación de datos en comparación con las columnas de tipo Texto.

Conclusión

Utilice el tipo de datos VarChar(MAX) lugar de TEXT para un buen rendimiento.

Source