Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Silent Truncation of SQL Server Data Inserts

MSSQLTips author Tim Wiseman By:   |   Read Comments (2)   |   Related Tips: More > T-SQL
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
 */

Null Results
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.


Last Update: 1/18/2013


About the author
MSSQLTips author Tim Wiseman
Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, October 01, 2014 - 8:59:39 PM - TimothAWiseman Read The Tip

@Ray  You are entirely right.  Some of the truncations I mention are caused by implicit conversions.  But data can be lost that way, and it is something that is all to easy to forget about since it happens silently without a warning.


Thursday, September 25, 2014 - 11:12:27 AM - Ray Read The Tip

Some of the events you attribute to truncation are actually the result of T-SQL implicit conversions that are implemented for the "convenience" of developers.  Specially assigning a Float or Real Literal to an Integer receptacle.

The BOL article "Cast and Convert (Transact-SQL)" contains a nice matrix of Implicit conversions and the statement "Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function".  The chart included in that article clearly shows that almost all of the numeric data types will silently convert to Integer.

I guess one could argue either side of the proposition "T-SQL is a strongly typed language" but the presence of so many automatic, implicit conversions certainly makes it IMPLICIT on the developer to clearly understand declare their data.




 
Sponsor Information