Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – String or binary data would be truncated: O que é, como identificar a causa raiz e como corrigir

Post Views 41,633 views
Reading time 8 minutes

Hey guys!
Nesse artigo, quero explicar a vocês exatamente o que significa a mensagem “String or binary data would be truncated”, como podemos identificar qual a string que está causando o erro, como ocultar essa mensagem de erro (se você quiser), o que a mudança na sys.messages impactou nesse tema a partir do SQL Server 2016+ e muito mais!

Então se você tem dificuldades de identificar e corrigir ocorrências dessa mensagem de erro, hoje será a última vez que isso ainda será um problema pra você.

O que é “String or binary data would be truncated”

Um dos erros mais comuns do SQL Server, a mensagem “String or binary data would be truncated” ocorre quando um valor está tentando ser inserido ou atualizado em uma tabela e ele é maior que o tamanho máximo do campo.

Exemplo 1 – Tamanho máximo do campo com 10 caracteres, string com 10:

Exemplo 2 – Tamanho máximo do campo com 10 caracteres, string com 11:

Exemplo 3 – Difícil identificar o erro

Reparem no exemplo 3. Não estou mais inserindo um valor fixo e sim de uma outra tabela. Esse tipo de situação pode parecer simples num cenário como o dos exemplos 1 e 2, mas quando você está inserindo vários registros, ainda mais levando dados de várias colunas, é difícil identificar qual registro e de qual coluna que está causando essa mensagem de erro e essa tarefa pode acabar gastando mais tempo do que você gostaria.

Como ignorar o truncamento de strings

Caso você queira ignorar o truncamento de strings em um determinado momento ou operação, você tem a opção de fazer isso no SQL Server, conforme já havia demonstrado no artigo SQL Server – Porque NÃO utilizar SET ANSI_WARNINGS OFF. Eu não recomendo utilizar essa técnica de forma alguma, pois é uma solução que está apenas mascarando o problema e não corrigindo, mas eu gostaria de mostrar que isso existe e que é possível fazê-lo.

Quando você utiliza o comando SET ANSI_WARNINGS OFF, você faz com que o motor do SQL Server não gere mais esse erro na execução, fazendo com que o seu texto de 14 caracteres seja truncado e armazenado na coluna de 10 caracteres. Os caracteres excedentes serão silenciosamente descartados, ignorando e mascarando um problema na gravação dos dados do seu sistema, sem que ninguém fique sabendo.

Exemplo:

Retorno com SET ANSI_WARNINGS ON (Padrão):

Retorno com SET ANSI_WARNINGS OFF:

String or binary data would be truncated no SQL Server 2019

O SQL Server 2019 foi lançado no dia 04 de novembro de 2019, durante o Microsoft Ignite, e com ele, uma gama enorme de novos recursos foram lançados oficialmente.

Um desses novos recursos, são as novas mensagens disponíveis na sys.messages, que eu já havia compartilhado com vocês no meu artigo SQL Server 2019 – Lista de novidades e novos recursos desde o final de agosto de 2018:

Com essa mudança, agora ficou muito mais fácil identificar exatamente onde está ocorrendo o truncamento dos valores:

Conforme a documentação de traceflags, a traceflag 460 substitui a mensagem de ID 8152 de truncamento de dados pela mensagem de ID 2628. No nível de compatibilidade do banco de dados 150 em diante (2019), a mensagem de ID 2628 é o padrão e a traceflag 460 não tem nenhum efeito.

Ou seja, esse novo comportamento só funcionará automaticamente no SQL Server 2019, se o banco de dados da conexão estiver no modo de compatibilidade 150 em diante.

Observation: Essa mudança na sys.messages do SQL Server 2019 começou na release CTP2.0 e até sair a versão final, não era ativado automaticamente, exigindo as mesmas alterações das versões 2017 e 2016 para funcionar.

O que mudou a partir do SQL Server 2016+

Com as alterações necessárias para essa implementação no SQL Server 2019, a Microsoft acabou liberando essa nova mensagem também nas versões 2016 (a partir do SP2 Cumulative Update 6) e 2017 (A partir do Cumulative Update 12). E para conseguir utilizar esse nova mensagem, podemos utilizar 2 formas diferentes:

Forma 1: Utilizando parâmetro de inicialização -T460

A primeira forma e mais prática, é habilitando a traceflag 460 em toda a instância utilizando o parâmetro de inicialização -T460 no serviço do SQL Server:

Uma vez adicionado, será necessário reiniciar o serviço e a partir de então, a nova mensagem já estará funcionando por padrão, sem precisar fazer nenhuma alteração em query:

Forma 2: Utilizando traceflag 460

Uma forma que não exige reiniciar o serviço do SQL Server e também não exige alterar código, é utilizando o comando DBCC TRACEON, que permite ativar essa traceflag a nível de sessão e a nível de instância (global):

Nível de sessão (só afeta a sua sessão):

Nível de instância (global – afeta todas as sessões):

Script utilizado:

Observações: Para desativar a traceflag, utilize o comando DBCC TRACEOFF(460) ou DBCC TRACEOFF(460, -1), de acordo com o parâmetro utilizado na ativação. Caso você tenha ativado a traceflag a nível de instância, afetando todas as sessões, ao reiniciar o serviço do SQL Server, será necessário ativar a traceflag novamente.
Existia um bug no SQL Server 2017 CU12 e CU13, que fazia com que desse erro de “String or binary data would be truncated” em operações de inserção envolvendo variáveis do tipo tabela, quando o tamanho da string era maior que o campo mesmo em casos em que o código não era executado (Ex: Dentro de um IF com condição 1=0). Isso foi corrigido no SQL Server 2017 CU14.

Identificando truncamento de strings antes do SQL Server 2016

Caso você esteja utilizando uma versão anterior ao 2016 e tenha essa dificuldade em identificar quais os valores que estão estourando o limite de alguma coluna, vou compartilhar um script simples, que consegue identificar esse tipo de erro mesmo nas versões antigas do SQL Server, criando uma tabela “clone” da original, com o tamanho das colunas definido para o máximo, inserindo os dados nessa nova tabela e comparando o tamanho utilizado pelo taamnho das colunas na tabela original.

Exemplo de situação onde o script pode ser utilizado:

Tentei executar o meu comando de INSERT e ele deu erro em algum valor, de alguma coluna, que eu não sei qual é. Agora é o momento em que você pensa o trabalho que terá para identificar isso, especialmente se tiver umas 100 colunas ao invés de só essas 3 do exemplo.. Ter que inserir os dados numa tabela “clone” e ficar consultando e comparando o maior tamanho de cada coluna… Parece trabalhoso..

Para facilitar esse trabalho de identificação das colunas, vou compartilhar com vocês o script abaixo, que faz essa identificação para você. Lembre-se de alterar o nome da tabela origem e do script de INSERT/UPDATE na tabela clonada com o seu script original.

Com a execução desse script, teremos o retorno exato de quais colunas estão pequenas demais para os dados que estão recebendo e qual o tamanho ideal para conseguir armazenar esses dados.

Observation: Cuidado ao utilizar esse script em tabelas muito grandes. Como ele precisa pegar o maior tamanho de cada coluna da tabela, essa operação pode ser um pouco lenta e pesada em tabelas com milhões de registros. Então, tenha muito cuidado!

Espero que tenham gostado desse artigo, um grande abraço e até o próximo post!
Abraços

Referências: