Beware the impact of the ANSI_WARNINGS setting

Disabling the ANSI_WARNINGS setting in your code can compromise your data integrity.

As an example; say you are inserting an invoice number into a table and ANSI_WARNINGS are disabled.


You pat yourself on the back as the insert succeeded.

Can you spot the issue?


Ok you can take that pat back.

You should have ANSI_WARNINGS ON to ensure you get a warning and the operation doesn’t succeed.


One way to spot if an active connection has this setting disabled is to query the DMV sys.dm_exec_sessions. An example is shown below:

,CASE WHEN [ansi_warnings] = 1 THEN 'yes' ELSE 'no' END AS is_ansi_warnings_enabled
FROM sys.dm_exec_sessions
WHERE session_id > 50;

Time to investigate session 54!

Leave a Reply