SQL SERVER – Truncating Data and ANSI_WARNINGS

Today’s blog subject of Truncating Data and ANSI_WARNINGS was accidentally discovered while working on the Comprehensive Database Performance Health Check. Let us talk about the topic.

SQL SERVER - Truncating Data and ANSI_WARNINGS TruncatingData-800x160

It is very much known that in SQL Server 2019, Microsoft has fixed one of our age-old request about displaying the column which is being truncated while inserting the data. You can read about them in the blog posts here:

Let us see the error and message here in this blog post one more time.

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

Solarwinds
USE TempDB
GO
CREATE TABLE TestTable (MyName VARCHAR(5));

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

INSERT INTO TestTable (MyName)
VALUES ('SQLAuthority');
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 ‘tempdb.dbo.TestTable’, column ‘MyName’. Truncated value: ‘SQLAu’.
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.

SET ANSI_WARNINGS OFF
GO

Next, run the following command one more time.

INSERT INTO TestTable (MyName)
VALUES ('SQLAuthority');
GO

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

SQL SERVER - Truncating Data and ANSI_WARNINGS ansioff

When you try to run the select on the table, you will notice that from our string the first characters are inserted into the table. This is because we turned off the warning and SQL Server, went ahead and inserted the string inside the table, however, as the string is longer what column could hold, it actually, inserted maximum data it could insert in the column. I hope you enjoyed learning about truncating data.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – SELECT INTO a Table Variable in T-SQL
Next Post
SQL SERVER – Count NULL Values From Column

Related Posts

Leave a Reply

Menu