Quais são os casos de uso para selecionar CHAR sobre VARCHAR no SQL?

Eu percebo que o CHAR é recomendado se todos os meus valores forem de largura fixa. Mas e daí? Por que não apenas escolher VARCHAR para todos os campos de texto apenas para estar seguro.

Geralmente, escolha CHAR se todas as linhas tiverem quase o mesmo comprimento . Escolha VARCHAR quando o comprimento variar significativamente. CHAR também pode ser um pouco mais rápido, porque todas as linhas são do mesmo tamanho.

Ele varia de acordo com a implementação do BD, mas geralmente o VARCHAR usa um ou dois bytes de armazenamento (para comprimento ou terminação) além dos dados reais. Então (supondo que você está usando um conjunto de caracteres de um byte), armazenando a palavra “FooBar”

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

A linha inferior é CHAR pode ser mais rápida e mais eficiente em termos de espaço para dados com o mesmo comprimento (com uma diferença de comprimento de dois caracteres).

Nota : O Microsoft SQL possui 2 bytes de sobrecarga para um VARCHAR. Isso pode variar de DB para DB, mas geralmente há pelo menos 1 byte de sobrecarga necessária para indicar comprimento ou EOL em um VARCHAR.

Como foi apontado por Gaven nos comentários, se você estiver usando um conjunto de caracteres de vários bytes e comprimento variável como UTF8, CHAR armazena o número máximo de bytes necessários para armazenar o número de caracteres. Portanto, se UTF8 precisar de no máximo 3 bytes para armazenar um caractere, então CHAR (6) será fixado em 18 bytes, mesmo que apenas armazenando caracteres latin1. Então, neste caso, o VARCHAR se torna uma escolha muito melhor.

Se você está trabalhando comigo e está trabalhando com a Oracle, eu provavelmente faria você usar o varchar em quase todas as circunstâncias. A suposição de que char usa menos poder de processamento do que o varchar pode ser verdade … por enquanto … mas os mecanismos de database melhoram com o tempo e esse tipo de regra geral tem a criação de um “mito” futuro.

Outra coisa: eu nunca vi um problema de desempenho porque alguém decidiu ir com o varchar . Você fará um uso muito melhor do seu tempo escrevendo código bom (menos chamadas para o database) e SQL eficiente (como os índices funcionam, como o otimizador toma decisões, por que exists mais rápido do que normalmente …).

Pensamento final: Eu vi todos os tipos de problemas com o uso de CHAR , pessoas procurando ” quando eles deveriam estar procurando ”, ou pessoas procurando por ‘FOO’ quando deveriam estar procurando por ‘FOO (monte de espaços aqui) ‘, ou pessoas não aparando os espaços em branco ou bugs com o Powerbuilder adicionando até 2000 espaços em branco ao valor que ele retorna de um procedimento Oracle.

Além dos benefícios de desempenho, o CHAR pode ser usado para indicar que todos os valores devem ter o mesmo comprimento, por exemplo, uma coluna para abreviações de estado dos EUA.

Char é um pouco mais rápido, por isso, se você tiver uma coluna que você sabe que será um determinado período, use char. Por exemplo, armazenar (M) ale / (F) emale / (U) n conhecido para sexo ou 2 caracteres para um estado dos EUA.

O NChar ou o Char tem melhor desempenho que suas alternativas var?

Ótima pergunta. A resposta simples é sim em certas situações. Vamos ver se isso pode ser explicado.

Obviamente, todos sabemos que se eu criar uma tabela com uma coluna de varchar (255) (vamos chamar essa coluna de myColumn) e inserir um milhão de linhas, mas colocar apenas alguns caracteres em myColumn para cada linha, a tabela será muito menor número de páginas de dados necessárias pelo mecanismo de armazenamento) do que se eu tivesse criado myColumn como char (255). Sempre que eu fizer uma operação (DML) nessa tabela e solicitar muitas linhas, será mais rápido quando myColumn for varchar, porque não preciso mover todos esses espaços “extras” no final. Mover, como quando o SQL Server faz classificações internas, como durante uma operação distinta ou de união, ou se escolhe uma mesclagem durante o plano de consulta, etc. Mover também pode significar o tempo necessário para obter os dados do servidor para o local pc ou para outro computador ou onde quer que seja consumido.

Mas há alguma sobrecarga no uso de varchar. O SQL Server tem que usar um indicador de dois bytes (sobrecarga) para, em cada linha, saber quantos bytes o myColumn da linha em particular possui. Não são os 2 bytes extras que apresentam o problema, é ter que “decodificar” o tamanho dos dados em myColumn em cada linha.

Em minhas experiências, faz mais sentido usar char em vez de varchar em colunas que serão unidas em consultas. Por exemplo, a chave primária de uma tabela ou alguma outra coluna que será indexada. CustomerNumber em uma tabela demográfica, ou CodeID em uma tabela de decodificação, ou talvez OrderNumber em uma tabela de pedidos. Usando char, o mecanismo de consulta pode executar mais rapidamente a junit porque pode fazer aritmética de ponteiro reto (deterministicamente) em vez de ter que mover seus pointers uma quantidade variável de bytes à medida que lê as páginas. Eu sei que eu poderia ter perdido você nessa última frase. As junções no SQL Server são baseadas na idéia de “predicados”. Um predicado é uma condição. Por exemplo, myColumn = 1 ou OrderNumber <500.

Portanto, se o SQL Server estiver executando uma instrução DML, e os predicados, ou “chaves” unidas, tiverem comprimento fixo (char), o mecanismo de consulta não terá que trabalhar tanto para corresponder linhas de uma tabela a linhas de outra mesa. Ele não precisará descobrir quanto tempo os dados estão na linha e, em seguida, percorrer a seqüência para encontrar o final. Tudo isso leva tempo.

Agora, tenha em mente que isso pode ser implementado com facilidade. Eu vi o char usado para campos de chave primária em sistemas online. A largura deve ser mantida pequena, isto é, char (15) ou algo razoável. E funciona melhor em sistemas on-line porque você geralmente só recupera ou faz um pequeno número de linhas, portanto, ter que “rtrim” os espaços que você obterá no conjunto de resultados é uma tarefa trivial, em vez de ter que ingressar em milhões de linhas de uma tabela para milhões de linhas em outra tabela.

Outra razão pela qual o CHAR faz sentido sobre o varchar em sistemas online é que ele reduz as divisões de páginas. Usando char, você está essencialmente “reservando” (e desperdiçando) esse espaço, portanto, se um usuário aparecer mais tarde e colocar mais dados nessa coluna, o SQL já alocou espaço para ele e para ele.

Outro motivo para usar o CHAR é semelhante ao segundo motivo. Se um programador ou usuário fizer uma atualização “em lote” para milhões de linhas, adicionando alguma sentença a um campo de nota, por exemplo, você não receberá uma binding do seu DBA no meio da noite perguntando por que suas unidades estão cheias. Em outras palavras, isso leva a um crescimento mais previsível do tamanho de um database.

Então essas são 3 maneiras que um sistema online (OLTP) pode se beneficiar do char sobre varchar. Eu quase nunca uso char em um cenário de warehouse / análise / OLAP porque normalmente você tem TANTO de dados que todas essas colunas char podem adicionar até um monte de espaço desperdiçado.

Tenha em mente que o char pode tornar seu database muito maior, mas a maioria das ferramentas de backup tem compactação de dados, de modo que os backups tendem a ter aproximadamente o mesmo tamanho que se você tivesse usado o varchar. Por exemplo, LiteSpeed ​​ou RedGate SQL Backup.

Outro uso está nas visualizações criadas para exportar dados para um arquivo de largura fixa. Digamos que eu tenha que exportar alguns dados para um arquivo simples para ser lido por um mainframe. É largura fixa (não delimitada). Eu gosto de armazenar os dados em minha tabela “staging” como varchar (consumindo assim menos espaço no meu database) e, em seguida, usar uma visão para CAST tudo para ele é equivalente, com o comprimento correspondente à largura da largura fixa para essa coluna . Por exemplo:

 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 

Isso é legal porque internamente meus dados ocupam menos espaço porque está usando varchar. Mas quando eu uso DTS ou SSIS ou mesmo apenas um recorte e cola do SSMS para o Bloco de Notas, posso usar a visualização e obter o número correto de espaços à direita. No DTS nós costumávamos ter um recurso chamado, porra eu esqueço que eu acho que foi chamado de “sugerir colunas” ou algo assim. No SSIS você não pode mais fazer isso, você tem que definir tediosamente o gerenciador de conexões de arquivos simples. Mas como você tem sua configuração de visualização, o SSIS pode saber a largura de cada coluna e economizar muito tempo ao criar suas tarefas de stream de dados.

Então, linha de fundo … use varchar. Há um pequeno número de razões para usar char e é apenas por motivos de desempenho. Se você tiver um sistema com hundrends de milhões de linhas, verá uma diferença notável se os predicados forem determinísticos (char), mas para a maioria dos sistemas que usam char é simplesmente desperdício de espaço.

Espero que ajude. Jeff

Existem benefícios de desempenho, mas aqui está um que não foi mencionado: migration de linha. Com char, você reserva o espaço inteiro com antecedência. Então, vamos dizer que você tem um char (1000), e você armazena 10 caracteres, você usará todos os 1000 charaters do espaço. Em um varchar2 (1000), você usará apenas 10 caracteres. O problema surge quando você modifica os dados. Digamos que você atualize a coluna para agora conter 900 caracteres. É possível que o espaço para expandir o varchar não esteja disponível no bloco atual. Nesse caso, o mecanismo de database deve migrar a linha para outro bloco e fazer um ponteiro no bloco original para a nova linha no novo bloco. Para ler esses dados, o mecanismo de database agora terá que ler dois blocos.
Ninguém pode dizer equivocadamente que varchar ou char são melhores. Há um espaço para troca de tempo e consideração se os dados serão atualizados, especialmente se houver uma boa chance de que ele cresça.

Há uma diferença entre a otimização de desempenho inicial e o uso de um tipo de regra de prática recomendada. Se você está criando novas tabelas onde você sempre terá um campo de tamanho fixo, faz sentido usar o CHAR, você deve usá-lo nesse caso. Isso não é otimização inicial, mas sim implementar uma regra geral (ou melhor prática).

ie – Se você tiver um campo de estado de 2 letras, use CHAR (2). Se você tiver um campo com os nomes de estado reais, use VARCHAR.

Eu escolheria varchar a menos que a coluna armazenasse um valor fixo como o código de estado dos EUA – que é sempre de 2 caracteres de comprimento e a lista de códigos de estados dos EUA válidos não muda com frequência :).

Em todos os outros casos, mesmo como armazenar uma senha com hash (que é um tamanho fixo), eu escolheria varchar.

Por que – a coluna do tipo char sempre é preenchida com espaços, o que torna a coluna my_column definida como char (5) com o valor ‘ABC’ dentro da comparação:

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

falso.

Esse recurso pode levar a muitos bugs irritantes durante o desenvolvimento e dificulta os testes.

O CHAR ocupa menos espaço de armazenamento que o VARCHAR, se todos os seus valores de dados nesse campo tiverem o mesmo tamanho. Agora, talvez em 2009, um database de 800GB é o mesmo para todos os efeitos, como 810GB se você converteu os VARCHARs em CHARs, mas para strings curtas (1 ou 2 caracteres), CHAR ainda é uma “melhor prática” da indústria, eu diria.

Agora, se você olhar para a grande variedade de tipos de dados que a maioria dos bancos de dados fornece, mesmo para inteiros apenas (bit, tiny, int, bigint), existem razões para escolher um sobre o outro. Simplesmente escolher bigint toda vez é, na verdade, ser um pouco ignorante dos propósitos e usos do campo. Se um campo simplesmente representa a idade de uma pessoa em anos, um bigint é um exagero. Agora não é necessariamente “errado”, mas não é eficiente.

Mas é um argumento interessante, e como os bancos de dados melhoram com o tempo, pode-se argumentar que o CHAR vs VARCHAR se torna menos relevante.

Eu estou com o comentário de Jim McKeeth.

Além disso, a indexação e as varreduras de tabelas completas serão mais rápidas se sua tabela tiver apenas colunas CHAR. Basicamente, o otimizador será capaz de prever o tamanho de cada registro se ele tiver colunas CHAR, enquanto ele precisa verificar o valor de tamanho de cada coluna VARCHAR.

Além disso, se você atualizar uma coluna VARCHAR para um tamanho maior que seu conteúdo anterior, poderá forçar o database a reconstruir seus índices (porque você forçou o database a mover fisicamente o registro no disco). Enquanto com colunas CHAR isso nunca acontecerá.

Mas você provavelmente não se importará com o desempenho, a menos que sua mesa seja enorme.

Lembre-se das sábias palavras de Djikstra. A otimização inicial do desempenho é a raiz de todo o mal.

É a troca clássica de espaço versus desempenho.

No MS SQL 2005, Varchar (ou NVarchar para lanuagues que requerem dois bytes por caractere, isto é, chinês) são de tamanho variável. Se você adicionar à linha depois de ter sido gravada no disco rígido, ela localizará os dados em um local não contencioso na linha original e levará à fragmentação de seus arquivos de dados. Isso afetará o desempenho.

Portanto, se o espaço não é um problema, então Char é melhor para o desempenho, mas se você quiser manter o tamanho do database baixo, então os varchars são melhores.

Há uma pequena sobrecarga de processamento no cálculo do tamanho real necessário para um valor de coluna e a alocação do espaço para um Varchar, portanto, se você tiver certeza de quanto tempo o valor será sempre, é melhor usar Char e evitar o impacto.

Eu acho que no seu caso provavelmente não há razão para não escolher Varchar. Isso lhe dá flexibilidade e, como tem sido mencionado por alguns entrevistados, o desempenho é tal que, exceto em circunstâncias muito específicas, os mais mortais (ao contrário do Google DBA) não notarão a diferença.

Uma coisa interessante que vale a pena notar quando se trata de DB Types é que o sqlite (um popular mini database com desempenho bastante impressionante) coloca tudo no database como uma string e digita em tempo real.

Eu sempre uso o VarChar e geralmente o faço muito maior do que eu poderia precisar. Por exemplo. 50 para Firstname, como você diz porque não apenas estar seguro.

Muitas pessoas apontaram que, se você sabe a extensão exata do valor usando o CHAR, tem alguns benefícios. Mas enquanto armazenar Estados dos EUA como CHAR (2) é ótimo hoje, quando você recebe a mensagem das vendas de que “Acabamos de fazer nossa primeira venda para a Austrália”, você está em um mundo de dor. Eu sempre envio para superestimar quanto tempo eu acho que os campos precisarão ser, em vez de fazer um palpite ‘exato’ para cobrir events futuros. O VARCHAR me dará mais flexibilidade nessa área.

ao usar valores varchar, o SQL Server precisa de 2 bytes adicionais por linha para armazenar algumas informações sobre essa coluna, enquanto que, se você usar char, isso não será necessário, a menos que você

Fragmentação Char reserva espaço e o VarChar não. A divisão de página pode ser necessária para acomodar a atualização para varchar.

Em alguns bancos de dados SQL, o VARCHAR será preenchido até seu tamanho máximo para otimizar os deslocamentos. Isso é para acelerar varreduras e índices de tabela completos.

Devido a isso, você não tem nenhuma economia de espaço usando um VARCHAR (200) comparado a um CHAR (200)

O uso de CHAR (NCHAR) e VARCHAR (NVARCHAR) traz diferenças nas maneiras como o servidor de database armazena os dados. O primeiro introduz espaços em branco à direita; Eu encontrei problema ao usá-lo com o operador LIKE nas funções do SQL SERVER. Então eu tenho que torná-lo seguro usando VARCHAR (NVARCHAR) todas as vezes.

Por exemplo, se tivermos uma tabela TEST (ID INT, Status CHAR (1)) , e você escrever uma function para listar todos os registros com algum valor específico como o seguinte:

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

Nesta function esperamos que quando colocamos o parâmetro default a function retornará todas as linhas, mas na verdade não. Alterar o tipo de dados @Status para VARCHAR corrigirá o problema.

Eu nunca usaria chars. Eu tive esse debate com muitas pessoas e elas sempre trazem o clichê cansado de que char é mais rápido. Bem, eu digo, quanto mais rápido? O que estamos falando aqui, milissegundos, segundos e se sim quantos? Você está me dizendo porque alguém afirma que é alguns milissegundos mais rápido, devemos introduzir toneladas de dificuldade para consertar bugs no sistema?

Então, aqui estão alguns problemas que você vai encontrar:

Cada campo será preenchido, então você acaba com o código para sempre que tem RTRIMS em todos os lugares. Este também é um enorme desperdício de espaço em disco para os campos mais longos.

Agora digamos que você tenha o exemplo perfeito de um campo char de apenas um caractere, mas o campo é opcional. Se alguém passa uma corda vazia para esse campo, ela se torna um espaço. Então, quando outro aplicativo / processo o consultar, eles terão um único espaço, se não usarem o rtrim. Nós tivemos documentos xml, arquivos e outros programas, exibimos apenas um espaço, em campos opcionais e quebramos as coisas.

Então agora você tem que garantir que você está passando nulos e não string vazia, para o campo char. Mas esse NÃO é o uso correto de null. Aqui está o uso de null. Vamos dizer que você recebe um arquivo de um fornecedor

Nome | Sexo | Cidade Bob || Los Angeles

Se o gênero não for especificado, insira Bob, a string vazia e Los Angeles na tabela. Agora digamos que você obtenha o arquivo e suas alterações de formato, e o gênero não está mais incluído, mas foi no passado.

Nome | Cidade Bob | Seattle

Bem, já que gênero não está incluído, eu usaria null. Varchars suporta isso sem problemas.

Char, por outro lado, é diferente. Você sempre tem que enviar null. Se você enviar uma string vazia, você terá um campo com espaços.

Eu poderia continuar com todos os bugs que tive que consertar em chars e em cerca de 20 anos de desenvolvimento.