Environmental Concerns

To finish this discussion of Transact -SQL programming, we'll briefly look at some of the environmental concerns that you need to be aware of in your programming ”for example, we'll discuss case sensitivity, which can greatly affect your applications. We'll also look at nullability issues and ANSI compatibility.

Case Sensitivity

Various options and settings affect the semantics of your Transact-SQL statements. You must be sure that your Transact-SQL code can work regardless of the setting, or you must control the environment so that you know what the setting is.

Case sensitivity is by far the most common environmental problem, and it is simple to avoid. You should seriously consider doing most of your development in a case-sensitive environment, even if you will deploy your application mostly in a case-insensitive environment. The reason is simple: nearly all operations that work in a case-sensitive environment will also work in a case-insensitive environment, but the converse is not true. For example, if we write the statement select * from authors in the pubs database of a case-sensitive environment, it will work equally well in a case-insensitive environment. On the other hand, the statement SELECT * FROM AUTHORS will work fine in a case-insensitive environment but will fail in a case-sensitive environment. The table in pubs is actually named authors , which is lowercase. The only statement that would work in a case-sensitive environment but fail in a case-insensitive environment is the declaration of an object name, a column name, or a variable name . For example, with the statement declare @myvar int , another statement declaring @MYVAR int would work fine in a case-sensitive environment because the two names are distinct, but it would fail in a case-insensitive environment because the names would be considered duplicates.

The easiest way to determine whether your environment is case sensitive is to perform a SELECT statement with a WHERE clause that compares a lowercase letter with its uppercase counterpart ”you wouldn't need to access a table to do this. The following simple SELECT statement returns 1 if the server is case sensitive and 0 if the server is case insensitive:

 SELECT CASE     WHEN ('A'='a') THEN 0     ELSE 1 END 

Case sensitivity is just one of the issues surrounding the character set used by SQL Server. The character set choice will affect both the rows selected and their ordering in the result set for a query such as this:

 SELECT au_lname, au_fname FROM authors     WHERE au_lname='Jos' ORDER BY au_lname, au_fname 

If you never use characters that are not in the standard ASCII character set, case sensitivity is really your primary issue. But if your data has special characters like the in this example, be sure that you understand character-set issues. (For more information, see Chapter 4.)

Nullability and ANSI Compliance Settings

To pass the NIST test suite for ANSI SQL-92 compliance, various options had to be enabled in SQL Server because of subtle differences in semantics between the traditional SQL Server behavior and what is mandated by ANSI. We already covered the majority of these issues in earlier chapters. To preserve backward compatibility, the prior behavior couldn't simply be changed. So options were added (or in a few cases, previous options were toggled on) to change the semantics to comply with the ANSI SQL requirements. These options are summarized below, along with the statement used to change the behavior:

• Disable SQL Server's = NULL extension ( SET ANSI_NULLS ON ).
• Automatically display a warning if a truncation would occur because the target column is too small to accept a value. By default, SQL Server truncates without any warning ( SET ANSI_WARNINGS ON ).
• Always right-pad char columns , and don't trim trailing blanks that were entered in varchar columns, as SQL Server would do by default ( SET ANSI_PADDING ON ).
• Make statements implicitly part of a transaction, requiring a subsequent COMMIT or ROLLBACK ( SET IMPLICIT TRANSACTIONS ON ).
• Terminate a query if an overflow or divide-by-zero error occurs ( SET ARITHABORT ON ). By default, SQL Server returns NULL for these operators, issues a warning message, and proceeds.
• Close any open cursors upon COMMIT of a transaction. By default, SQL Server keeps the cursor open so that it can be reused without incurring the overhead of reopening it ( SET CURSOR_CLOSE_ON_COMMIT ON ).
• Allow identifier names to include SQL Server keywords if the identifier is included in double quotation marks, which by default is not allowed. This causes single and double quotation marks to be treated differently ( SET QUOTED_IDENTIFIER ON ).
• By default, create as NOT NULL a column in a CREATE TABLE statement that is not specified as NULL or NOT NULL. SET ANSI_NULL_DFLT_ON ON toggles this so that the column can be created with NULL. (We recommend that you always specify NULL or NOT NULL so that this setting option is irrelevant.) The nullability of a column not explicitly declared is determined by the setting at the time the table was created, which could be different from the current setting.

All the previous options can be set individually, but you might want to avoid doing that because there are 256 (2 ⁸ ) permutations to consider. You might want to set a few of the options individually, such as SET ARITHABORT or SET ARITHIGNORE. But by and large, you should either leave them all at their default settings or change them as a group to the ANSI SQL-92 behavior. These options can be enabled as a group by setting SET ANSI_DEFAULTS ON.

The ability to set these options on a per-connection basis makes life interesting for you as a SQL Server application programmer. Your challenge is to recognize and deal with the fact that these settings will change the behavior of your code. Basically, that means you need to adopt some form of the following four strategies:

• The Optimistic Approach Hope that none of your users or the person doing database administration will change such a setting. Augment your optimism by educating users not to change these settings.
• The Flexible Approach Try to write all your procedures to accommodate all permutations of the settings of all the various options (usually not practical).
• The Hard-Line Approach Explicitly set your preferences at startup and periodically recheck them to determine that they have not been subsequently altered . Simply refuse to run if the settings are not exactly what you expect.
• The Clean Room Approach Have a "sealed" system that prevents anyone from having direct access to change such a setting.

Whichever of these approaches you take is your choice, but recognize that if you don't think about the issue at all, you have basically settled for the Optimistic Approach. This approach is certainly adequate for many applications for which it's pretty clear that the user community would have neither the desire nor the ability to make environmental changes. But if you're deploying an application and the machine running SQL Server will be accessed by applications that you don't control, it is probably an overly simplistic approach. Philosophically, the Flexible Approach is nice, but it probably isn't realistic unless the application is quite simple.

You can change the SQL Server default values for the server as a whole by using sp_configure 'user options' . A specific user connection can then further refine the environment by issuing one of the specific SET statements we just discussed. The system function @@OPTIONS can then be examined by any connection to see the current settings for that connection. The @@OPTIONS function and the value to be set using sp_configure 'user options' are a bit mask with the values depicted in Table 10-2.

Decimal Value	Hexadecimal Value	Option and Description
2	0x0002	IMPLICIT_TRANSACTIONS. Controls whether a transaction is started implicitly when a statement is executed.
4	0x0004	CURSOR_CLOSE_ON_COMMIT. Controls behavior of cursors once a commit has been performed.
8	0x0008	ANSI_WARNINGS. Controls truncation and NULL in aggregate warnings.
16	0x0010	ANSI_PADDING. Controls padding of variables .
32	0x0020	ANSI_NULLS. Controls NULL handling by using equality operators.
64	0x0040	ARITHABORT. Terminates a query when an overflow or divide-by-zero error occurs during query execution.
128	0x0080	ARITHIGNORE. Returns NULL when an overflow or divide-by-zero error occurs during a query.
256	0x0100	QUOTED_IDENTIFIER. Differentiates between single and double quotation marks when evaluating an expression, allowing object names to include characters that would otherwise not conform to naming rules or would collide with a reserved word or a keyword.
512	0x0200	NOCOUNT . Turns off the message returned at the end of each statement that states how many rows were affected by the statement.
1024	0x0400	ANSI_NULL_DFLT_ON. Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability will be defined to allow NULLs.
2048	0x0800	ANSI_NULL_DFLT_OFF. Alters the session's behavior to not use ANSI compatibility for nullability. New columns defined without explicit nullability will be defined to prohibit NULLS.

Table 10-2. Bit mask values for SQL Server options.

By default, none of these options is enabled for SQL Server itself. So in a brand-new installation, the run value for sp_configure 'user options' will be 0. A system administrator can set this so that all connections have the same initial default settings. If you query the value of @@OPTIONS from an application that has not modified the environment, the value will also be 0. However, be aware that many applications, or even the SQL Server ODBC or OLE DB drivers that the application uses, might have changed the environment. Note that this includes the SQL Server Query Analyzer, which uses ODBC. To change the default behavior, simply set the corresponding bit by doing a bitwise OR with the previous value. For example, suppose that your run value is 512, which indicates that NOCOUNT is the only option turned on. You want to leave NOCOUNT enabled, but you also want to enable option value 32, which controls how NULLs are handled when using equality comparisons. You'd simply pass the decimal value 544 (or 0x220) to sp_configure 'user options' , which is the result of doing a bitwise OR between the two options (for example, SELECT 32512).

You can examine current options that have been set using DBCC USER OPTIONS . The output is similar to the code below.

 Set Option    Value ----------    ---------- textsize      64512 language      us_english dateformat    mdy datefirst     7 arithabort    SET nocount       SET 

This DBCC command shows only options that have been set ”it doesn't show all the current settings for sp_configure 'user options' . But you can also decode your current connection settings pretty easily from @@OPTIONS using something like this:

 SELECT "IMPLICIT TRANSACTIONS", CASE WHEN     (@@OPTIONS & 0x0002 > 0) THEN 'ON' ELSE 'OFF' END UNION SELECT "CURSOR_CLOSE_ON_COMMIT", CASE WHEN     (@@OPTIONS & 0x0004 > 0) THEN 'ON' ELSE 'OFF' END UNION SELECT "ANSI_WARNINGS",CASE WHEN (@@OPTIONS & 0x0008 > 0) THEN     'ON' ELSE 'OFF' END UNION SELECT "ANSI_PADDINGS", CASE WHEN (@@OPTIONS & 0x0010 > 0) THEN      'ON' ELSE 'OFF' END UNION SELECT "ANSI_NULLS", CASE WHEN (@@OPTIONS & 0x0020 > 0) THEN 'ON'     ELSE 'OFF' END UNION SELECT "ARITHABORT", CASE WHEN (@@OPTIONS & 0x0040 > 0) THEN 'ON'      ELSE 'OFF' END UNION SELECT "ARITHIGNORE", CASE WHEN (@@OPTIONS & 0x0080 > 0)     THEN 'ON' ELSE 'OFF' END UNION SELECT "QUOTED_IDENTIFIER", CASE WHEN (@@OPTIONS & 0x0100 > 0)     THEN 'ON' ELSE 'OFF' END UNION SELECT "NOCOUNT", CASE WHEN (@@OPTIONS & 0x0200 > 0) THEN 'ON'     ELSE 'OFF' END UNION SELECT "ANSI_NULL_DFLT_ON", CASE WHEN (@@OPTIONS & 0x0400 > 0)     THEN 'ON' ELSE 'OFF' END UNION SELECT "ANSI_NULL_DFLT_OFF", CASE WHEN (@@OPTIONS & 0x0800 > 0)     THEN 'ON' ELSE 'OFF' END ORDER BY "OPTION" 

Here's the result:

 OPTION                   SETTING ----------------------   ------- ANSI_NULL_DFLT_OFF       OFF ANSI_NULL_DFLT_ON        OFF ANSI_NULLS               OFF ANSI_PADDINGS            OFF ANSI_WARNINGS            OFF ARITHABORT               OFF ARITHIGNORE              OFF CURSOR_CLOSE_ON_COMMIT   OFF IMPLICIT TRANSACTIONS    OFF NOCOUNT                  ON QUOTED_IDENTIFIER        OFF 

Unfortunately, running the previous SELECT statement will not give you a complete picture. As we saw in Chapter 5, many of the options, especially those involving NULL handling, can also be changed at the database level by using sp_dboption . To really get the full picture, you must examine @@OPTIONS for your current session in addition to sp_dboption for your current database.

Locale-Specific SET Options

Beware of the locale-specific SET options. SET DATEFORMAT and SET DATEFIRST change the recognized default date format. If DATEFORMAT is changed to dmy instead of the (U.S.) default mdy , a date such as '12/10/98' will be interpreted as October 12, 1998. A good strategy for dates is to always use the ISO format yyyymmdd , which is recognized no matter what the setting of DATEFORMAT is.

DATEFIRST affects what is considered the first day of the week. By default (in the United States), it has the value 7 (Sunday). Date functions that work with the day-of-week as a value between 1 and 7 will be affected by this setting. These day-of-week values can be confusing, since their numbering depends on the DATEFIRST value; but the values for DATEFIRST don't change. For example, as far as DATEFIRST is considered, Sunday's value is always 7. But having then designated Sunday (7) as DATEFIRST, if you executed the statement SELECT DATEPART(dw,GETDATE()) and your date falls on a Sunday, the statement will return 1. You just defined Sunday to be the first day of the week, so 1 is correct.