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
[codesyntax lang=”tsql”]
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[/codesyntax]
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)
Output
What about Nvarchar?
Short Answer is Yes
[codesyntax lang=”tsql”]
create table truncateexample2
( id int ,
somenames nvarchar(100))
insert into truncateexample2
select 1,replicate('a',100)
insert into truncateexample2
select 2,replicate(N'ना',101)
[/codesyntax]
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.
Please Consider Subscribing
