transact - ¿Cuáles son los casos de uso para seleccionar CHAR sobre VARCHAR en SQL?

transact sql tutorial (19)

¿NChar o Char funcionan mejor que sus alternativas var?

Gran pregunta La respuesta simple es sí en ciertas situaciones. A ver si esto se puede explicar.

Obviamente, todos sabemos que si creo una tabla con una columna de varchar (255) (llamemos a esta columna myColumn) e inserto un millón de filas pero pongo solo unos pocos caracteres en myColumn para cada fila, la tabla será mucho más pequeña (en general). número de páginas de datos que necesita el motor de almacenamiento) que si hubiera creado myColumn como char (255). Cada vez que hago una operación (DML) en esa tabla y solicito muchas filas, será más rápido cuando myColumn sea varchar porque no tengo que moverme por todos esos espacios "adicionales" al final. Mover, como en el caso en que SQL Server realiza clasificaciones internas como, por ejemplo, durante una operación distinta o de unión, o si elige una combinación durante su plan de consulta, etc. Mover también puede significar el tiempo que lleva obtener los datos del servidor a mi local PC o a otra computadora o donde sea que se vaya a consumir.

Pero hay algo de sobrecarga en el uso de varchar. SQL Server tiene que usar un indicador de dos bytes (sobrecarga) para, en cada fila, saber cuántos bytes tiene myColumn de esa fila en particular. No son los 2 bytes adicionales lo que presenta el problema, es la necesidad de "descodificar" la longitud de los datos en myColumn en cada fila.

En mi experiencia, tiene más sentido utilizar char en lugar de varchar en columnas a las que se unirá en consultas. Por ejemplo, la clave principal de una tabla, o alguna otra columna que se indexará. CustomerNumber en una tabla demográfica, o CodeID en una tabla de decodificación, o quizás OrderNumber en una tabla de orden. Al usar char, el motor de consulta puede realizar la unión más rápidamente porque puede hacer aritmética de punteros directos (determinísticamente) en lugar de tener que mover sus punteros a una cantidad variable de bytes a medida que lee las páginas. Sé que podría haberte perdido en esa última frase. Las uniones en SQL Server se basan en la idea de "predicados". Un predicado es una condición. Por ejemplo, myColumn = 1 o OrderNumber <500.

Entonces, si SQL Server está realizando una declaración DML, y los predicados, o las "claves" que se unen son de una longitud fija (char), el motor de consulta no tiene que hacer tanto trabajo para hacer coincidir las filas de una tabla con las filas de otra mesa No tendrá que averiguar cuánto tiempo los datos están en la fila y luego caminar por la cadena para encontrar el final. Todo eso lleva tiempo.

Ahora tenga en cuenta que esto puede ser implementado pobremente. He visto el uso de char para campos de clave principal en sistemas en línea. El ancho debe mantenerse pequeño, es decir, char (15) o algo razonable. Y funciona mejor en sistemas en línea porque normalmente solo está recuperando o subiendo un pequeño número de filas, por lo que tener que "restringir" esos espacios finales que obtendrá en el conjunto de resultados es una tarea trivial en lugar de tener que unirse a millones de filas de una tabla a millones de filas en otra tabla.

Otra razón por la que CHAR tiene sentido sobre varchar en los sistemas en línea es que reduce la división de páginas. Al usar char, básicamente estás "reservando" (y desperdiciando) ese espacio, de modo que si un usuario llega más tarde y coloca más datos en esa columna, SQL ya ha asignado espacio para él y ya está.

Otra razón para usar CHAR es similar a la segunda razón. Si un programador o usuario realiza una actualización "por lotes" a millones de filas, por ejemplo, si agrega alguna frase a un campo de nota, no recibirá una llamada de su DBA en medio de la noche preguntándose por qué sus unidades están llenas. En otras palabras, conduce a un crecimiento más predecible del tamaño de una base de datos.

Así que esas son 3 formas en que un sistema en línea (OLTP) puede beneficiarse de la función char sobre varchar. Casi nunca uso char en un escenario de almacén / análisis / OLAP porque, por lo general, tiene TANTOS datos que todas esas columnas de char pueden agregar a un montón de espacio desperdiciado.

Tenga en cuenta que char puede hacer que su base de datos sea mucho más grande, pero la mayoría de las herramientas de copia de seguridad tienen compresión de datos, por lo que sus copias de seguridad tienden a ser del mismo tamaño que si hubiera utilizado varchar. Por ejemplo, LiteSpeed o RedGate SQL Backup.

Otro uso es en vistas creadas para exportar datos a un archivo de ancho fijo. Digamos que tengo que exportar algunos datos a un archivo plano para ser leído por un mainframe. Es ancho fijo (no delimitado). Me gusta almacenar los datos en mi tabla de "preparación" como varchar (por lo tanto, consumir menos espacio en mi base de datos) y luego usar una vista para CAST todo a su equivalente en caracteres, con la longitud correspondiente al ancho del ancho fijo para esa columna . Por ejemplo:

create table tblStagingTable ( pkID BIGINT (IDENTITY,1,1), CustomerFirstName varchar(30), CustomerLastName varchar(30), CustomerCityStateZip varchar(100), CustomerCurrentBalance money ) insert into tblStagingTable (CustomerFirstName,CustomerLastName, CustomerCityStateZip) (''Joe'',''Blow'',''123 Main St Washington, MD 12345'', 123.45) create view vwStagingTable AS SELECT CustomerFirstName = CAST(CustomerFirstName as CHAR(30)), CustomerLastName = CAST(CustomerLastName as CHAR(30)), CustomerCityStateZip = CAST(CustomerCityStateZip as CHAR(100)), CustomerCurrentBalance = CAST(CAST(CustomerCurrentBalance as NUMERIC(9,2)) AS CHAR(10)) SELECT * from vwStagingTable

Esto es genial porque internamente mis datos ocupan menos espacio porque está usando varchar. Pero cuando uso DTS o SSIS o incluso solo un corte y pegado desde SSMS a Bloc de notas, puedo usar la vista y obtener el número correcto de espacios finales. En DTS solíamos tener una función llamada, maldita sea, se me olvida que creo que se llamaba "sugerir columnas" o algo así. En SSIS ya no puede hacer eso, debe definir tediosamente el administrador de conexión de archivos planos. Pero como tiene configurada la vista, SSIS puede conocer el ancho de cada columna y puede ahorrar mucho tiempo al crear sus tareas de flujo de datos.

Así que la línea de fondo ... use varchar. Hay un número muy pequeño de razones para usar char y es solo por razones de rendimiento. Si tiene un sistema con cientos de millones de filas, verá una diferencia notable si los predicados son deterministas (char) pero para la mayoría de los sistemas, usar char es simplemente perder espacio.

Espero que ayude. Jeff

Me doy cuenta de que se recomienda CHAR si todos mis valores son de ancho fijo. ¿Y qué? ¿Por qué no elegir VARCHAR para todos los campos de texto solo para estar seguro?

Además de los beneficios de rendimiento, se puede usar CHAR para indicar que todos los valores deben tener la misma longitud, por ejemplo, una columna para las abreviaturas del estado de EE. UU.

CHAR ocupa menos espacio de almacenamiento que VARCHAR si todos sus valores de datos en ese campo tienen la misma longitud. Ahora, tal vez en 2009, una base de datos de 800 GB es la misma para todos los intentos y para 810 GB si convirtió los VARCHAR en CHAR, pero para cadenas cortas (1 o 2 caracteres), CHAR sigue siendo una "mejor práctica" de la industria, diría.

Ahora, si observa la gran variedad de tipos de datos que la mayoría de las bases de datos brindan, incluso para los enteros solos (bit, tiny, int, bigint), HAY razones para elegir una sobre la otra. Simplemente elegir bigint cada vez es, en realidad, ser un poco ignorante de los propósitos y usos del campo. Si un campo simplemente representa la edad de una persona en años, un bigint es excesivo. Ahora no es necesariamente "incorrecto", pero no es eficiente.

Pero es un argumento interesante, y como las bases de datos mejoran con el tiempo, podría argumentarse que CHAR vs VARCHAR se vuelve menos relevante.

Char es un poco más rápido, así que si tienes una columna que SABES que tendrá una cierta longitud, usa char. Por ejemplo, almacenar (M) ale / (F) emale / (U) no se conoce para el género, o 2 caracteres para un estado de EE. UU.

Creo que en tu caso probablemente no haya razón para no elegir a Varchar. Le brinda flexibilidad y, como lo mencionaron varios encuestados, el desempeño es tal que ahora, excepto en circunstancias muy específicas, los mortales meer mortales (en lugar de los DBA de Google) no notarán la diferencia.

Una cosa interesante que vale la pena destacar cuando se trata de tipos de base de datos es el sqlite (una popular mini base de datos con un rendimiento bastante impresionante) pone todo en la base de datos como una cadena y tipos sobre la marcha.

Siempre uso VarChar y generalmente lo hago mucho más grande de lo que podría necesitar. P.ej. 50 para Firstname, como dices, por qué no solo para estar seguro.

El uso de CHAR (NCHAR) y VARCHAR (NVARCHAR) genera diferencias en las formas en que el servidor de la base de datos almacena los datos. El primero introduce espacios en blanco al final; He encontrado un problema al usarlo con el operador LIKE en las funciones del SERVIDOR SQL. Así que tengo que hacerlo seguro usando VARCHAR (NVARCHAR) todas las veces.

Por ejemplo, si tenemos una tabla TEST (ID INT, Estado CHAR (1)) , y escribe una función para enumerar todos los registros con un valor específico como el siguiente:

CREATE FUNCTION List(@Status AS CHAR(1) = '''') RETURNS TABLE AS RETURN SELECT * FROM TEST WHERE Status LIKE ''%'' + @Status ''%''

En esta función esperamos que cuando pongamos el parámetro predeterminado, la función devolverá todas las filas, pero en realidad no lo hace. Cambiar el tipo de datos @Status a VARCHAR solucionará el problema.

Elegiría varchar a menos que la columna almacene un valor fijo como el código de estado de EE. UU., Que siempre tiene 2 caracteres y la lista de códigos de estado de EE. UU. Válidos no cambia a menudo :).

En cualquier otro caso, incluso como almacenar una contraseña con hash (que es de longitud fija), elegiría varchar.

Por qué: la columna de tipo char siempre se llena con espacios, lo que hace que la columna my_column se defina como char (5) con valor ''ABC'' dentro de la comparación:

my_column = ''ABC'' -- my_column stores ''ABC '' value which is different then ''ABC''

falso.

Esta característica podría provocar muchos errores irritantes durante el desarrollo y hace que las pruebas sean más difíciles.

En algunas bases de datos SQL, VARCHAR se completará hasta su tamaño máximo para optimizar las compensaciones. Esto es para acelerar los escaneos e índices de la tabla completa.

Debido a esto, no tiene ningún ahorro de espacio utilizando un VARCHAR (200) en comparación con un CHAR (200)

En general, elija CHAR si todas las filas tendrán cerca de la misma longitud . Elija VARCHAR cuando la longitud varía significativamente. CHAR también puede ser un poco más rápido porque todas las filas son de la misma longitud.

Varía según la implementación de la base de datos, pero generalmente VARCHAR usa uno o dos bytes más de almacenamiento (por longitud o terminación) además de los datos reales. Entonces (asumiendo que estás usando un juego de caracteres de un byte) almacenando la palabra "FooBar"

CHAR (6) = 6 bytes (sin sobrecarga)
VARCHAR (10) = 8 bytes (2 bytes de sobrecarga)
CHAR (10) = 10 bytes (4 bytes de sobrecarga)

La conclusión es que CHAR puede ser más rápido y más eficiente en el espacio para datos de la misma longitud (dentro de la diferencia de longitud de dos caracteres).

Nota : Microsoft SQL tiene 2 bytes de sobrecarga para un VARCHAR. Esto puede variar de una base de datos a otra, pero generalmente hay al menos 1 byte de sobrecarga necesaria para indicar la longitud o EOL en un VARCHAR.

Como lo señaló Gaven en los comentarios, si está utilizando un conjunto de caracteres de múltiples bytes y longitud variable como UTF8, CHAR almacena el número máximo de bytes necesarios para almacenar el número de caracteres. Entonces, si UTF8 necesita un máximo de 3 bytes para almacenar un carácter, entonces CHAR (6) se fijará en 18 bytes, incluso si solo se almacenan caracteres latin1. Así que en este caso VARCHAR se convierte en una opción mucho mejor.

Es el intercambio clásico entre el espacio y el rendimiento.

En MS SQL 2005, Varchar (o NVarchar para lanuagues que requieren dos bytes por carácter, es decir, chino) son de longitud variable. Si agrega a la fila después de que se haya escrito en el disco duro, ubicará los datos en una ubicación no contigiosa en la fila original y provocará la fragmentación de sus archivos de datos. Esto afectará el rendimiento.

Por lo tanto, si el espacio no es un problema, Char es mejor para el rendimiento, pero si desea mantener el tamaño de la base de datos hacia abajo, los varchars son mejores.

Estoy de acuerdo con el comentario de Jim McKeeth.

Además, la indexación y las exploraciones de tablas completas son más rápidas si su tabla solo tiene columnas CHAR. Básicamente, el optimizador podrá predecir qué tan grande es cada registro si solo tiene columnas CHAR, mientras que necesita verificar el valor del tamaño de cada columna VARCHAR.

Además, si actualiza una columna VARCHAR a un tamaño mayor que su contenido anterior, puede forzar a la base de datos a reconstruir sus índices (porque obligó a la base de datos a mover físicamente el registro en el disco). Mientras que con las columnas CHAR eso nunca sucederá.

Pero es probable que no te importe el impacto de rendimiento a menos que tu mesa sea enorme.

Recuerda las sabias palabras de Djikstra. La optimización temprana del rendimiento es la raíz de todo mal.

Existe una diferencia entre la optimización temprana del rendimiento y el uso de un tipo de regla de mejores prácticas. Si está creando nuevas tablas en las que siempre tendrá un campo de longitud fija, tiene sentido utilizar CHAR, debe usarlo en ese caso. Esta no es una optimización temprana, sino que implementa una regla general (o la mejor práctica).

es decir, si tiene un campo de estado de 2 letras, use CHAR (2). Si tiene un campo con los nombres de los estados reales, use VARCHAR.

Existe una pequeña sobrecarga de procesamiento al calcular el tamaño real necesario para un valor de columna y asignar el espacio para un Varchar, por lo que si está definitivamente seguro de cuánto tiempo será el valor, es mejor usar Char y evitar el impacto.

Fragmentación. Char reserva espacio y VarChar no. Se puede requerir división de página para adaptar la actualización a varchar.

Hay beneficios de rendimiento, pero aquí hay uno que no se ha mencionado: la migración de filas. Con char, reserva todo el espacio por adelantado. Así que digamos que tiene un char (1000) y que almacena 10 caracteres, utilizará los 1000 caracteres del espacio. En varchar2 (1000), solo usarás 10 caracteres. El problema viene cuando modificas los datos. Digamos que actualiza la columna para que ahora contenga 900 caracteres. Es posible que el espacio para expandir el varchar no esté disponible en el bloque actual. En ese caso, el motor de base de datos debe migrar la fila a otro bloque y hacer un puntero en el bloque original a la nueva fila en el nuevo bloque. Para leer estos datos, el motor de DB ahora tendrá que leer 2 bloques.
Nadie puede decir equívocamente que varchar o char son mejores. Hay un espacio para el intercambio de tiempo, y la consideración de si los datos se actualizarán, especialmente si hay una buena probabilidad de que crezca.

Muchas personas han señalado que si conoce la longitud exacta del valor, el uso de CHAR tiene algunos beneficios. Pero mientras que almacenar estados de EE. UU. Como CHAR (2) es excelente hoy, cuando recibe el mensaje de ventas de que "acabamos de hacer nuestra primera venta a Australia", se encuentra en un mundo de dolor. Siempre envío para sobreestimar el tiempo que pienso que los campos deberán tener en lugar de hacer una conjetura ''exacta'' para cubrir eventos futuros. VARCHAR me dará más flexibilidad en esta área.

NUNCA usaría caracteres. He tenido este debate con mucha gente y siempre mencionan el cliché de que char es más rápido. Pues yo digo, ¿cuánto más rápido? ¿De qué estamos hablando aquí, milisegundos, segundos y, si es así, cuántos? ¿Me estás diciendo que alguien dice que es unos milisegundos más rápido, deberíamos introducir toneladas de errores difíciles de corregir en el sistema?

Así que aquí hay algunos problemas con los que se encontrará:

Cada campo se rellenará, por lo que terminará con el código para siempre que tiene RTRIMS en todas partes. Esto también es un gran desperdicio de espacio en disco para los campos más largos.

Ahora digamos que tiene el ejemplo por excelencia de un campo de carácter de un solo carácter, pero el campo es opcional. Si alguien pasa una cadena vacía a ese campo, se convierte en un espacio. Entonces, cuando otra aplicación / proceso lo consulta, obtienen un solo espacio, si no usan rtrim. Hemos tenido documentos xml, archivos y otros programas, muestra solo un espacio, en campos opcionales y rompe cosas.

Así que ahora tienes que asegurarte de que estás pasando nulos y no una cadena vacía al campo char. Pero ese NO es el uso correcto de null. Aquí está el uso de null. Digamos que obtienes un archivo de un proveedor

Nombre | Género | Ciudad Bob || Los Ángeles

Si no se especifica el género, ingrese Bob, la cadena vacía y Los Ángeles en la tabla. Ahora digamos que obtienes el archivo y su formato cambia y el género ya no se incluye, pero fue en el pasado.

Nombre | Ciudad Bob | Seattle

Bueno, ahora que el género no está incluido, usaría null. Varchars apoyan esto sin problemas.

Char por otro lado es diferente. Siempre hay que enviar nulos. Si alguna vez envía una cadena vacía, terminará con un campo que tiene espacios en ella.

Podría seguir y seguir con todos los errores que tuve que solucionar de los caracteres y en aproximadamente 20 años de desarrollo.

cuando se usan valores de varchar, SQL Server necesita 2 bytes adicionales por fila para almacenar cierta información sobre esa columna, mientras que si usa char, no lo necesita, a menos que

Si trabajas conmigo y trabajas con Oracle, probablemente te haré usar varchar en casi todas las circunstancias. La suposición de que char utiliza menos poder de procesamiento que varchar puede ser cierta ... por ahora ... pero los motores de base de datos mejoran con el tiempo y este tipo de regla general tiene la creación de un futuro "mito".

Otra cosa: nunca he visto un problema de rendimiento porque alguien decidió ir con varchar . Hará un mejor uso de su tiempo escribiendo buen código (menos llamadas a la base de datos) y SQL eficiente (cómo funcionan los índices, cómo toma decisiones el optimizador, por qué es más rápido que in general ...).

Pensamiento final: he visto todo tipo de problemas con el uso de CHAR , personas que buscan '''' cuando deberían buscar '''', o personas que buscan ''FOO'' cuando deberían buscar ''FOO (montón de espacios aquí) '', o personas que no recortan los espacios en blanco finales, o errores con Powerbuilder agregando hasta 2000 espacios en blanco al valor que retorna de un procedimiento de Oracle.