ANSI DEFAULT statement in SQL Server

The Set ANSI DEFAULT statement in SQL Server is comprehensive setting that controls several ANSI DEFAULT standard behaviors related to data handling and error checking. It essentially sets multiple ANSI related option simultaneously.

Syntex

SET ANSI_DEFAULTS ON/OFF .

This is a server-side setting which can enable the behavior for all clients’ connection. The client typically requests the setting on connection or session initialization. User should not modify the server setting.

When we enable ANSI DEFAULT ON it set the following option ON.

Ø  ANSI NULL

Ø  ANSI NULL DFLT ON

Ø  ANSI PADDING

Ø  ANSI WARNING

Ø  CURSOR CLOSE ON COMMIT

Ø  IMPECIT TRANSACTION

Ø  QUOTE IDENTIFIER

See the below example

SET ANSI_DEFAULTS OFF  --ANSI null declare @a int =null; if (@a=null) select ' Null is equal to null' else select ' Null is not equal to null'

See once we set the ANSI DEFAULT  ON or OFF it will impact the above setting. In the above example without using the ASNI NULL we are getting the value.

Now we are setting it ON the same example.