[Updated on 10/25/2018]
With MSIgnite being this week tons of great new features are being announced. While many will focus on Spark integration, performance tuning, security, and containers sometimes it’s the simple things that give us the most value. I will talk about a feature that is hidden on page 17 in Microsoft’s SQL 2019 whitepaper. This feature is not getting a lot of love now but everyone will love it. I will be honest, I didn’t even know about it until reading the white paper. What is really super cool is that this new feature will find its way into SQL Server 2017 and SQL Server 2016. More on this in a bit.
In the past, I have struggled with the classic string truncation error. Everyone has at one point in time or another. It’s tricky to troubleshoot especially when you are processing a big batch of data.
String or binary data would be truncated
So even in SQL 2019, SQL Server 2017 CU12, and SQL Server 2016 (upcoming SQL Server 2016 SP2 CU) by default, you will see the very classic “string or binary data would be truncated. The statement has been terminated.” message. Developers, DBA’s, IT Professionals, and almost everyone who has ever touched SQL Server have seen this frustrating error at least once. It is very frustrating for two separate reasons. One, you have no idea which column had the truncation. Second, you have no idea which row also had the error.
Let’s take a look at a very simple example.
use [tempdb]
go
if exists (select 1 from sys.tables where name like 'WorldSeries')
drop table dbo.WorldSeries
GO
CREATE TABLE dbo.WorldSeries (TeamName varchar(200), Championships varchar(3))
GO
INSERT INTO dbo.WorldSeries (TeamName, Championships) VALUES ('Texas Rangers', null)
INSERT INTO dbo.WorldSeries (TeamName, Championships) VALUES ('Houston Astros', 'one'),
('Atlanta Braves', 'two'), ('Pittsburgh Pirates', 'five')
GO
You will see that our first statement on inserting the Texas Rangers completes successfully even though the Rangers have zero (I like NULL) championships. We have an error in the next batch of doing three inserts in a single query so the whole transaction will rollback. In this example, we could figure out where our error is but let’s pretend we are doing a massive data load into several columns with billions of records. Actually, I don’t even want to think about troubleshooting that and with this new hot feature in SQL Server 2019, you won’t have too either.
SQL Server 2019 Feature Everyone Will Love
Okay, I think you know where this is going… Let’s not prolong this any longer. We will rerun the last statement with just adding trace flag 460.
DBCC traceon(460)
INSERT INTO dbo.WorldSeries (TeamName, Championships) VALUES ('Houston Astros', 'one'),
('Atlanta Braves', 'two'), ('Pittsburgh Pirates', 'five')
GO
Now it is crystal clear. The Pittsburgh Pirates have won too many championships to fit into this table. Okay, I guess we can both agree the string “five” is too big to fit into a varchar(3) column.
John’s Thoughts and Recommendation
All you have to do in SQL Server 2019 CTP 2.0 (Current newest public release) is enable trace flag 460. I hope, the need for trace flag 460 goes away when SQL Server 2019 is Generally Available. That said, enabling the trace flag is easy and this feature is going to be a lifesaver especially for people consuming data from other sources and struggle troubleshooting data that doesn’t fit into their data model due to the length of a value.
I am sure I am not the only person who provided feedback on this new feature to Microsoft. That said, I am really excited to see Pedro’s announcement that the trace flag 460 shouldn’t be required when SQL Server 2019 is Generally Available (GA). The new error message (message 2628) will replace the old error message (message 8152). I am also excited that you will be able to utilize this new feature in SQL Server 2016 and 2017. It makes perfect sense for backward compatibility that you will need to provide trace flag 460 if you are using SQL Server 2017 CU 12 or SQL Server 2016. Way to go Pedro and the SQL Server Team. This is amazing news!
use [tempdb]
go
if exists (select 1 from sys.tables where name like 'WorldSeries')
drop table dbo.WorldSeries
GO
CREATE TABLE dbo.WorldSeries (TeamName varchar(200), Championships varchar(3))
GO
INSERT INTO dbo.WorldSeries (TeamName, Championships) VALUES ('Texas Rangers', null)
INSERT INTO dbo.WorldSeries (TeamName, Championships) VALUES ('Houston Astros', 'one'),
('Atlanta Braves', 'two'), ('Pittsburgh Pirates', 'five')
GO
