Data Truncation Handling Techniques

Question:

we are trying to get this to error because the value is getting truncated. What do i need to change on our warning settings?
SELECT CONVERT(varchar(10), 'adededddasdfasdf')

On a regular basis I use CONVERT(VARCHAR(10), GETDATE(), 101) to convert

2010-06-09 08:03:07.313    to    2010-06-09

I'm counting on the truncation to cut out the data I don't want. I don't recall ever getting a truncation error in this case.  So there may not be a setting to cause this to occur.  Unfortunately, I do not know the direct answer to the question.

On the other hand truncation errors are common when doing ETL projects.  Here is an example of what does cause a truncation error to bubble up.

CREATE TABLE #MyTable ( MyField VARCHAR(10) NOT NULL ) 

INSERT INTO #MyTable ( MyField )VALUES ( 'abcdefghij') 
INSERT INTO #MyTable ( MyField )VALUES ( '12345678901112') 

SELECT * FROM #MyTable AS mt 

Executing this code will have the following in the message window:

(1 row(s) affected)
Msg 8152, Level 16, State 14, Line 10
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)

This code will cause a truncation error to abort the second insert statement.   The error is useful and important because it helps maintain data integrity.  On the other hand it would be nice to find a way to avoid the problem in the first place

Here are some techniques for preemptively dealing with truncation errors:

When dealing with unstable or unverified data sources a good approach is to validate the data prior to loading. Typically this involves loading the data in to varchar(max) fields and running various metadata inspections such as LEN(), ISNUMERIC(), ISDATE(), etc.

Yes it takes time, but in an automated process it is more important to run without error and be able to generate meaningful error reports on what data is flawed.

---------------------------------------
-- In line validation
---------------------------------------
;
WITH SourceData AS (    
    SELECT 'adededddasdfasdf' AS MyField
    UNION ALL SELECT 'abcdefghij'
    UNION ALL SELECT '12345678901112'
    UNION ALL SELECT '1234567890'
)
SELECT
      CONVERT(VARCHAR(10), MyField) AS MyField_Len10
    , CASE    
        WHEN LEN(MyField) <= 10 THEN 0
        ELSE 1
      END AS IsTruncated
FROM 
    SourceData


---------------------------------------
-- List of truncation errors - with option raised error if it's really needed
---------------------------------------
DECLARE @ErrorCount    INT
SET @ErrorCount = 0
;
WITH SourceData AS (    
    SELECT 'adededddasdfasdf' AS MyField
    UNION ALL SELECT 'abcdefghij'
    UNION ALL SELECT '12345678901112'
    UNION ALL SELECT '1234567890'
)
SELECT
      CONVERT(VARCHAR(10), MyField) AS MyField_Len10
FROM 
    SourceData
WHERE
    LEN(MyField) > 10    

SET @ErrorCount = @@ROWCOUNT
IF @ErrorCount != 0
    BEGIN
        RAISERROR('There were %d truncation errors', 16,1,@ErrorCount)
    END

A more useful approach to check for the error case.

GO
---------------------------------------
-- Alt solution without returning data
---------------------------------------
DECLARE @ErrorCount    INT
SET @ErrorCount = 0
;
WITH SourceData AS (    
    SELECT 'adededddasdfasdf' AS MyField
    UNION ALL SELECT 'abcdefghij'
    UNION ALL SELECT '12345678901112'
    UNION ALL SELECT '1234567890'
)
SELECT
     @ErrorCount = COUNT(*) 
FROM 
    SourceData
WHERE
    LEN(MyField) > 10    

--SET @ErrorCount = @@ROWCOUNT
IF @ErrorCount != 0
    BEGIN
        RAISERROR('There were %d truncation errors', 16,1,@ErrorCount)
    END

The RAISERROR code is there to illustrate how to cause an error to bubble up if that is the desired behavior.

Note: The IF EXISTS() syntax would also have worked well in this case.  However, If EXISTS()  doesn’t work with CTEs and the error count would not have been available for the error message.