As of SQL 2019 CTP2.0 or SQL 2017 CU12 Microsoft has given us a long-awaited addition to the truncation error. Yay! But …

Yes, the new error is awesome! It gives us information about exactly what column isn’t big enough and what data is going to be truncated. And everyone’s been very excited about this. Myself included. On the other hand ..

You know what, here’s a quick demo.

Using Randolph West’s (b/t) post String or binary data would be truncated: get the full picture in SQL Server 2017 I was able to turn the error on on my SQL 2017 instance. (Although in this case I just used the trace flag on the INSERT statement. Did you know you could put a trace flag on just an insert statement? I didn’t. Pretty cool though.)

CREATE TABLE TruncationTest (SSN char(9));
GO
INSERT INTO TruncationTest VALUES ('123-45-6789')
OPTION(QUERYTRACEON 460);
GO

Msg 2628, Level 16, State 1, Line 7

String or binary data would be truncated in table ‘Test.dbo.TruncationTest’, column ‘SSN’. Truncated value: ‘123-45-67’.

Very cool! We now know that the error was on the SSN column of the table TruncationTest. Of course, we also know the first 9 characters (the actual length of the column) of what someone was putting in for their social security number. That’s the part that worries me. This is potentially a security hole. Probably easy enough to avoid, but then again, so is SQL Injection.

Basically, I’m just saying, I’d be very careful about what data is on the instance if you are going to use this feature. You know .. just in case.

Oh, and just FYI, Dynamic Data Masking doesn’t appear to affect the output. It makes sense since the information is not in the column yet. Given that I’m also guessing that any encryption on the column isn’t going to make a difference either.