Ansi warning statement in SQL Server

The set ANSI WARNING Statement in SQL Server controls whether warnings and errors are generated for certain operations such as

Ø  Divided by zero error

Ø  Arithmetic overflow errors

Ø  String truncation during insert / update operation

Ø  Aggregate function warning (for example null values in SUM or AVG)

Syntex 

SET ANSI_WARNING ON/OFF
SET ANSI_WARNING ON: It enable ANSI WARNING ang it is a default and recommended.

SET ANSI_WARNING OFF : It disable the ANSI WARNINGS.

See the example

SET ANSI_WARNINGS ON  create table #t (n varchar(5))  insert into #t(n) values ('BageshKumarSingh');  select * from #t

Creating table and inserting a record. This will thrown an error because we are trying to insert the record which column length is more that which we have declared.

Lets see when we are SETTING OFF.

use Test_DB SET ANSI_WARNINGS OFF  create table #t (n varchar(5))  insert into #t(n) values ('BageshKumarSingh');  select * from #t

 Record inserted successfully.

It truncates the value and inserts this in to table.

See the message.

This is session level setting. We need to use try… catch to handle the error. Keep ANSI_WARNING ON for proper error handling.