SQL Server - using DATALENGTH in where clause causes truncation error

Question

I have found a scenario where I have a temp table with a varchar(20) column, and I want to validate the the length of a string when it's being updated.

CREATE TABLE #tmpTest
(
    code1 VARCHAR(20)
)

This code causes a

data will be truncated

error:

DECLARE @CODE VARCHAR(10) = '0123456789';

UPDATE #tmpTest
SET code1 = '0123456789' + '/' + @CODE -- 21 chars
WHERE DATALENGTH('0123456789' + '/' + @CODE) <= 20;

Whereas:

DECLARE @CODE VARCHAR(10) = '0123456789';

IF DATALENGTH('0123456789' + '/' + @CODE) <= 20
BEGIN
    UPDATE #tmpTest
    SET code1 = '0123456789' + '/' + @CODE -- 21 chars
END

and

DECLARE @CODE VARCHAR(10) = '0123456789';

UPDATE #tmpTest
SET code1 = CASE 
              WHEN DATALENGTH('0123456789' + '/' + @CODE) <= 20  
                THEN '0123456789' + '/' + @CODE 
            END

Do not throw this error.

I'm curious to understand why this is happening. Is this something to do with the compiler / optimiser, or am I doing something wrong?

`WHERE 1=0` doesn't replicate the problem, however `GETDATE() = GETDATE() + 1` does. *Looks* like SQL Server is evaluating the expressions containing functions *first* (`1=0` doesn't have any functions, so appears to be calculated first instead) and seeing that the value in the `SET` is too large, and therefore generating an error. — Thom A, Nov 08 '19 at 12:28
[This](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d3ac15f1a58ca4a75c2cf8e001c9e8f2) however does not replicate the error, as the expression is no longer on literal values, but a column (albeit from a CTE). Using the literal value plays a part in this. — Thom A, Nov 08 '19 at 12:38
The `IF` and `CASE` conditions in your 2nd and 3rd statements respectively returns `FALSE` and so it wont even go inside the loop. So there is `no error` — Arun Palanisamy, Nov 08 '19 at 12:40
It is a common mistake to assume that you're safe if rows that would produce an error are excluded in the `WHERE`, but that's not how the optimizer works. Every T-SQL subexpression must be valid *in and of itself in every context*, because the optimizer considers itself free to rearrange things as it sees fit (bug reports to this effect have traditionally always been closed as "by design"). Even `CASE` is not completely safe here when combined with aggregates (this particular use of `CASE` is). The second block is entirely different because the query is never executed, courtesy of the `IF`. — Jeroen Mostert, Nov 08 '19 at 13:27

score 1 · Accepted Answer · answered Nov 08 '19 at 13:12

The error is raised even when the table is empty and there are no rows flowing through the plan. This is because (in the first case) the expression is evaluated up front during initialisation of the statement execution.

The call stack for the exception is below

In the second case the statement is never executed at all. In the third case the result of the CASE does not exceed 20 characters so does not cause a problem even if it is calculated during execution initialisation.

score 0 · Answer 2 · answered Nov 08 '19 at 12:36

The first query error is very clear the field is not enough to store the updated value.

error, string or binary data would be truncated when trying to insert

Second and third queries does not meet the condition for this reason they dont work. Could you execute the following query , you will see the UPDATE IS NOT WORK

CREATE TABLE #tmpTest
(
  code1 VARCHAR(20)
)

INSERT INTO #tmpTest VALUES('0123456789')
DECLARE @CODE VARCHAR(10) = '0123456789';

    IF DATALENGTH('0123456789' + '/' + @CODE) <= 20
      BEGIN
        UPDATE #tmpTest
        SET code1 = '0123456789' + '/' + @CODE -- 21 chars
        PRINT 'UPDATE IS WORK'
        END
        ELSE
        BEGIN
        PRINT 'UPDATE IS NOT WORK'
      END

SQL Server - using DATALENGTH in where clause causes truncation error

2 Answers2