Home > SQL Server > Show or Hide SQL Warnings – SET ANSI_WARNINGS – SQL Server

Show or Hide SQL Warnings – SET ANSI_WARNINGS – SQL Server


ANSI_WARNINGS Specifies ISO standard behavior for errors in SQL Server. It helps to show and hide the error messages during query execution.

Usage

SET ANSI_WARNINGS ON
or
SET ANSI_WARNINGS OFF
 

If ANSI_WARNINGS is ON than warning will be raised when the SQL statements has problem while execution. such as, null value in aggregate function returns warning message. So, I have used aggregation function in the below given example to show how this set statement treats the error messages.

pic1If ANSI_WARNINGS is OFF then warnings will not be shown.

pic2If  ANSI_WARNINGS is set to OFF then the divide-by-zero and arithmetic overflow errors returns null values. Else, it rolled back the transaction and throws an error message.

SET ANSI_WARNINGS must be ON when we are creating or using indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

So, we should understand the use of this set statement and add this feature in our application properly.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: