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 = NULL
WHILE (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)
GO
SELECT * FROM pubs.dbo.authors
WHERE 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.
End of Article
Prev. page
1
[2]
next page -->
