We have examined some of the SET options of SQL Server in the previous posts. We saw how these SET options controls the behavior of SQL Server in different cases.

We saw that some of the settings can be done at the database level. When an option is configured at the database level, all queries that you run on that database will inherit the setting, unless you change the option explicitly.

However, be aware that your data access library or the tool that you use to run your queries (SSMS, Query Analyzer etc) might change the SET options while establishing a connection to the server. They might change the SET options for the current session and when you run your queries, you might find a different behavior than expected.

Let us look at an example to understand this.

In one of the previous posts, we examined SET ANSI_NULLS. When ANSI_NULLS is set to OFF, you can compare a value with NULL using comparison operators. Let me show an example to explain this.

-- example 1
SET ANSI_NULLS ON;
DECLARE @name VARCHAR(10)
SELECT @name = NULL

IF @name = NULL
    PRINT '@name is null'
    
    
-- example 2
SET ANSI_NULLS OFF;
DECLARE @name2 VARCHAR(10)
SELECT @name2 = NULL

IF @name2 = NULL
    PRINT '@name2 is null'    
/*
OUTPUT:
@name2 is null
*/

The first example does not print anything because while ANSI_NULLS is set to ON, you cannot compare a value against NULL (using the comparison operators). The second example sets ANSI_NULLS to OFF and the comparison succeeds.

It means that when ANSI_NULLS is OFF, you can compare a value against null using the comparison operator. Now, look at the next example:

-- SET ANSI_NULLS to OFF at database level
USE XMLWorkshop
ALTER DATABASE XMLWorkshop SET ANSI_NULLS OFF;
GO

-- check the ANSI_NULLS setting of current database
SELECT 
    is_ansi_nulls_on 
FROM sys.databases 
WHERE database_id = DB_ID()

/*
is_ansi_nulls_on
----------------
0
*/

-- Let us try to compare a value against NULL
DECLARE @name VARCHAR(10)
SELECT @name = NULL

IF @name = NULL
    PRINT '@name is null'
ELSE
    PRINT '@name IS NOT null'
/*
OUTPUT:
@name IS NOT null
*/

Note that the last query prints "@name is not null". This is not what we expected. Note that the DB level setting of ANSI_NULLS is OFF. When ANSI_NULLS is OFF, the comparison should succeed and the result should be "@name is null". So what went wrong?

ANSI_NULLS and many of the SET options of SQL server can be set at the DB level as well as session level. Session level settings will take precedence over DB level settings.

But in our case, we did not change any setting at the session level. Then why didn't our session inherit the DB level setting?

Well, we did not change the session level setting. But SQL Server Management Studio did. When it connects to SQL server, it changes the settings of the current session. In this case, SSMS changed the setting of ANSI_NULLS to ON, when connected.

What are the options SSMS sets automatically when connecting to the server?

You can see these options by selecting the "options" menu item from "Tools" menu.

Though it is not recommended that you change these settings, it will give us a good idea of what are the options that SSMS changes behind the screen. Just like SSMS, query tools like Query Analyzer or Enterprise manager are also setting some options while connecting to a server instance. Similarly, many of the Data Access libraries that you use in your application are also changing some options while connecting to the server.

Hence it is not a good practice to assume any specific setting while running your queries. If you want a specific setting, change it at the session level. For example, if you want your query to execute with ANSI_NULLS OFF, do not depend on the setting at the DB level. Instead check the value of the setting at SESSION level and if it is different from what you need, change it. Also, it is a good practice to change it back to original value when you are done with your query.

You can use the SESSIONPROPERTY() function to check the value of a specific option for the current session. Here is an example that shows how to do it.

SET ANSI_NULLS ON;
SELECT SESSIONPROPERTY('ANSI_NULLS') AS IsAnsiNullsOn
/*
IsAnsiNullsOn
-------------
1
*/
SET ANSI_NULLS OFF;
SELECT SESSIONPROPERTY('ANSI_NULLS') AS IsAnsiNullsOn
/*
IsAnsiNullsOn
-------------
0
*/

SEE ALSO

1. SQL Server SET Options