Feeds:
Posts
Comments

Archive for the ‘SQL Server 2019’ Category

‘Verbose Truncation Warnings’ is one of the greatest features launched in SQL Server 2019. I have been waiting for this feature for quiet a long time because troubleshooting was a nightmare in such cases. This feature can literally save a lot of time while importing, inserting & updating huge amount of data. Let me cut short the story.

Let me create a sample database and a table to demonstrate the issue in earlier version (Any older version than SQL Sever 2019) of SQL Server Step by Step.

Step 1 : 

Let’s create a database and set the compatibility to 140 (SQL Server 2017).

USE [master]
GO
--Create Sample Database
CREATE DATABASE [Sample DB 2017]
GO
ALTER DATABASE  [Sample DB 2017]
SET COMPATIBILITY_LEVEL = 140  -- SQL Server 2017
GO

Step 2 : 

Let’s create a sample table and insert few records.

USE [Sample DB 2017]
GO
CREATE TABLE [dbo].[tbl_Color](
	[Color ID] [int] IDENTITY(1,1) NOT NULL,
	[Color Name] [varchar](3) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tbl_Color]
           ([Color Name])
     VALUES
	       ('Red'),
	       ('Blue'),
	       ('Green')
GO
--OUTPUT

 

Verbose Truncation Warnings-1

This is general error message which shows that one or more than one Table’s column size is less than the actual data limit. In other words, the data size is exceeding the Tables’ column size. The issue is, it does not tell you which column and which data is exceeding the limit. Lets say if you are inserting or updating more than 1 million rows in a table which has more than 50 cloumns. Nightmare right ?

Now, you should not worry anymore because this issue has been fixed in SQL Server 2019. 

Let me demonstrate step by step. How it’s fixed in SQL Server 2019 and make it very simple for us.

Step 1 : 

Let’s create a database in SQL Server 2019 by default the compatibility is 150.

USE [master]
GO
--Create Sample Database
CREATE DATABASE [Sample DB 2019]
GO

Step 2 : 

Let’s create a sample table and insert few records

 
USE [Sample DB 2019]
GO
CREATE TABLE [dbo].[tbl_Color](
	[Color ID] [int] IDENTITY(1,1) NOT NULL,
	[Color Name] [varchar](3) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tbl_Color]
           ([Color Name])
     VALUES
		   ('Red'),
		   ('Blue'),
		   ('Green')
GO
--OUTPUT

Verbose Truncation Warnings-2

As we can see in the above output which is in SQL Server 2019, the error message is very precise and does not only show the database name, table name & column name but also shows which data is exceeding the limit. In the above case ‘Blue’ in Column ‘Color Name’ is exceeding the column size which is varchar(3).

I hope this feature will make your troubeshooting a lot more easier. Do let me know your feedback about this feature. 

Read Full Post »