Whatever happens in terms of communication between an RDBMS server and a user accessing it happens in the context of a session. In a multiuser environment, one of the primary concerns is data integrity. When a client application establishes a connection to an RDBMS server, it is said that it opens a session. The session becomes this application's private communication channel. The user of the application may change some preferences within the session (for example, default language or default date format); these settings would affect only the session environment and remain valid only for the duration of the session. The details of the implementation and default behavior of the sessions might differ among the RDBMS, but these basic principles always remain the same.

By now, you ought to be acquainted with at least one of the tools provided by Oracle, IBM, or Microsoft to access their respective databases. Each RDBMS package is a resource intensive piece of software, and in general it is recommended not to install all of them onto the same machine. Once you've installed your RDBMS of choice, you could run multiple instances of Oracle's SQL Plus to access Oracle 9i RDBMS, Microsoft's OSQL (if you've selected MS SQL Server 2000), or IBM's Command Line Processor for IBM DB2 UDB from the same computer where your RDBMS is installed, and each instance will open its own session, which would be isolated from every other session established to the RDBMS server.

The SQL standard specifies a number of parameters that could be set in a session (listed in Table 7-1). None of these are implemented directly by the RDBMS, though some elements made it into proprietary syntax, ditching the letter, preserving the spirit.

Table 7-1: SQL Standard SET Statements

SQL Statement



If more than one connection is opened by a user to an RDBMS, this statement allows that user to switch between the connections.


This statement defines the default catalog for the session.


Changes the constraints mode between DEFERRED, and IMMEDIATE.


Stores values in the descriptor area.


Defines the default character set for the SQL statements.


Sets the schema to be used as a default qualifier for all unqualified objects.


Sets the authorization ID for the session, no other IDs can be used.


Sets the default time zone for the session.

In Oracle, a user must have a system privilege CREATE SESSION in order to establish a database connection. Initially, all the default parameter values for the session are loaded from a special Oracle configuration file; the file could be modified only by a database administrator, or someone who has the necessary privileges. Once the connection is established (a session is created), a user can alter the session according to his/her preferences and job requirements.


See Chapter 12 for more information on privileges.

The session parameters in Oracle can be modified using an ALTER SESSION statement. The syntax of the statement is relatively complicated and usually belongs to advanced database topics. Even the parameters that can be changed with this statement are somewhat irrelevant to SQL programming, like DB_BLOCK_CHECKING, HASH_JOIN_ENABLED, or MAX_DUMP_FILE_SIZE. These statements deal more with RDBMS administration and optimization, and belong to an advanced Oracle book.


We recommend Oracle Administration and Management by Michael Ault (Wiley, 2002).

Here we are going to demonstrate the concept of altering the session to suit your particular needs using one of the parameters NLS_DATE_FORMAT.

You can use this parameter to alter date format returned by your SQL query, as it specifies the default date format returned by the TO_CHAR and TO_DATE functions.


Read more about NLS_DATE_FORMAT in Chapter 10.

		nls_date FROM dual; NLS_DATE ----------------- 06 - 10 - 03

The format in which the output of the TO_CHAR function appears is determined by the initialization parameter NLS_DATE_FORMAT, which is the default for each new session. After the session is altered, the format of the displayed date is changed:

		---------------------------- 06-OCT-2003 10:33:44

The changes made with an ALTER SESSION statement are valid for the duration of the session. To make changes permanent, the ALTER SYSTEM statement should be used.


You may also control privileges afforded to the session by issuing a SET ROLE statement. Refer to Chapter 12 for more information.

IBM DB2 UDB provides surprisingly little control for the user over the session environment. It lists the keyword SESSION as reserved for future use, alongside with SESSION_USER.

The closest it comes to providing session control is with the SET PASSTHRU statement, which opens and closes a session for submitting SQL data directly to the database. Also, a global temporary table created during the session may be qualified with the SESSION component as a schema. (It is used to prevent ambiguity in accessing the table, when the temporary table name is the same as some persistent table, and in some other just as obscure cases.)

Microsoft SQL Server 2000 has a number of statements that you can specify to alter the current session (some of them are shown in Table 7-2 and Table 7-3). These statements are not part of SQL standard, being rather part of the Transact-SQL dialect. They can be grouped in several categories: statements that affect date and time settings, query execution statements, statistics statements, locking and transaction statements, SQL-92 settings statements, and — the all-time favorite — miscellaneous settings.

Table 7-2: Microsoft SQL Server 2000 SQL-92 Settings

SET Statement



Specifies that all the defaults used for the duration of the session should be these of ANSI defaults. This option is provided for compatibility with SQL Server 6.5 or later


Specifies whether columns could contain NULL value by default. If set to ON, the new columns created would allow NULL values (unless NOT NULL is specified); otherwise it would raise an error. It has no effect on the columns explicitly set for NULL. It is used to override default nullability of new columns when the ANSI null default option for the database is TRUE.


Essentially, the same as the statement above, with one exception: it is used to override default nullability of new columns when the ANSI null default option for the database is FALSE.


Specifies the SQL-92 compliant behavior when comparing values using operators EQUAL (=) and NOT EQUAL (< >).


Specifies how the values that are shorter than the column size for CHAR, VARCHAR, BINARY, and VARBINARY data types are displayed.


Specifies whether a warning should be issued when any of the following conditions occur: presence of NULL values in the columns evaluated in the aggregate functions (like SUM, AVG,COUNT, etc.); divide-by-zero and arithmetic overflow errors generate an error message and the statement rolls back when this option is set to ON; specifying OFF would cause a NULL value to be returned in the case.

Table 7-3: Microsoft SQL Server 2000 SET Statements

SET Statement


SET DATEFORMAT {<format> | @<format ID>}

Specifies the order of the date parts for DATETIME and SMALLDATETIME input.


Specifies what would be the result of concatenation of the column values (or expressions) should any or both of them contain NULL.

SET LANGUAGE { <language> | @<language ID>}

Specifies the default language for the session. This setting affects the datetime format, and system messages returned by SQL Server.


SQL Server usually returns a message indicating how many rows were affected by any given statement. Issuing this command would stop this message.


Specifies the severity of an error that results in loss of precision; if set to OFF the rounding generates no error; when it is set to ON, then an error will be generated and no results returned. Depending on some other settings, a NULL might be returned.

SET ROWCOUNT <integer>

If this statement is used, Microsoft SQL Server stops processing a query after the required number of rows (specified in the SET statement) is returned.

While detailed discussion of these settings and their implications are well beyond the scope of our SQL topic, nevertheless, we are going to discuss some of the most important statements and how they may affect your SQL statements executed against Microsoft SQL Server 2000.

Here is an example of how setting ANSI_NULLS affects the output in the current session. The SQL-92 standard mandates that the comparison operations involving NULL always evaluate to FALSE. The following statement is supposed to bring all the records from the PHONE table of the ACME database when the PHONE_SALESMANID_FN filed is not NULL.

		1> SELECT phone_phonenum_s 2> FROM phone 3> WHERE phone_salesmanid_fn
		<> NULL 4> GO PHONE_PHONENUM_S -------------------- (0 row(s)

The query returns zero records in spite of the fact that there are supposed to be 12 records satisfying this criterion. Setting the ANSI_NULLS OFF changes the situation (valid in Microsoft SQL Server only; neither Oracle nor IBM DB2 UDB supports this feature):

		1> SELECT phone_phonenum_s 2> FROM phone 3> WHERE phone_salesmanid_fn
		<> NULL 4> GO PHONE_PHONENUM_S -------------------- (305) 555-8502
		(626) 555-4435 (717) 555-5479 (718) 555-7879 (718) 555-5091 (814) 555-0324
		(305) 555-8501 (626) 555-4434 (717) 555-5478 (718) 555-7878 (718) 555-5091
		(814) 555-0323 (12 row(s) affected)

This situation could be completely avoided if the IS NULL syntax is used. The query

SELECT phone_phonenum_s FROM
		  phone WHERE phone_salesmanid_fn IS NULL

would return correct results in all three RDBMS. Since NULL is not a specific value, it has to be treated differently. Neither Oracle 9i nor IBM DB2 UDB have such a setting as ANSI_NULLS. Refer to Chapter 3 for more information about NULL.

It is possible to specify multiple options with ON or OFF settings, using one SET statement. For example, the following statement will set two options at the same time.

		ON 2> GO

To check the options set for your session, use the following statement. It returns all the active options that have been set for this particular session within which you execute this statement

		USEROPTIONS Set Option Value -------------------------- ---------------------
		textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier
		SET arithabort SET ansi_null_dflt_on SET ansi_defaults SET
		ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET
		(12 row(s) affected) DBCC execution completed. If DBCC printed error messages,
		contact your system administrator.

The DataBase Console Command (DBCC) package is a toolbox of all the DBA utilities, with some options accessible to a user. There are over 60 DBCC commands that handle various aspects of SQL Server configuration, administration, status checking, and so on.


If the SET statement is set in the stored procedure, it is valid within the session for the duration of the stored procedure execution, and reverts to its previous value once the execution stops. When using Dynamic SQL (see Chapter 15), the SET statement affects only the batch it is specified in; subsequent statements will not be affected by this setting.

Some other SET statements pertaining to transactions and locks will be discussed in the corresponding paragraphs of this chapter.

When a client terminates a session — either voluntarily or abnormally — all values set for various session parameters disappear. In addition, for all pending transactions, an implicit commit will be issued in the case of voluntary termination or rolled back when the session has terminated abnormally. The session can be killed or disconnected by a DBA; syntax for the statements vary among RDBMS.