SQL 2019:- Truncation Error message and where it fails

By | December 6, 2018

I was wondering if I should even bother checking this feature out since it was pretty obvious what it does and how useful it is. I can’t even begin to explain how many times I have had to troubleshoot truncations errors when working with customer data. The basic thing about this feature is that now when a truncation error happens you done just get a message saying

Msg 8152, Level 16, State 30, Line 8

String or binary data would be truncated.

You would get something like

String or binary data would be truncated in table ‘madworks.dbo.truncateexample’, column ‘somenames’. Truncated value: ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’.

With a clear explaination on the value and column causing the issue. So far great!!

But I did manage to find some issues with the implementation so here goes:-

Old Behavior

Run the below script to see how the code behaves by default

CREATE TABLE truncateexample 
( id INT , 
somenames VARCHAR(100))
 
INSERT INTO truncateexample
SELECT 1,REPLICATE('a',100)
 
INSERT INTO truncateexample
SELECT 2,REPLICATE('a',101)
GO

 

Output

New Behavior

The Trace flag needs to be enabled since the feature is disabled by default

DBCC TRACEON (460)
Go
 
INSERT INTO truncateexample
SELECT 1,REPLICATE('a',100)
 
INSERT INTO truncateexample
SELECT 2,REPLICATE('a',201)
GO

 

Output

As you can see from the above we get the truncate text as the output of the error message and not the full value, so in cases where you might have strings where the tail end differs you might still have to do some digging around and play with the len function to identify the exact row causing the problem.’

But I guess that is OK we can manage to that and it is still a big leap from not even knowing which column was being truncated.

Does it work for temp tables?

Short answer yes

CREATE TABLE #truncateexample 
( id INT , 
somenames VARCHAR(100))
 
 
INSERT INTO #truncateexample
SELECT 1,REPLICATE('a',100)
 
INSERT INTO #truncateexample
SELECT 2,REPLICATE('a',201)
GO

 

Output

What about Nvarchar?

Short Answer is Yes

CREATE TABLE truncateexample2 
( id INT , 
somenames NVARCHAR(100))
 
INSERT INTO truncateexample2
SELECT 1,REPLICATE('a',100)
 
INSERT INTO truncateexample2
SELECT 2,REPLICATE(N'ना',101)

 

Output

What if is leave trailing space at the end of the string?

Short answer there is no change in behavior here as SQL server doesn’t care about trailing spaces and ignores them anyway. But in my mind it is truncated if SQL has modified

This behavior doesn’t work for other hidden characters obviously.