New Truncation Error Message in SQL Server 2019

If you have been coding TSQL for a while you probable are familiar and frustrated with this error message:

Msg 8152, Level 16, State 30, Line 3
String or binary data would be truncated.
The statement has been terminated.

Not knowing what column and data value was truncated can be frustration! You will be happy to hear that SQL Server 2019 has improved this message to include the table and column name and the truncated value, provided you are running SQL Server 2019 CTP 2.1 and above.

Suppose you have a column named MyColumn, which is defined as a nvarchar(4), in a table name Demo.dbo.MyTable. If you were trying to insert the value “My Data” into that column, in a SQL Server 2019 table, then you would get the following error message:

Msg 2628, Level 16, State 1, Line 3
String or binary data would be truncated in table 'Demo.dbo.MyTable', column 'MyColumn'. Truncated value: 'My D'.

Now you can tell the table, column and the truncated value for the row that was truncated. SQL Server only displays the first row that was truncated, if you have multiple rows that would be truncated. You need to know that this new error message is only available in SQL Server 2019 CTP 2.1 and CTP 2.2 if you have turned on trace flag 460. Hopefully by the time SQL Server 2019 is fully baked you will no longer need to set this trace flag to receive the new truncation error message.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles