SET and database options put the power to control queries in your hands
The Transact SQL (T-SQL) language provides options for controlling SQL Server's behavior on an individual-connection basis. You can specify many of these options with a SET command. A SET command changes behavior only for the connection where you issue the command. If you issue a SET command in a stored procedure, the command usually controls that procedure's behavior. The connection that called the procedure reverts to the previous behavior when the procedure finishes executing.
Many SET options have counterparts that you can enable for all connections in a database. Although the relationship between the SET commands and the corresponding database options is nonintuitive and poorly documented, you have some tools for reporting when an option is enabled.
Controlling NULL Behavior
The option ANSI_NULLS controls whether you can use an equality operator with NULL to mean the same thing as IS NULL. The ANSI SQL standard specifies that because NULL is never equal to anything, no rows ever satisfy a WHERE clause that uses an equality operator with NULL. For example, when ANSI_ NULLS is on, the following query in the Pubs database doesn't return any rows, even though two rows have NULL for price:
ELECT title_id, price
FROM titles
WHERE price = NULL
When ANSI_NULLS is off, the query returns two rows. When ANSI_NULLS is on, the only way to return those two rows is to use the query below:
ELECT title_id, price
FROM titles
WHERE price IS NULL
Of course, IS NULL always returns rows with NULL, no matter what the ANSI_NULLS setting is.
You can also enable a database option called ANSI nulls, which does the same thing at the database level that ANSI_NULLS does at the session level, by using the sp_dboption stored procedure. Because the SET option always overrides the database option, the database option applies only when ANSI_NULLS has never been set to either ON or OFF. You can think of ANSI_NULLS as having three possible values: ON, OFF, and NEVER SET. If the value is either ON or OFF, SQL Server ignores the database option ANSI nulls. The Query Analyzer automatically turns on the option ANSI_NULLS for each connection, so SQL Server ignores the database option even if you then set ANSI_NULLS to OFF.
You might try to change the Query Analyzer's behavior by selecting the New Connections tab under File, Configure. Clearing the box that says Use ANSI nulls, padding and warnings disables ANSI behavior, but you can still set the ANSI_NULLS option. If you use SQL Profiler to trace which commands the Query Analyzer sends to SQL Server, you'll see that initially the Query Analyzer sends a SET ANSI _NULLS ON command, followed by SET ANSI _NULLS OFF. So, SQL Server still ignores the database option.
To find out whether the ANSI behavior is in effect, you can run the command DBCC USEROPTIONS to see which SET options have been turned on. However, this command doesn't tell you which options have been explicitly turned off, so you can't tell whether the database option controls the behavior unless you run a query using '= NULL' in the WHERE clause and note the behavior.
Another behavior relating to NULLs that you can control by SET and database options is whether the default for a column in a new table allows NULLs. This behavior is different from ANSI_NULLS in that you can specify whether the SET option or the corresponding database option controls the behavior.
SQL Server's default behavior is to prohibit NULLs when you issue a CREATE TABLE statement without specifying whether a column allows NULLs. The ANSI standard says, however, that such columns should allow NULLs. To obtain the ANSI behavior for a particular connection, you can enable a SET option called ANSI_NULL_DFLT_ON. To obtain this behavior for all connections in a database, set the database option ANSI null default to true. These options differ from ANSI_NULLS in that a separate SET option turns the behavior off. If ANSI_NULL_DFLT_OFF is set to ON, the ANSI behavior isn't in effect. Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_ DFLT_ON can't be ON simultaneously. If one option is ON, the other option is set to OFF. If both options are OFF, SQL Server uses the value of the ANSI null default option of sp_dboption.
One way to determine the various options' behavior is to use a decision table, such as Table 1. The first two lines contain the values for the two SET options, and the third line tells which behavior will be in effect—i.e., whether columns allow NULL if CREATE TABLE doesn't specify the behavior.
Prev. page
[1]
2
next page 
