Speak guys!
In this article, I want to explain to you exactly what the message “String or binary data would be truncated” means, how can we identify which string is causing the error, how to hide this error message (if you want), what the change in sys.messages impacted this theme from SQL Server 2016+ and more!
So if you have trouble identifying and correcting occurrences of this error message, today will be the last time this will still be a problem for you.
What is “String or binary data would be truncated”
One of the most common SQL Server errors, the message “String or binary data would be truncated” occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.
Example 1 - Maximum field size with 10 characters, string with 10:
Example 2 - Maximum field size with 10 characters, string with 11:
Example 3 - Difficult to identify the error
Look at the example 3. I am no longer entering a fixed value but from another table. This kind of situation may seem simple in a scenario such as the 1 and 2 examples, but when you are entering multiple records, especially taking data from multiple columns, it is difficult to identify which record and which column is causing this error message and This task may end up spending more time than you would like.
How to Bypass String Truncation
If you want to bypass string truncation at a given time or operation, you have the option to do so in SQL Server, as already shown in the article SQL Server - Why Not Use SET ANSI_WARNINGS OFF. I do not recommend using this technique at all, as it is a solution that is only masking the problem and not correcting it, but I would like to show that it exists and that it can be done.
When you use the SET ANSI_WARNINGS OFF command, you cause the SQL Server engine to no longer generate this error on execution, causing your 14 character text to be truncated and stored in the 10 character column. Excess characters will be silently discarded, ignoring and masking a problem writing your system data without anyone knowing.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SET NOCOUNT ON SET ANSI_WARNINGS OFF IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON' IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste CREATE TABLE #Teste ( Nome VARCHAR(10) ) INSERT INTO #Teste VALUES ('Dirceu Resende') -- 14 caracteres SELECT * FROM #Teste |
Return with SET ANSI_WARNINGS ON (Default):
Return with SET ANSI_WARNINGS OFF:
String or binary data would be truncated in SQL Server 2019
SQL Server 2019 was released on 04 November 2019, during Microsoft Ignite, and with it, a huge range of new features were officially released.
One of these new features is the new posts available on sys.messages, which I had already shared with you in my article. SQL Server 2019 - News and Features List since late August of 2018:
With this change, it's now much easier to identify exactly where the truncation of values is taking place:
That is, this new behavior will only work automatically in SQL Server 2019 if the connection database is in 150 compatibility mode onwards.
What has changed from SQL Server 2016 +
With the changes required for this implementation in SQL Server 2019, Microsoft eventually released this new message also in the 2016 (from SP2 Cumulative Update 6) and 2017 (From Cumulative Update 12) versions. And to be able to use this new message, we can use 2 in different ways:
1 Form: Using Initialization Parameter -T460
The first and most practical way is to enable 460 traceflag on the entire instance using the -T460 initialization parameter in the SQL Server service:
Once added, it will be necessary to restart the service and from then on, the new message will be working by default, without having to make any query changes:
2 Form: Using 460 Traceflag
One way that does not require restarting the SQL Server service and also does not require changing code is by using the DBCC TRACEON command, which allows you to enable this session level and instance level (global) traceflag:
Session level (only affects your session):
Instance level (global - affects all sessions):
Script used:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
PRINT @@VERSION CREATE TABLE dbo.Teste ( nome1 VARCHAR(10), nome2 VARCHAR(10), nome3 VARCHAR(10) ) -- Modo padrão - Mensagem anterior INSERT INTO dbo.Teste (nome1, nome2, nome3) VALUES('123456789', '1234567890', '12345678901') -- Nível de instância (global - afeta todas as sessões) DBCC TRACEON(460, -1); GO -- Nível de sessão (afeta apenas a sua sessão) DBCC TRACEON(460); GO -- Após ativar a traceflag, a mensagem é alterada INSERT INTO dbo.Teste (nome1, nome2, nome3) VALUES('123456789', '1234567890', '12345678901') |
Identifying String Truncation Before SQL Server 2016
If you are using a version prior to 2016 and have this difficulty in identifying which values are breaking the limit of any column, I will share a simple script, which can identify this type of error even in the old versions of SQL Server, creating a table “clone” of the original, with the size of the columns set to the maximum, inserting the data in this new table and comparing the size used by the size of the columns in the original table.
Example situation where script can be used:
I tried to execute my INSERT command and it gave an error in some value, in some column, which I don't know what it is. Now is the time when you think about the work you will have to do to identify this, especially if you have about 100 columns instead of just those 3 in the example .. Having to insert the data in a “clone” table and keep consulting and comparing the largest size of each column… It seems laborious ..
To make this column identification work easier, I will share with you the script below, which makes this identification for you. Remember to change the source table and script name from INSERT / UPDATE to the cloned table with your original script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
DECLARE @Nome_Tabela VARCHAR(255) = 'Teste' -- Crio uma tabela com a mesma estrutura IF (OBJECT_ID('tempdb..#Tabela_Clonada') IS NOT NULL) DROP TABLE #Tabela_Clonada SELECT * INTO #Tabela_Clonada FROM dbo.Teste -- Tabela original (LEMBRE-SE DE ALTERAR AQUI TAMBÉM) WHERE 1=2 -- Não quero copiar os dados -- Agora, vou alterar todas as colunas varchar/char/nvarchar/nchar para varchar(max) DECLARE @Query VARCHAR(MAX) = '' SELECT @Query += 'ALTER TABLE #Tabela_Clonada ALTER COLUMN [' + B.[name] + '] ' + UPPER(C.[name]) + '(MAX); ' FROM sys.tables A JOIN sys.columns B ON B.[object_id] = A.[object_id] JOIN sys.types C ON B.system_type_id = C.system_type_id WHERE A.[name] = @Nome_Tabela AND B.system_type_id IN (167, 175, 231, 239) -- varchar/char/nvarchar/nchar AND B.max_length > 0 EXEC(@Query) ------------------------------------------------------------------------------------------------------------------ -- Faço a inserção dos dados que estão apresentando erro na tabela temporária (LEMBRE-SE DE ALTERAR AQUI TAMBÉM) ------------------------------------------------------------------------------------------------------------------ INSERT INTO #Tabela_Clonada -- Alterar esse SELECT aqui pelo seu SELECT [name], [type_desc] + ' | ' + lock_escalation_desc, SCHEMA_NAME([schema_id]) FROM sys.tables -- Crio uma tabela temporária com o tamanho máximo de caracteres de cada coluna SET @Query = '' SELECT @Query = @Query + 'SELECT ' + QUOTENAME(A.[name], '''') + ' AS coluna, ' + QUOTENAME(B.[name], '''') + ' AS tipo, MAX(DATALENGTH(' + QUOTENAME(A.[name]) + ')) AS tamanho FROM #Tabela_Clonada UNION ' FROM tempdb.sys.columns A JOIN sys.types B on B.system_type_id = A.system_type_id and B.[name] <> 'sysname' WHERE A.[object_id] = OBJECT_ID('tempdb..#Tabela_Clonada') AND B.system_type_id IN (167, 175, 231, 239) -- varchar/char/nvarchar/nchar SET @Query = LEFT(@Query, LEN(@Query)-6) IF (OBJECT_ID('tempdb..#Tamanho_Maximo_Coluna') IS NOT NULL) DROP TABLE #Tamanho_Maximo_Coluna CREATE TABLE #Tamanho_Maximo_Coluna ( coluna VARCHAR(255), tipo VARCHAR(255), tamanho INT ) INSERT INTO #Tamanho_Maximo_Coluna EXEC(@Query) -- E por fim, faço a comparação dos dados de uma tabela pela outra SELECT B.[name] AS coluna, C.[name] AS tipo, B.max_length AS tamanho_atual, D.tamanho AS tamanho_necessario FROM sys.tables A JOIN sys.columns B ON B.[object_id] = A.[object_id] JOIN sys.types C ON B.system_type_id = C.system_type_id JOIN #Tamanho_Maximo_Coluna D ON B.[name] = D.coluna WHERE A.[name] = @Nome_Tabela AND B.system_type_id IN (167, 175, 231, 239) -- varchar/char/nvarchar/nchar AND B.max_length < D.tamanho |
Running this script will give you the exact return of which columns are too small for the data they are receiving and what is the optimal size to be able to store this data.
I hope you enjoyed this article, a big hug and until the next post!
Hugs
References:
- SQL Server 2019 - News and Features List
- SQL Server - Why Not Use SET ANSI_WARNINGS OFF
- How was SQL Server 2019 What's New Live on the dotNET Channel?
- https://www.sqlshack.com/sql-truncate-enhancement-silent-data-truncation-in-sql-server-2019/
- https://imasters.com.br/banco-de-dados/adeus-string-binary-data-truncated
- https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/
Very good. You are the guy. I had to make a small adaptation, from char to varchar, but it worked very well here. I think version incompatibility. helped me a lot with this old problem