Have you five minutes to learn about the ANSI_WARNINGS option? Yes i suppose!
Introduction
Some days ago we talked about the option XACT_ABORT.Seeing the great success today I will show you another option, the ANSI_WARNINGS!
Who knows exactly what ANSI_WARNINGS is for?
Well only five minutes of reading and you will know all about this option!
ANSI_WARNINGS option
The ANSI_WARNINGS option controls behavior of the SQL Server database engine for various error conditions.When the ANSI_WARNINGS option is set to ON, the engine follows the standard ISO behavior for this situations:
- Encountering a NULL value during an aggregation operation
- Encountering a Divide by Zero error
- String truncation
Example for the string truncation
Let's take a table name Table_A where the field CODICE is a varchar datatype with a length of 4 character:
If i execute the insert below where in the field codice i insert a value that have a length greater than 4 char i get an error:
Now we set the ANSI_WARNINGS to OFF and execute the same insert again.
What do we get?
In the the same way if ANSI_WARNINGS is OFF and also then SET ARITHABORT option is set to OFF no error will be returned dividing a number by 0
P.S. How to determine is the ANSI_WARNINGS is set to ON?
Try This:
DECLARE @options INT
SELECT @options = @@OPTIONS
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
That's all for today.
See you soon,
Luca
Luca Biondi @ SQLServerPerformance blog 2020!
Previous post: SQL Server 2019 CU2 and the breaking of the SQL Server Agent