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.
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:
- SQL SERVER – Identify the column(s) responsible for “String or binary data would be truncated.”
- SQL SERVER 2019 – Still Getting String or Binary Data Would be Truncated
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).
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.
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)
