Mejores prácticas para SQL varchar longitud de columna
Cada vez que se configura una nueva tabla SQL o se agrega una nueva columna varchar a una tabla existente, me pregunto una cosa: cuál es el mejor valor para length.
Así que, digamos, tienes una columna llamada name de tipo varchar. Por lo tanto, usted tiene que elegir la longitud. No puedo pensar en un nombre > 20 caracteres, pero nunca lo sabrás. Pero en lugar de usar 20, siempre redondeo hasta el siguiente número 2^n. En este caso, elegiría 32 como la longitud. Hago eso, porque desde un punto de vista científico de la computación de vista, un número 2^n se ve más even para mí que otros números y estoy asumiendo que la arquitectura debajo puede manejar esos números ligeramente mejor que otros.
Por otro lado, MSSQL server, por ejemplo, establece el valor de longitud predeterminado en 50, cuando elige crear una columna varchar. Eso me hace pensar en ello. ¿Por qué 50? ¿es solo un número aleatorio, o basado en la longitud media de la columna, o qué?
También podría ser - o probablemente es-que diferentes servidores SQL implementaciones (como MySQL, MSSQL, Postgres,...) tienen diferentes mejores valores de longitud de columna.
7 answers
Ningún DBMS que yo sepa tiene alguna "optimización" que haga que un VARCHAR con una longitud 2^n funcione mejor que uno con una longitud max que no sea una potencia de 2.
Creo que las primeras versiones de SQL Server en realidad trataban un VARCHAR con longitud 255 de manera diferente a uno con una longitud máxima más alta. No se si este sigue siendo el caso.
Para casi todos los DBMS, el almacenamiento real que se requiere solo está determinado por el número de caracteres que pone en él, no por la longitud max tú defines. Así que desde un punto de vista de almacenamiento (y muy probablemente también de rendimiento), no hace ninguna diferencia si declara una columna como VARCHAR(100) o VARCHAR(500).
Debería ver la longitud max proporcionada para una columna VARCHAR como una especie de restricción (o regla de negocio) en lugar de una cosa técnica/física.
Para PostgreSQL la mejor configuración es usar text sin una restricción de longitud y un CHECK CONSTRAINT que limite el número de caracteres a cualquiera que sea su negocio requerir.
Si ese requisito cambia, alterar la restricción check es mucho más rápido que alterar la tabla (porque la tabla no necesita ser reescrita)
Lo mismo se puede aplicar para Oracle y otros - en Oracle sería VARCHAR(4000) en lugar de text aunque.
No se si hay una diferencia de almacenamiento físico entre VARCHAR(max) y, por ejemplo, VARCHAR(500) en SQL Server. Pero aparentemente hay un impacto en el rendimiento cuando se usa varchar(max) en comparación con varchar(8000).
Ver este enlace (publicado por Erwin Brandstetter como un comentario)
Editar 2013-09-22
Respecto al comentario de bigown:
En las versiones de Postgres anteriores a la 9.2 (que no estaba disponible cuando escribí la respuesta inicial) un cambio en la definición de columna hizo reescribir toda la tabla, ver por ejemplo aquí. Desde 9.2 este ya no es el caso y una prueba rápida confirmó que el aumento del tamaño de la columna para una tabla con 1.2 millones de hecho, las filas solo tardaron 0,5 segundos.
Para Oracle esto parece ser cierto también, a juzgar por el tiempo que tarda en alterar la columna varchar de una tabla grande. Pero no pude encontrar ninguna referencia para eso.
Para MySQL el manual dice "En la mayoría de los casos, ALTER TABLE hace una copia temporal de la tabla original". Y mis propias pruebas confirman que: ejecutar un ALTER TABLE en una tabla con 1.2 millones de filas (lo mismo que en mi prueba con Postgres) para aumentar el tamaño de una columna tomó 1.5 minuto. Sin embargo, en MySQL puede no usar la "solución" para usar una restricción de verificación para limitar el número de caracteres en una columna.
Para SQL Server no pude encontrar una declaración clara sobre esto, pero el tiempo de ejecución para aumentar el tamaño de una columna varchar (de nuevo la tabla de 1,2 millones de filas de arriba) indica que no se realiza una reescritura.
Editar 2017-01-24
Parece que estaba (al menos parcialmente) equivocado sobre SQL Server. Ver esta respuesta de Aaron Bertrand que muestra que la longitud declarada de una nvarchar o varchar columnas hace una gran diferencia para el rendimiento.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-11 15:43:01
VARCHAR(255) y VARCHAR(2) tomar exactamente la misma cantidad de espacio en el disco! Así que la única razón para limitarlo es si tienes una necesidad específica de que sea más pequeño. De lo contrario, haz que todos sean 255.
Específicamente, al ordenar, las columnas más grandes ocupan más espacio, por lo que si eso perjudica el rendimiento, debe preocuparse por ello y hacerlas más pequeñas. Pero si solo seleccionas 1 fila de esa tabla, entonces puedes hacer que todas sean 255 y no importará.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-04-26 03:49:25
El mejor valor es el correcto para los datos tal como se definen en el dominio subyacente.
Para algunos dominios, VARCHAR(10) es correcto para el atributo Name, para otros dominios VARCHAR(255) podría ser la mejor opción.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-28 11:28:45
Cada vez que configuro una nueva tabla SQL siento lo mismo acerca de que 2^n sea más "par"... pero para resumir las respuestas aquí, no hay un impacto significativo en el espacio de almacenamiento simplemente definiendo varchar (2^n) o incluso varchar(MAX).
Dicho esto, aún debe anticipar las implicaciones potenciales en el almacenamiento y el rendimiento al establecer un límite alto de varchar (). Por ejemplo, supongamos que crea una columna varchar (MAX) para contener descripciones de productos con indexación de texto completo. Si el 99% de las descripciones tienen solo 500 caracteres de largo, y luego de repente tienes a alguien que reemplaza dichas descripciones con artículos de Wikipedia, es posible que notes un almacenamiento significativo e imprevisto y éxitos de rendimiento.
Otra cosa a considerar de Bill Karwin:
Hay un posible impacto en el rendimiento: en MySQL, las tablas temporales y las tablas de MEMORIA almacenan una columna VARCHAR como una columna de longitud fija, acolchado a su longitud máxima. Si diseñas columnas VARCHAR mucho más grande que el tamaño más grande que usted necesita, usted consumirá más memoria de lo que tienes que hacer. Esto afecta la eficiencia de la caché, la velocidad de clasificación, etc.
Básicamente, acaba de llegar con restricciones comerciales razonables y error en un tamaño ligeramente mayor. Como señaló @onedaywhen, los apellidos en el Reino Unido suelen tener entre 1 y 35 caracteres. Si decides convertirlo en varchar(64), realmente no vas a lastimar nada... a menos que estés almacenando el apellido de este tipo se dice que está arriba a 666 caracteres de largo. En ese caso, tal vez varchar(1028) tenga más sentido.
Y en caso de que sea útil, esto es lo que varchar 2^5 a 2^10 podría parecer si se llena:
varchar(32) Lorem ipsum dolor sit amet amet.
varchar(64) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
varchar(128) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
varchar(256) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
varchar(512) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
varchar(1024) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
dapibus leo lobortis eleifend. Vivamus vitae diam turpis. Vivamu
nec tristique magna, vel tincidunt diam. Maecenas elementum semi
quam. In ut est porttitor, sagittis nulla id, fermentum turpist.
Curabitur pretium nibh a imperdiet cursus. Sed at vulputate este
proin fermentum pretium justo, ac malesuada eros et Pellentesque
vulputate hendrerit molestie. Aenean imperdiet a enim at finibus
fusce ut ullamcorper risus, a cursus massa. Nunc non dapibus vel
Lorem ipsum dolor sit amet, consectetur Praesent ut ultrices sit
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-24 20:58:12
Agregando a la respuesta de a_horse_with_no_name puede encontrar lo siguiente de interés...
No hace ninguna diferencia si declara una columna como VARCHAR(100) o VACHAR(500).
-- try to create a table with max varchar length
drop table if exists foo;
create table foo(name varchar(65535) not null)engine=innodb;
MySQL Database Error: Row size too large.
-- try to create a table with max varchar length - 2 bytes for the length
drop table if exists foo;
create table foo(name varchar(65533) not null)engine=innodb;
Executed Successfully
-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65533))engine=innodb;
MySQL Database Error: Row size too large.
-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65532))engine=innodb;
Executed Successfully
No olvide el byte de longitud(s) y el byte anulable so:
name varchar(100) not null será de 1 byte (longitud) + hasta 100 caracteres (latin1)
name varchar(500) not null será de 2 bytes (longitud) + hasta 500 caracteres (latin1)
name varchar(65533) not null será de 2 bytes (longitud) + hasta 65533 caracteres (latin1)
name varchar(65532) será de 2 bytes (longitud) + hasta 65532 caracteres (latin1) + 1 byte nulo
Espero que esto ayude:)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-28 14:26:33
Siempre consulte con su experto en dominios de negocios. Si eres tú, busca un estándar de la industria. Si, por ejemplo, el dominio en cuestión es el apellido (apellido) de una persona física, entonces para una empresa del Reino Unido, iría al catálogo de estándares de datos de UK Govtalk para información de la persona y descubriría que un apellido tendrá entre 1 y 35 caracteres.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-28 12:00:26
No he comprobado esto últimamente, pero sé en el pasado con Oracle que el controlador JDBC reservaría un trozo de memoria durante la ejecución de la consulta para mantener el conjunto de resultados que regresa. El tamaño del fragmento de memoria depende de las definiciones de columna y del tamaño de búsqueda. Así que la longitud de las columnas varchar2 afecta la cantidad de memoria reservada. Esto causó graves problemas de rendimiento para mí hace años, ya que siempre usamos varchar2 (4000) (el máximo en ese momento) y la recolección de basura era mucho menor eficiente de lo que es hoy.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-06-27 15:49:49