By: Tim Wiseman | Comments (3) | Related: > TSQL
Problem
There are certain circumstances where SQL Server will silently truncate data, without providing an error or warning, before it is inserted into a table. In this tip we cover some examples of when this occurs.
Solution
Normally, SQL Server will present an error on any attempt to insert more data into a field than it can hold:
Msg 8152, Level 16, State 14, Line 2 String or binary data would be truncated. The statement has been terminated.
SQL Server will not permit a silent truncation of data just because the column is too small to accept the data. But there are other ways that SQL Server can truncate data that is about to be inserted into a table that will not generate any form of error or warning.
ANSI_WARNINGS turned off
By default, ANSI_WARNINGS are turned on, and certain activities such as creating indexes on computed columns or indexed views require that they be turned on. But if they are turned off, SQL Server will truncate the data as needed to make it fit into the column. The ANSI_WARNINGS setting for a session can be controlled by:
SET ANSI_WARNINGS {ON|OFF}
Passing through a variable
Unlike with an insert into a table, SQL Server will quietly cut off data that is being assigned to a variable, regardless of the status of ANSI_WARNINGS. For instance:
declare @smallString varchar(5) declare @testint int set @smallString = 'This is a long string' set @testint = 123.456 print @smallString print @testint
Results in:
This 123
This is because SQL Server is trying to do an implicit conversion of the data type to the type of the variable.
This can occasionally show itself in subtle ways since passing a value into a stored procedure or function assigns it to the parameter variables and will quietly do a conversion. One method that can help guard against this situation is to give any parameter that will be directly inserted into a table a larger datatype than the target column so that SQL Server will raise the error, or perhaps to then check the length of the parameter and have custom code to handle it when it is too long.
For instance, if a stored procedure will use a parameter to insert data into a table with a column that is varchar(10), make the parameter varchar(15). Then if the data that is passed in is too long for the column, it will rollback and raise a truncation error instead of silently truncating and inserting. Of course, that runs the risk of being misleading to anyone who looks at the stored procedures header information without understanding what was done.
Unexpected Return Types
Many of SQL Server's built in functions determine their return type based on the parameter passed in. Some of these can determine their return type in subtle ways that can result in silent truncation. For instance, as Aaron Bertrand highlighted, isnull uses the datatype of the first parameter in determining its return type. So, if the second parameter is longer it can silently truncate to match.
Similarly, string concatenation can result in a silent truncation for long strings. Normally, the return from string concatenation is long enough to hold the result even if none of the variables used would be large enough on its own. So, if two variables that are char(5) are used, the result would be ten characters long. However, unless at least one of the strings concatenated is of a large value type (such as varchar(max) or nvarchar(max)) it will truncate rather than return a large value type. For instance:
declare @long1 varchar(max), @long2 varchar(max)
declare @short1 varchar(8000), @short2 varchar(8000)
set @short1 = replicate('0', 8000)
set @short2 = replicate('1', 8000)
set @long1 = @short1 + @short2 --neither is large value type, but @long1 is.
print len(@long1)
set @long2 = cast(@short1 as varchar(max)) + @short2 --one is large value type
print len(@long2)
/*Results:
8000
16000
*/
ASCII Null values
ASCII Null character values (char(0)) values are used by many systems to convey the end of a text string. The main SQL Engine simply handles them literally and will permit a value to be stored in a variable with a literal null character. However, the results in SSMS from a select and some client applications that SQL might hand the data off to later may take the presence of the null character to indicate the end of the string even if it was successfully inserted and is technically in the table. For example:
if object_id(N'testTbl', N'U') is not null
drop table testTbl
create table testTbl (col1 varchar(25))
insert into testTbl
values ('Start ' + char(0) + 'end')
select
col1, --Stops at the null
len(col1) as sLen, --Full length
replace(col1, char(0), '') as [NoNull] --Now the whole string
from testTbl
--But this prints out past the null
--Although displayed past the null, the results still include it
--Which can be carried over if copied and pasted.
print 'Start ' + char(0) + 'end'
/*Results:
(1 row(s) affected)
(1 row(s) affected)
Start end
*/
Next Steps
- Generally, SQL Server will warn about truncations on insert, but certain circumstances can cause a truncation without a warning or an error.
- Aaron Bertrand has more details on silent truncations with isnull
- Andy Novick has some details on the performance impact on implicit conversions and Robert Sheldon has details on both the precision and performance impacts of Data Conversion in SQL Server.
About the author
Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
