type - text length sql

¿Hay alguna desventaja de usar siempre nvarchar(MAX)?

Question

En SQL Server 2005, ¿existen desventajas para hacer que todos los campos de caracteres sean nvarchar (MAX) en lugar de especificar una longitud explícitamente, por ejemplo, nvarchar (255)? (Aparte de lo obvio, no puede limitar la longitud del campo a nivel de la base de datos)

Answer 1

1) El servidor SQL tendrá que utilizar más recursos (memoria asignada y tiempo de CPU) cuando se trate de nvarchar (max) frente a nvarchar (n) donde n es un número específico del campo.

2) ¿Qué significa esto con respecto al rendimiento?

En SQL Server 2005, consulté 13,000 filas de datos de una tabla con 15 columnas nvarchar (max). Programé las consultas repetidamente y luego cambié las columnas a nvarchar (255) o menos.

Las consultas previas a la optimización promediaron 2.0858 segundos. Las consultas después del cambio regresaron en un promedio de 1.90 segundos. Eso fue alrededor de 184 milisegundos de mejora a la consulta de selección * básica. Eso es una mejora del 8,8%.

3) Mis resultados coinciden con algunos otros artículos que indicaron que hubo una diferencia de rendimiento. Dependiendo de su base de datos y la consulta, el porcentaje de mejora puede variar. Si no tiene muchos usuarios concurrentes o muchos registros, entonces la diferencia de rendimiento no será un problema para usted. Sin embargo, la diferencia de rendimiento aumentará a medida que aumenten los registros y los usuarios simultáneos.

Answer 2

A veces desea que el tipo de datos imponga algún sentido en los datos que contiene.

Digamos, por ejemplo, que tiene una columna que realmente no debería tener más de, digamos, 20 caracteres. Si define esa columna como VARCHAR (MAX), alguna aplicación no autorizada podría insertar una cadena larga en ella y nunca lo sabría, o tendría alguna forma de prevenirla.

La próxima vez que su aplicación use esa cadena, suponiendo que la longitud de la cadena sea modesta y razonable para el dominio que representa, experimentará un resultado impredecible y confuso.

Answer 3

Como se señaló anteriormente, se trata principalmente de un compromiso entre el almacenamiento y el rendimiento. Al menos en la mayoría de los casos.

Sin embargo, hay al menos otro factor que debe considerarse al elegir n / varchar (Max) sobre n / varchar (n). ¿Se indexarán los datos (como, por ejemplo, un apellido)? Dado que la definición de MAX se considera un LOB, entonces cualquier cosa definida como MAX no está disponible para la indexación. y sin un índice, cualquier búsqueda que incluya los datos como predicado en una cláusula WHERE se forzará en un análisis de tabla completa, que es el peor rendimiento que puede obtener para las búsquedas de datos.

Answer 4

El único problema que encontré fue que desarrollamos nuestras aplicaciones en SQL Server 2005, y en una instancia, tenemos que admitir SQL Server 2000. Acabo de enterarme, de la manera difícil que a SQL Server 2000 no le gusta la opción MAX para varchar o nvarchar.

Answer 5

El trabajo de la base de datos es almacenar datos para que puedan ser utilizados por la empresa. Parte de hacer que esos datos sean útiles es asegurarse de que sean significativos. Permitir que alguien ingrese un número ilimitado de caracteres para su nombre no garantiza datos significativos.

Construir estas restricciones en la capa empresarial es una buena idea, pero eso no garantiza que la base de datos permanezca intacta. La única forma de garantizar que las reglas de datos no se violen es imponerlas en el nivel más bajo posible en la base de datos.

Answer 6

Enlace interesante: ¿Por qué usar un VARCHAR cuando puede usar TEXTO?

Se trata de PostgreSQL y MySQL, por lo que el análisis de rendimiento es diferente, pero la lógica de "explicación explícita" sigue siendo válida: ¿por qué obligarse a preocuparse siempre por algo que es relevante un pequeño porcentaje del tiempo? Si guardó una dirección de correo electrónico en una variable, usaría una ''cadena'' y no una ''cadena limitada a 80 caracteres''.

Answer 7

Es una pregunta justa y él dijo aparte de lo obvio ...

Las desventajas podrían incluir:

Implicaciones de rendimiento El optimizador de consultas utiliza el tamaño del campo para determinar el plan de ejecución más eficiente

"1. La asignación de espacio en se extiende y las páginas de la base de datos son flexibles. Por lo tanto, al agregar información al campo usando la actualización, su base de datos tendría que crear un puntero si los nuevos datos son más largos que los que se insertaron anteriormente. fragmentarse = menor rendimiento en casi todo, desde indexar hasta eliminar, actualizar e insertar. " http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

Implicaciones de la integración: es difícil para otros sistemas saber cómo integrarse con su base de datos Crecimiento imprevisible de datos Posibles problemas de seguridad, por ejemplo, podría bloquear un sistema al ocupar todo el espacio en disco

Aquí hay un buen artículo: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Answer 8

Esto causará un problema de rendimiento, aunque puede que nunca cause problemas reales si su base de datos es pequeña. Cada registro ocupará más espacio en el disco duro y la base de datos necesitará leer más sectores del disco si está buscando en muchos registros a la vez. Por ejemplo, un registro pequeño podría ajustarse a 50 en un sector y un registro grande podría ajustarse a 5. Necesitaría leer 10 veces más datos del disco utilizando el registro grande.

Answer 9

Hará que el diseño de la pantalla sea más difícil, ya que no podrás predecir qué tan amplios deberían ser tus controles.

Answer 10

La misma pregunta se hizo en los foros de MSDN:

• Varchar (max) vs Varchar (255)

De la publicación original (mucha más información allí):

Cuando almacena datos en una columna VARCHAR (N), los valores se almacenan físicamente de la misma manera. Pero cuando lo almacena en una columna VARCHAR (MAX), detrás de la pantalla, los datos se manejan como un valor de TEXTO. Por lo tanto, se necesita un procesamiento adicional cuando se trata de un valor VARCHAR (MAX). (solo si el tamaño supera los 8000)

VARCHAR (MAX) o NVARCHAR (MAX) se considera como un ''tipo de valor grande''. Los tipos de valores grandes generalmente se almacenan ''fuera de fila''. Esto significa que la fila de datos tendrá un puntero a otra ubicación donde se almacena el "valor grande" ...

Answer 11

La principal desventaja que puedo ver es que digamos que tienes esto:

¿Cuál le proporciona la mayor información sobre los datos necesarios para la interfaz de usuario?

Esta

            CREATE TABLE [dbo].[BusData](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [RecordId] [nvarchar](MAX) NULL,
                [CompanyName] [nvarchar](MAX) NOT NULL,
                [FirstName] [nvarchar](MAX) NOT NULL,
                [LastName] [nvarchar](MAX) NOT NULL,
                [ADDRESS] [nvarchar](MAX) NOT NULL,
                [CITY] [nvarchar](MAX) NOT NULL,
                [County] [nvarchar](MAX) NOT NULL,
                [STATE] [nvarchar](MAX) NOT NULL,
                [ZIP] [nvarchar](MAX) NOT NULL,
                [PHONE] [nvarchar](MAX) NOT NULL,
                [COUNTRY] [nvarchar](MAX) NOT NULL,
                [NPA] [nvarchar](MAX) NULL,
                [NXX] [nvarchar](MAX) NULL,
                [XXXX] [nvarchar](MAX) NULL,
                [CurrentRecord] [nvarchar](MAX) NULL,
                [TotalCount] [nvarchar](MAX) NULL,
                [Status] [int] NOT NULL,
                [ChangeDate] [datetime] NOT NULL
            ) ON [PRIMARY]

¿O esto?

            CREATE TABLE [dbo].[BusData](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [RecordId] [nvarchar](50) NULL,
                [CompanyName] [nvarchar](50) NOT NULL,
                [FirstName] [nvarchar](50) NOT NULL,
                [LastName] [nvarchar](50) NOT NULL,
                [ADDRESS] [nvarchar](50) NOT NULL,
                [CITY] [nvarchar](50) NOT NULL,
                [County] [nvarchar](50) NOT NULL,
                [STATE] [nvarchar](2) NOT NULL,
                [ZIP] [nvarchar](16) NOT NULL,
                [PHONE] [nvarchar](18) NOT NULL,
                [COUNTRY] [nvarchar](50) NOT NULL,
                [NPA] [nvarchar](3) NULL,
                [NXX] [nvarchar](3) NULL,
                [XXXX] [nvarchar](4) NULL,
                [CurrentRecord] [nvarchar](50) NULL,
                [TotalCount] [nvarchar](50) NULL,
                [Status] [int] NOT NULL,
                [ChangeDate] [datetime] NOT NULL
            ) ON [PRIMARY]

Answer 12

Mala idea cuando sabes que el campo estará en un rango establecido, por ejemplo, de 5 a 10 caracteres. Creo que solo usaría max si no estuviera seguro de cuál sería la longitud. Por ejemplo, un número de teléfono nunca sería más que un cierto número de caracteres.

¿Puede decir honestamente que no está seguro de los requisitos de longitud aproximada para cada campo en su tabla?

Sin embargo, sí entiendo su punto: hay algunos campos que sin duda consideraría usar varchar (max).

Curiosamente, los documentos de MSDN lo resumen bastante bien:

Utilice varchar cuando los tamaños de las entradas de datos de columna varían considerablemente. Use varchar (max) cuando los tamaños de las entradas de datos de la columna varían considerablemente, y el tamaño puede exceder los 8,000 bytes.

Hay una discusión interesante sobre el tema aquí .

Answer 13

Mis pruebas han demostrado que hay diferencias al seleccionar.

CREATE TABLE t4000 (a NVARCHAR(4000) NULL);

CREATE TABLE tmax (a NVARCHAR(MAX) NULL);

DECLARE @abc4 NVARCHAR(4000) = N''ABC'';

INSERT INTO t4000
SELECT TOP 1000000 @abc4
    FROM
    master.sys.all_columns ac1,
    master.sys.all_columns ac2;

DECLARE @abc NVARCHAR(MAX) = N''ABC'';

INSERT INTO tmax
SELECT TOP 1000000 @abc
    FROM
    master.sys.all_columns ac1,
    master.sys.all_columns ac2;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT * FROM dbo.t4000;
SELECT * FROM dbo.tmax;

Answer 14

Piense en ello como otro nivel de seguridad. Puede diseñar su tabla sin relaciones de clave externa, perfectamente válidas, y garantizar la existencia de entidades asociadas completamente en la capa empresarial. Sin embargo, las claves externas se consideran buenas prácticas de diseño porque agregan otro nivel de restricción en caso de que algo se complique en la capa empresarial. Lo mismo ocurre con la limitación de tamaño de campo y no usar varchar MAX.

Answer 15

Revisé algunos artículos y encontré un guión de prueba útil en esto: http://www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspx Luego lo cambié para comparar entre NVARCHAR (10) vs NVARCHAR (4000) vs NVARCHAR (MAX ) y no encuentro la diferencia de velocidad cuando uso números específicos, pero cuando uso MAX. Puedes probar por ti mismo. Espero que esto ayude.

SET NOCOUNT ON;

--===== Test Variable Assignment 1,000,000 times using NVARCHAR(10)
DECLARE @SomeString NVARCHAR(10),
        @StartTime DATETIME;
--=====         
 SELECT @startTime = GETDATE();
 SELECT TOP 1000000
        @SomeString = ''ABC''
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2;
 SELECT testTime=''10'', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO
--===== Test Variable Assignment 1,000,000 times using NVARCHAR(4000)
DECLARE @SomeString NVARCHAR(4000),
        @StartTime DATETIME;
 SELECT @startTime = GETDATE();
 SELECT TOP 1000000
        @SomeString = ''ABC''
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2;
 SELECT testTime=''4000'', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO
--===== Test Variable Assignment 1,000,000 times using NVARCHAR(MAX)
DECLARE @SomeString NVARCHAR(MAX),
        @StartTime DATETIME;
 SELECT @startTime = GETDATE();
 SELECT TOP 1000000
        @SomeString = ''ABC''
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2;
 SELECT testTime=''MAX'', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO

Answer 16

Según el enlace proporcionado en la respuesta aceptada, parece que:

1. Los 100 caracteres almacenados en un campo nvarchar(MAX) no se almacenarán de manera diferente a 100 caracteres en un campo nvarchar(100) : los datos se almacenarán en línea y no tendrá la sobrecarga de leer y escribir datos ''fuera de fila''. Así que no te preocupes allí.

2. Si el tamaño es superior a 4000, los datos se almacenarán automáticamente "fuera de fila", que es lo que desearía. Así que no te preocupes allí tampoco.

Sin embargo...

3. No puede crear un índice en una columna nvarchar(MAX) . Puede usar la indexación de texto completo, pero no puede crear un índice en la columna para mejorar el rendimiento de la consulta. Para mí, esto cierra el trato ... es una desventaja definitiva usar siempre nvarchar (MAX).

Conclusión:

Si desea una especie de "longitud de cadena universal" en toda su base de datos, que se pueda indexar y que no desperdicie espacio y tiempo de acceso, entonces podría usar nvarchar(4000) .

Answer 17

Si todos los datos en una fila (para todas las columnas) nunca tomarían razonablemente 8000 caracteres o menos, entonces el diseño en la capa de datos debería imponer esto.

El motor de base de datos es mucho más eficiente y mantiene todo fuera del almacenamiento de blobs. Cuanto más pequeño puedas restringir una fila, mejor. Cuantas más filas pueda meter en una página, mejor. La base de datos solo funciona mejor cuando tiene que acceder a menos páginas.

Answer 18

Soporte del sistema heredado. Si tiene un sistema que utiliza los datos y se espera que tenga una cierta longitud, entonces la base de datos es un buen lugar para imponer la longitud. Esto no es ideal, pero los sistemas heredados a veces no son ideales. = P

Answer 19

Tuve un udf que rellenó las cadenas y puse la salida a varchar (max). Si se usó directamente en lugar de volver al tamaño apropiado para la columna que se está ajustando, el rendimiento fue muy pobre. Terminé poniendo el udf a una longitud arbitraria con una gran nota en lugar de confiar en todos los que llamaban al udf para volver a emitir la cadena a un tamaño más pequeño.

Answer 20

Un problema es que si tiene que trabajar con varias versiones de SQL Server, el MAX no siempre funcionará. Por lo tanto, si está trabajando con bases de datos heredadas o con cualquier otra situación que involucre varias versiones, será mejor que tenga mucho cuidado.

Answer 21

Una desventaja es que estará diseñando alrededor de una variable impredecible, y probablemente ignorará en lugar de aprovechar la estructura de datos interna de SQL Server, compuesta progresivamente por Fila (s), Página (s) y Extensión (s).

Lo que me hace pensar acerca de la alineación de la estructura de datos en C, y el hecho de que sea consciente de la alineación se considera generalmente como Good Thing (TM). Idea similar, contexto diferente.

Página de MSDN para Páginas y Extensiones

Página de MSDN para datos de desbordamiento de fila

Answer 22

Una razón para NO utilizar los campos de texto o máx. Es que no puede realizar reconstrucciones de índices en línea, es decir, REBUILD WITH ONLINE = ON incluso con SQL Server Enterprise Edition.