I’ve built a stored procedure that analyses a source table or query with several characteristics per column among which the minimum length (min_len) and maximum length (max_len).
CREATE PROCEDURE [dbo].[sp_analysetable] (
@tableName varchar(8000),
@deep bit = 0
) AS
/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/
DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
if CHARINDEX('from', @tableName)>0
set @isQuery=1
IF @intErrorCode=0 BEGIN
if @isQuery=1 begin
--set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
--replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
exec(@tableName)
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
set @intErrorCode=1
SET @errorMSG='Error generating temporary table from query.'
end
else begin
set @tableName='##tmpTableToAnalyse'
end
end
end
IF @intErrorCode=0 BEGIN
SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
select
c.column_name as [column],
cast(sp.value as varchar(1000)) as description,
tc_fk.constraint_type,
kcu_pk.table_name as fk_table,
kcu_pk.column_name as fk_column,
c.ordinal_position as pos,
c.column_default as [default],
c.is_nullable as [null],
c.data_type,
c.character_maximum_length as length,
c.numeric_precision as [precision],
c.numeric_precision_radix as radix,
cast(null as bit) as [is_unique],
cast(null as int) as min_len,
cast(null as int) as max_len,
cast(null as int) as nulls,
cast(null as int) as blanks,
cast(null as int) as numerics,
cast(null as int) as distincts,
cast(null as varchar(500)) as distinct_values,
cast(null as varchar(50)) as remarks
into ##tmpColumns'
if @isQuery=1 begin
SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
end
else begin
SET @tmpQ=@tmpQ+'
from information_schema.columns c
left join sysobjects so on so.name=c.table_name and so.xtype=''U''
left join syscolumns sc on sc.name=c.column_name and sc.id =so.id
left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''
left join information_schema.key_column_usage kcu_fk on kcu_fk.table_name = c.table_name and c.column_name = kcu_fk.column_name
left join information_schema.table_constraints tc_fk on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
left join information_schema.table_constraints tc_pk on rc.unique_constraint_name = tc_pk.constraint_name
left join information_schema.key_column_usage kcu_pk on tc_pk.constraint_name = kcu_pk.constraint_name
'
end
SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''
exec(@tmpQ)
end
IF @intErrorCode=0 AND @deep = 1 BEGIN
DECLARE
@count_rows int,
@count_distinct int,
@count_nulls int,
@count_blanks int,
@count_numerics int,
@min_len int,
@max_len int,
@distinct_values varchar(500)
DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
select [column] from ##tmpColumns;
OPEN curTmp
FETCH NEXT FROM curTmp INTO @column_name
WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
' @count_rows=count(0), '+char(13)+char(10)+
' @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
' @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
' @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
' @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
' @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
' @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
' from ['+@tableName+']'
exec sp_executesql @tmpQ,
N'@count_rows int OUTPUT,
@count_distinct int OUTPUT,
@count_nulls int OUTPUT,
@count_blanks int OUTPUT,
@count_numerics int OUTPUT,
@min_len int OUTPUT,
@max_len int OUTPUT',
@count_rows OUTPUT,
@count_distinct OUTPUT,
@count_nulls OUTPUT,
@count_blanks OUTPUT,
@count_numerics OUTPUT,
@min_len OUTPUT,
@max_len OUTPUT
IF (@count_distinct>10) BEGIN
SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
END ELSE BEGIN
set @distinct_values=null
set @tmpQ = N'USE '+DB_NAME()+';'+
' select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar), cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
' from ('+char(13)+char(10)+
' select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
exec sp_executesql @tmpQ,
N'@distinct_values varchar(500) OUTPUT',
@distinct_values OUTPUT
END
UPDATE ##tmpColumns SET
is_unique =case when @count_rows=@count_distinct then 1 else 0 end,
distincts =@count_distinct,
nulls =@count_nulls,
blanks =@count_blanks,
numerics =@count_numerics,
min_len =@min_len,
max_len =@max_len,
distinct_values=@distinct_values,
remarks =
case when @count_rows=@count_nulls then 'all null,' else '' end+
case when @count_rows=@count_distinct then 'unique,' else '' end+
case when @count_distinct=0 then 'empty,' else '' end+
case when @min_len=@max_len then 'same length,' else '' end+
case when @count_rows=@count_numerics then 'all numeric,' else '' end
WHERE [column]=@column_name
FETCH NEXT FROM curTmp INTO @column_name
END
CLOSE curTmp DEALLOCATE curTmp
END
IF @intErrorCode=0 BEGIN
select * from ##tmpColumns order by pos
end
IF @intErrorCode=0 BEGIN --Clean up temporary tables
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
end
IF @intErrorCode<>0 BEGIN
RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode
I store this procedure in the master database so that I can use it in every database like so:
sp_analysetable 'table_name', 1
// deep=1 for doing value analyses
And the output is:
column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric,
guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric,
id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,
CREATE TABLE dbo.CoolPeople(PersonName VARCHAR(20), PrimaryCar VARCHAR(20));
GO
INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD');
GO
Машина Baby длиннее, чем 20 символов, поэтому при выполнении оператора INSERT получаем ошибку:
Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.
Это засада, поскольку у нас нет идей относительно того, какое поле вызвало проблемы! Это особенно ужасно, когда вы пытаетесь вставить множество строк.
Чтобы пофиксить ошибку, включите флаг трассировки 460
Флаг трассировки 460 был введен в SQL Server Sevice Pack 2, Cummulative Update 6, и в SQL Server 2017. (Вы можете найти и загрузить последние обновления с SQLServerUpdates.com.) Вы можете включить флаг на уровне запроса, например:
INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD')
OPTION (QUERYTRACEON 460);
GO
Теперь, если выполнить запрос, он покажет вам, какой столбец усекается, а также какая строка. В нашем случае мы имеем только одну строку, но в реальной жизни много полезней будет знать, какая строка вызвала ошибку:
Msg 2628, Level 16, State 1, Line 9
String or binary data would be truncated in
table 'StackOverflow2013.dbo.CoolPeople', column 'PrimaryCar'.
Truncated value: '2006 Subaru Impreza '.
Вы можете включить этот флаг трассировки как на уровне запроса (в нашем примере выше), так и на уровне сервера:
DBCC TRACEON(460, -1);
GO
Этот оператор включает его для всех, а не только для вас — поэтому сначала договоритесь со своей командой разработчиков, прежде чем включать его. Это изменит номер ошибки 8152 на 2628 (как показано выше), означающее, что если вы строили обработку ошибок на основании этих номеров, вы сразу получите другое поведение.
Я любитель включения этого флага трассировки на время отладки и изучения, но как только обнаруживаю источник проблем, выключаю его, снова выполнив команду:
DBCC TRACEON(460, -1);
GO
В нашем случае, как только мы идентифицировали избыточную длину машины Baby, необходимо либо изменить название машины, либо изменить тип данных в нашей таблице, чтобы сделать размер столбца больше. Можно также предварительно обрабатывать данные, явно отсекая избыточные символы. Мастерская по разборке данных, если хотите.
Не оставляйте этот флаг включенным
По крайней мере, имеется связанный с этим один баг в SQL Server 2017 CU13: табличные переменные будут выбрасывать ошибки, говорящие, что их содержимое усекается, даже если никакие данные не вставляются в них.
Вот простой скрипт, чтобы проверить, пофиксили ли это поведение:
CREATE OR ALTER PROC dbo.Repro @BigString VARCHAR(8000) AS
BEGIN
DECLARE @Table TABLE ( SmallString VARCHAR(128) )
IF ( 1 = 0 )
/* Это никогда не выполняется */
INSERT INTO @Table ( SmallString )
VALUES(@BigString)
OPTION (QUERYTRACEON 460)
END
GO
DECLARE @BigString VARCHAR(8000) = REPLICATE('blah',100)
EXEC dbo.Repro @BigString
GO
SQL Server 2017 CU13 всё еще сообщает об усечении строки, даже если строка не вставляется:
Переключение с табличной переменной на временную таблицу приводит к ожидаемому поведению:
Это замечательный пример, почему не следует использовать флаги трассировки по умолчанию. Конечно, они могут пофиксить проблемы, но они также могу вызвать непредсказуемое или нежелательное поведение. (И, вообще, я не фанат табличных переменных.)
Reading Time: 8 minutes
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.
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:
|
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:
According to traceflags documentation, the 460 traceflag replaces the data truncation 8152 ID message with the 2628 ID message. At the 150 database compatibility level onwards (2019), the 2628 ID message is the default and the 460 traceflag has no effect.
That is, this new behavior will only work automatically in SQL Server 2019 if the connection database is in 150 compatibility mode onwards.
Observation: This change to SQL Server 2019 sys.messages began in the CTP2.0 release and until the final release came out, it was not automatically activated, requiring the same changes as the 2017 and 2016 versions to work.
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’) |
Remarks: To disable traceflag, use the DBCC TRACEOFF (460) or DBCC TRACEOFF (460, -1) command, depending on the parameter used for activation. If you have enabled instance-level traceflag, affecting all sessions, when you restart the SQL Server service, you must enable traceflag again.
There was a bug in SQL Server 2017 CU12 and CU13, which caused this “String or binary data would be truncated” error in insertion operations involving table variables, when the string size was larger than the field even in cases where the code was not executed (Ex: Within an IF with condition 1 = 0). This was fixed in SQL Server 2017 CU14.
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.
Observation: Be careful when using this script on very large tables. Because it needs to get the largest size of each column in the table, this operation can be a little slow and cumbersome on tables with millions of records. So have very careful!
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/
- Remove From My Forums
-
Question
-
Hi all,
i have «String or binary data would be truncated» error when i try to execute an insert statment.
can i find witch field is affected by this error? (for return it to the user)
thank’s all
Answers
-
I had this very same problem recently when doing SQL Reporting. I solved by changing varchar(20) to varchar(50) in my temporary table declarations. It so happens that some of the older records in the table were bigger than 20 so when selecting into the temporary tables an error would result.
— Will
-
Marked as answer by
Friday, January 15, 2010 9:40 PM
-
Marked as answer by
-
As far as I am aware, there’s no way to determine which column’s length has been exceeded.
You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary — see the example below.
Again I stress that it would be better to modify the client application’s code to either warn the user or to limit the number of characters they can enter into a field.
Chris
Code Snippet
—This batch will fail with the SQL Server error message
DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))
DECLARE @MyParameter VARCHAR(100)
—Create a string of 52 chars in length
SET @MyParameter = REPLICATE(‘Z’, 52)
INSERT INTO @MyTable(MyValue)
VALUES (@MyParameter)
GO
—This batch will fail with a custom error message
DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))
DECLARE @MyParameter VARCHAR(100)
—Create a string of 52 chars in length
SET @MyParameter = REPLICATE(‘Z’, 52)
IF LEN(@MyParameter) > 10
BEGIN
RAISERROR(‘You attempted to insert too many characters into MyTable.MyValue.’, 16, 1)
RETURN
END
ELSE
BEGIN
INSERT INTO @MyTable(MyValue)
VALUES (@MyParameter)
END
GO
-
Unmarked as answer by
Kalman Toth
Saturday, November 10, 2012 4:52 AM -
Marked as answer by
Kalman Toth
Saturday, November 10, 2012 4:52 AM
-
Unmarked as answer by
-
Msg 8152, Level 16, State 14, Line 7 String or binary data would be truncated.
Answers to several of the posts above in this thread.
You can prevent this error very easily by getting the column
size metadata from INFORMATION_SCHEMA views (see T-SQL code below).SQL Server 2008 error message is not any different than previous versions.
Surely it would be easy to return the offending column on a single line insert! What if you are inserting 1 million rows with one statement and there are 100,000 offending rows?
Well that is tough….however, incredible it may sound but SSIS can handle it. This is a normal feature of SSIS Data
Flow. It filters out the offending rows, and there are several ways to do it.What can you do in T-SQL?
For one-liner insert you just compare the target length with the intended insert length.For datasets with multiple rows, you have to do filtering like:
1. WHERE ISNUMERIC (ColumnA) = 1
2. WHERE ISDATE (ColumnX) = 1
3. WHERE LEN (StagingName) <=(SELECT CHARACTER_MAXIMUM_LENGTH FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Production' AND TABLE_NAME='Product' AND COLUMN_NAME = 'Name' )
Column metadata demo.
-- OBTAIN COLUMN SIZE METADATA FROM INFORMATION_SCHEMA VIEW SELECT ColumnName=COLUMN_NAME, Limit=CHARACTER_MAXIMUM_LENGTH INTO #MaxLengths FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Production' AND TABLE_NAME='Product' AND DATA_TYPE IN ('varchar','nvarchar','char','nchar') SELECT * FROM #MaxLengths GO /* ColumnName Limit Name 50 ProductNumber 25 Color 15 Size 5 SizeUnitMeasureCode 3 WeightUnitMeasureCode 3 ProductLine 2 Class 2 Style 2 */ DROP TABLE #MaxLengths
Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016-
Marked as answer by
Kalman Toth
Saturday, June 25, 2011 9:26 AM -
Edited by
Kalman Toth
Wednesday, November 8, 2017 10:33 PM
-
Marked as answer by
In this SQL Server tutorial, we will understand the “String or binary data would be truncated” error in a SQL Server 2019, and we will also learn how to resolve this error. For this, we will cover the following topics.
- String or binary data would be truncated SQL Server Error
- String or binary data would be truncated SQL Server Which Column
- How to fix “String or binary data would be truncated”
- String or binary data would be truncated SQL Server 2019
- String or binary data would be truncated Datetime SQL Server
- String or binary data would be truncated in SQL Server Stored Procedure
This error usually occurs when we try to insert more data into a column than the specified column can store. Let’s understand this with the help of an example.
CREATE TABLE Sample (
id INT,
Name VARCHAR(10),
gender VARCHAR(8)
);
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender');In the example, we are creating a table with 3 columns in it, and for the Name column, we have defined the data type as “VARCHAR(10)“. It means that the Name column can hold a string having 10 characters.
But while inserting a value into the table, we have specified the value for the Name column with 15 characters which is more than 10.
So the above query will return the following error.
Read SQL Server bulk insert from CSV file
String or binary data would be truncated SQL Server Which Column
Till now, we have understood the reason why we are getting this error. Now let’s understand how we can find the column due to which we are getting the error.
In SQL Server 2019, Microsoft has updated the error message for this error. For SQL Server 2019, the error message not only returns the column name but also returns the table for which the error has been encountered.
For demonstration, consider the error message that we have shown in the previous section.
Now, if we read the error message thoroughly, we will easily get the column name for which the error has been encountered.
If you have upgraded from some previous SQL Server version to SQL Server 2019. Then you might need to execute the following query in the database where you want a complete truncate warning.
ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = ON;And for SQL Server 2016/2017, we have to turn on the trace flag 460. And to enable the trace flag 460, we can execute the following query while inserting or updated table records.
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender')
OPTION (QUERYTRACEON 460);Now by enabling the trace flag 460, the SQL Server will return a detailed truncation warning, from which we can easily identify the column name.
Read: Identity Column in SQL Server
How to fix “String or binary data would be truncated”
The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size. For this, we can use the ALTER TABLE command and increase the column size.
For demonstration, consider the following query, used to modify the column size of the sample table.
--Modifying Table
ALTER TABLE Sample
ALTER COLUMN Name VARCHAR(20)
GO
--Inserting the new record
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender')
GOIn the above example, first, we are using the ALTER TABLE and ALTER COLUMN statements to modify the size of the column. After this, we are using the INSERT statement to insert a new record. And if the inserted value is less than or equal to the size of the column, then the new record will be inserted successfully.
Read: Create Foreign Key in SQL Server
String or binary data would be truncated SQL Server 2019
While using SQL Server earlier versions, if we try to insert more data than specified for a column, we get the following error.
Now, this error message is not much helpful in debugging the issue. For this, we have to manually check the query to find and resolve the error.
But Microsoft has made some enhancements for this error message in SQL Server 2019. So, in SQL Server 2019, if this error has been encountered, the SQL Server will return a detailed truncate warning.
The column name, table name, even the value for which the error has been raised is mentioned in the error message. The example is already shown in the “String or binary data would be truncated SQL Server Which Column” topic.
If you are already using SQL Server 2019, but still not getting the detailed error message. Then execute the following query in the database where you want a complete truncate warning.
ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = ON;String or binary data would be truncated Datetime SQL Server
This issue commonly occurs when entering a record into a table with a VARCHAR or CHAR data type column and a value is longer than the column’s length. We will not get this error in the case of a Datetime datatype.
In the case of the DateTime data type, if we try to insert a longer value than the column size then the column will ignore the extra characters and save the value.
Now for demonstration, consider the following example given below.
--Creating table
CREATE TABLE Sample (
id INT,
Name VARCHAR(50),
Joining_date DATETIME
);
--inserting values
insert into Sample (id, Name, Joining_date) values (1, 'Modesty Malley', SYSDATETIME());- In the example, we are creating a table named “Sample” with 3 columns.
- Out of these 3 columns, we have defined the “Joining_date” column to be a DateTime column.
- Now, the Datetime column has a default fractional precision of 3, which means it can store a maximum of 3 characters in nanoseconds part (yyyy-mm-dd hh:mm:ss:nnn).
- But while inserting values, we are using the SYSDATETIME() function which returns the current date-time value in Datetime2 format. The SYSDATETIME() will return the date-time value with a fractional seconds precision of 7 (yyyy-mm-dd hh:mm:ss:nnnnnnn).
- But still, the value got successfully inserted in the Datetime column, but it has ignored the last 4 characters.
Read: Types of Backup in SQL Server
String or binary data would be truncated in SQL Server Stored Procedure
This error can also occur while executing a stored procedure in SQL Server, which is trying to insert new values in a column. But still, the reason behind this error will remain the same i.e, the column length is less than the value length that we are trying to insert.
Let’s understand this with the help of an example. And for this, we are using the following query to create a sample table.
--Creating table
CREATE TABLE Sample (
id INT,
Name VARCHAR(5),
Joining_date DATETIME
);Now take a look at the Name column, for the Name column we have defined the character length to be 5.
Next, we are using the following stored procedure to insert new records in the sample table.
ALTER PROCEDURE Insert_data
AS
--inserting values
insert into Sample (id, Name, Joining_date) values (1, 'Modesty Malley', GETDATE());
GO
Exec Insert_dataBut while inserting the record through the stored procedure, we have specified the name value to be 14 characters. So, if we try to execute this procedure, we will get the following error.
And the simplest solution to resolve this error is by modifying the column and increasing the column length. This solution is already been discussed in the topic above.
You may like the following SQL server tutorials:
- SQL Server Row_Number
- SQL Server Add Column + Examples
- IDENTITY_INSERT in SQL Server
- SQL Server stored procedure output parameter
So in this SQL Server tutorial, we have learned how to solve the “String or binary data would be truncated” error in a SQL Server, and we have also covered the below topics.
- String or binary data would be truncated SQL Server Error
- String or binary data would be truncated SQL Server Which Column
- How to fix “String or binary data would be truncated”
- String or binary data would be truncated SQL Server 2019
- String or binary data would be truncated Datetime SQL Server
- String or binary data would be truncated in SQL Server Stored Procedure
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.