Data Truncation Handling Techniques
Wednesday, June 9, 2010 at 8:42AM 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.
