Why am I getting "String or Binary Data would be Truncated Error" when there's no INSERT statement?

Question

I have this query:

USE [SomeDatabase];
GO

DECLARE @percentageValue decimal(15,4) = 1.50; 

SELECT a.ID, a.Amount, a.Status 
FROM [dbo].ATable as a          
INNER JOIN [dbo].BTable as b 
  ON a.LinkToB = b.ID
INNER JOIN [OtherDatabase].[dbo].CTable as value 
  ON value.[Key] = CONCAT(N'APrefixAboutThisLongThatsNecessaryBecauseDontAsk',b.AltID)
WHERE a.Status = N'SomeStatus'
AND a.Amount > (COALESCE(TRY_CONVERT(DECIMAL(15,2), value.Value), 0)*@percentageValue);
GO

(Actual column names redacted for confidentiality)

And I'm getting the traditional: "Msg 8152, Level 16, State 10, Line 3 String or binary data would be truncated." error. Google tells me that I'm trying to insert something into a column that's too small, which makes sense.

However, this isn't an INSERT operation (and this is literally all of the SQL for my query), so I can't for the life of me detect where the truncation is happening or why. I assume this is something that's in the bowels of Transact-SQL, but the weirdest issue is that I'm getting results from the query despite the error.

On request, here's the relevant parts of the table schema.

USE [SomeDatabase]

CREATE TABLE [dbo].[ATable](
    [ID] [uniqueidentifier] NOT NULL,
    [Amount] [decimal](15, 2) NOT NULL,
    [Status] [nvarchar](32) NOT NULL,
    [LinkToB] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))
GO

CREATE TABLE [dbo].[BTable](
    [ID] [uniqueidentifier] NOT NULL,
    [AltID]  [uniqueidentifier] NOT NULL
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))
GO

USE [OtherDatabase]
GO

CREATE TABLE [dbo].[CTable](
    [ID] [uniqueidentifier] NOT NULL,
    [Key] [nvarchar](100) NOT NULL,
    [Value] [nvarchar](max) NOT NULL)
CONSTRAINT [PK_TableC] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

Answer 1

Okay, this one turns out to be interesting but obvious once you know the secret.

As stated in the comments (and on the schema), value.Value is a nvarchar(max). It's the traditional Key-Value antipattern to store data that you don't want to store somewhere specific. Now, we're running try_convert(nvarchar(max)) on this column, but that's fine, because the

 ON Key = N'SomethingSpecific'

clause will mean that it only runs try_convert on the Key=N'SomethingSpecific' row, right?

Right?

Nope.

Depending on the data in the table, the execution plan can choose to try_convert every value in the column. And one of the rows in the nvarchar(max) is beyond the capacity of try_convert's parameter. Hence crashy. This also explains why I get all the results I expect back, it's evaluating those results and then coming to the crash on data rows I expected it to be ignoring.

Even better, unrelated data or structure changes can change the behaviour of the execution plan, so this bug can go "Heisenbug" really easily and vanish given more/less data, minor changes to the structure, and on one debugging pass, I'm pretty sure the whitespace.

So what's the simplest solution? Get the config value into a variable seperately to the try_convert, and use that instead.

And to anyone designing a database or writing database code:

• Do NOT manipulate data types inside an SQL where clause
• Do NOT use one column to store wildly different datatypes and write complex conversion routines to handle the issues
• DO use the correct datatype for your data and treat additional tables with the correct datatypes as good
• Remember that SQL functions often do INSERT-like operations under the hood