'String or binary data would be truncated' without any data exceeding the length-CodePudding

Yesterday suddenly a report occurred that someone was not able to get some data anymore because the issue Msg 2628, Level 16, State 1, Line 57 String or binary data would be truncated in table 'tempdb.dbo.#BC6D141E', column 'string_2'. Truncated value: '!012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678'. appeared.

I was unable to create a repro without our tables. This is the closest as I can get to:

-- Create temporary table for results
DECLARE @results TABLE (
    string_1 nvarchar(100) NOT NULL,
    string_2 nvarchar(100) NOT NULL
);

CREATE TABLE #table (
    T_ID BIGINT NULL,
    T_STRING NVARCHAR(1000) NOT NULL
);

INSERT INTO #table VALUES
    (NULL, '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'),
    (NULL, '!0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789!');

WITH abc AS 
(
    SELECT
        '' AS STRING_1,
        t.T_STRING AS STRING_2
    FROM
        UT
        INNER JOIN UTT ON UTT.UT_ID = UT.UT_ID 
        INNER JOIN MV ON MV.UTT_ID = UTT.UTT_ID
        INNER JOIN OT ON OT.OT_ID = MV.OT_ID
        INNER JOIN #table AS T ON T.T_ID = OT.T_ID -- this will never get hit because T_ID of #table is NULL
)
INSERT INTO @results
SELECT STRING_1, STRING_2 FROM abc
ORDER BY LEN(STRING_2) DESC

DROP TABLE #table;

As you can see the join of #table cannot yield any results because all T_ID are NULL nevertheless I am getting the error mentioned above. The result set is empty.

That would be okay if a text with more than 100 characters would be in the result set but that is not the case because it is empty. If I remove the INSERT INTO @results and display the results it does not contain any text with more than 100 characters. The ORDER BY was only used to determine the faulty text value (with the original data).

When I use SELECT STRING_1, LEFT(STRING_2, 100) FROM abc it does work but it does not contain the text either that is meant to be truncated.

Therefore: What am I missing? Is it a bug of SQL Server?

CodePudding user response：

-- this will never get hit is a bad assumption. It is well known and documented that SQL Server may try to evaluate parts of your query before it's obvious that the result is impossible.

A much simpler repro (from

This shows that SQL Server expected to convert all of the values in t1 before the filter eliminated the longer ones. And it's very difficult to predict or control when SQL Server will process your query in an order you don't expect - you can try with query hints that attempt to either force order or to stay away from hash joins but those can cause other, more severe problems later.

The best fix is to size the temp table to match the source (in other words, make it large enough to fit any value from the source). The blog post and db<>fiddle explain some other ways to work around the issue, but declaring columns to be wide enough is the simplest and least intrusive.