Inside SQL Server: Controlling Query Behavior

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, priceFROM titlesWHERE 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, priceFROM titlesWHERE 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.

Another way these options differ from ANSI_NULLS is that a special function, GETANSINULL( ), can tell you which behavior is in effect. But don't let the name fool you: This function tells you not the ANSI_NULLS setting, but the default nullability of columns. GETANSINULL( ) returns a 1 if ANSI_NULL _DFLT_ON is set to ON or if ANSI null default is set to true and ANSI_NULL_DFLT_OFF is set to OFF.

Another SET option that deals with NULL behavior is new in SQL Server 7.0. Previous releases of SQL Server have no way to represent an empty (zero-length) string. Even if a string looks empty—for example, if you represent it as two single quotes with nothing in between—SQL Server 6.5 and earlier releases interpret it as a string of a single space. If your application dynamically builds a string by repeatedly adding information to it, you need to initialize the string. In SQL Server 6.5 and earlier, you can initialize the string to NULL. For concatenation, the code treats NULL as an empty string and doesn't add anything to the string you concatenate it to. Pseudo code would look something like this:

DECLARE @string_variable varchar(100)SELECT @string-variable = NULLWHILE (there are more parts of the string)   SELECT @string_variable = @string_variable + 'Next       part of string'

In SQL Server 7.0, you can have a truly empty string. You can then use this statement to initialize a string with the empty string:

ELECT @string-variable = ''

Because you can now use an empty string to initialize strings, you might not want concatenation of NULL to treat the NULL as an empty string. Any other operation with NULL yields a result of NULL. If you want consistent behavior with concatenation, you can control it with the database option concat null yields null. A corresponding SET option, CONCAT_NULL_ YIELDS_NULL, works like ANSI_NULLS: Setting the option explicitly to either ON or OFF overrides the database option. And as with ANSI_NULLS, the ODBC driver and Query Analyzer turn this SET option on by default.

Other Options

A final option changes the meaning of single and double quotes. By default, SQL Server doesn't let you create object names that are reserved keywords or that contain special characters, such as spaces or dashes. The session option QUOTED_IDENTIFIER lets you create and access special object names if you enclose them in double quotes. However, when you set this option on, you can't use double quotes to delimit strings. So, when the option is off, the first statement below fails and the second succeeds. When the option is on, the first statement succeeds and the second fails.

CREATE TABLE "my table" (col1 int)GOSELECT * FROM pubs.dbo.authorsWHERE state = "CA"GO

A corresponding database option called quoted identifier works the same as the ANSI nulls option. That is, after the SET option has ever been explicitly set to either ON or OFF within a session, quoted identifier overrides the database option.

Additional Concerns

As I mentioned, if you change the value of most SET options in a stored procedure, a connection reverts to its previous behavior when the stored procedure finishes executing. This rule has two exceptions for two options whose values are stored when you create the procedure. SQL Server stores the settings ANSI_NULLS and QUOTED_IDENTIFIER in the procedure's row in sysobjects in the status column. When the 0 X 8 bit is set, ANSI_NULLS is in effect for a procedure; when the 0 X 10 bit is set, QUOTED_IDENTIFIER is in effect for the procedure. Issuing a SET command to change either value within a procedure has no effect. The documentation for SQL Server indicates that the sysobjects.status column is for internal use only, which means you can't count on these bits to keep the same meaning across releases. So don't build any assumptions into your production code.

A Real Solution

So how can you write your applications if it's so hard to tell which SET options are in effect when your queries are executed? I suggest using SQL code that doesn't change its behavior based on one SET option or another. If you always use IS NULL in your comparisons against NULL, you don't need to know whether the option ANSI_NULLS is on. If you always explicitly declare every column in every table as allowing NULLs or not, it doesn't matter whether ANSI_NULL_DFLT_ON is on. And if you always delimit strings with single quotes and use square brackets to delimit identifiers—for example, CREATE TABLE [my table]—it doesn't matter whether QUOTED_IDENTIFIER is on.

The only real problem is the concatenation of nulls. There is no way to force a standard behavior in all sessions and in all databases. You won't find a standard behavior because SQL Server changed its behavior between SQL Server 6.0 and 7.0. As a result, you might have to change your application code when you move to SQL Server 7.0. As soon as possible, change any applications that initialize strings through NULL so that they initialize strings to an empty string. While you make the transition to SQL Server 7.0, you can use another special feature called the database compatibility level. Books Online (BOL) shows exactly how to set this value, but you can use the procedure sp_dbcmptlevel to let queries within a database retain some of the SQL Server 6.5 behavior. With a database at 6.5 compatibility level, concatenation of NULL always treats NULL as an empty string, regardless of the value of the SET option CONCAT_NULL_YIELDS_NULL or the database option concat null yields null.