Truncating Data in SQL Server and fixed in ANSI_WARNINGS

Here in SQL SERVER 2019, Microsoft has fixed one of our old age problem about displaying the column which is being truncated while inserting the data i.e. "String or binary data would be truncated" by the help of ANSI_WARNINGS

Let's check it with an example.

First, create a test table with a single column with datatype varchar(5).

1 2   3

USE Test_Data GO CREATE TABLE ProductTable (ProductName VARCHAR(5));

Next, try to insert data string which is longer than 5 characters.

1 2 3

INSERT INTO ProductTable (ProductName)   VALUES ('Samsung Mobile');   GO

When you run the above script it will give you the following error:

Msg 2628, Level 16, State 1, Line 1
String or binary data would be truncated in table ‘TestData.dbo.ProductTable’, column ‘ProductName’. Truncated value: ‘Samsu’.
The statement has been terminated.

This is because the string is larger than the maximum width the column can hold. Now let us run a simple select statement and see the data inside the table. You will find that your table is empty and there are no data in it.

While we were working with various options, we turned off the ANSI_WARNINGS for the session and when we ran the same command, the result was interesting. Let us try that out together.

Turn off the ANSI warnings.

1   2

SET ANSI_WARNINGS OFF GO

Next, run the following command one more time.

1 2 3

INSERT INTO ProductTable (ProductName)   VALUES ('Samsung Mobile');   GO

Now this time the query will work fine and there will be no error at all.