Home > Articles > Data > SQL Server

SQL Building Blocks and Server Settings

Aug 6, 2004

📄 Contents

␡

⎙ Print

< Back Page 8 of 8

This chapter is from the book 

Transact-SQL Desk Reference: For Microsoft SQL Server

Learn More Buy

2.8 SERVER, DATABASE AND SESSION SETTINGS

Server configuration settings, database configuration settings and session (or connection) property settings in some cases interact and in some cases are disjointed. Because some settings interact, I have found it less confusing to consider them all together.

2.8.1 Settings Overview

Most people can live a long and happy life without delving into the morass of these settings. Microsoft has done an excellent job of designing the database engine to set appropriate default values and of self-tuning to keep performance at a peak for most applications. Nonetheless, I think they need to clean up the interfaces for setting and reading the settings (see Author's Opinion below).

Generally speaking, server settings are of interest mainly to database administrators and most should be used only by experienced users and then on a test machine first. I'm not likely to change the number of "max worker threads" or "nested triggers," but if you have a reason and know what you're doing, we'll show you how. The rest of us will defer. ;-)

For database settings, one is likely to occasionally need to a change a database from MULTI_USER to RESTRICTED_USER in order to do maintenance. One may also want to set a specific database to READ_ONLY if its use does not require making data changes.Most database settings are best left as the default unless there is a specific reason to do otherwise.

Most session (connection) settings are also best left as the default values unless one has a specific reason to make a change. Exceptions include the occasional guidance given for using OLE DB or ODBC library call to set a specific option to a certain value. In these cases, I just follow the guidance without asking questions.

Session settings that can be quite useful for debugging or performance testing include NOEXEC, NOCOUNT, PARSEONLY, SHOWPLAN_xxx, STATISTICS xxx, etc. These options are well worth studying and testing to see the information they provide.

Having an understanding of the differences between server, database and session configuration settings will facilitate your programming. The major differences are listed below.

2.8.1.1 Server Configuration

Server settings affect server-wide settings and some database settings. Methods to see and assign server settings are (see details page 178):

sp_configure system stored procedure (sets all options)
Enterprise Manager (sets only the most commonly used options)

2.8.1.2 Database Configuration

Database settings affect database and default settings for connections to the database. Methods to see and assign database settings are (see details page 187):

ALTER DATABASE with a SET clause — Set all db settings. See page 188.
DATABASEPROPERTYEX( 'dbname' , 'propertykeyword' ) — Read db settings. See page 195.
Enterprise Manager — Set primary settings only
EXEC sp_dboption — obsolete (may not be in new versions). Use ALTER DATABASE.

2.8.1.3 Session (Connection) Configuration

New sessions inherit server and database settings and the user may change some. Methods to see and assign session settings are (see details page 202):

SET — Set all session options. See page 204.
SELECT @@OPTIONS — Read all session options. See page 208.
DBCC USEROPTIONS — Read all session options. See page 210.
SELECT SESSIONPROPERTY ( 'option' ) — Read all session options. See page 211.

Session settings for Query Analyzer can also be read and set from its "Query" menu: "Query — Current Connection Properties."

Author's Opinion

The subject of Server, Database and Session settings on SQL Server is overly confusing and needs cleanup work by Microsoft to make it easier to understand and manage. For example, why must one use ALTER DATABASE pubs SET READ_ONLY, or READ_WRITE to change whether a database is updateable, but have to use SELECT DATABASEPROPERTYEX ( 'pubs' , 'Updateability' ) to read the current setting?

And notice that pubs in the first statement must have no quotation marks and in the second statement it must have them. The now-out-of-favor sp_dboption at least had a very consistent interface for changing and reading settings.

Utilities to read current session settings also need cleanup work. @@OPTION is relatively complete but a bit awkward to use. SESSIONPROPERTY uses consistent keywords with SET, but only covers seven of them. DBCC USEROPTIONS only shows the ON settings, which is fine, but it doesn't report on all of the SET options. Oh, well!

2.8.1.4 Server Configuration Settings

Figure 2-3 shows a brief summary of the settings for server configuration. The details of these settings are in the sections that follow.

Figure 2-3. Summary of Server Configuration Statements

2.8.1.5 sp_configure

Use sp_configure to display or change global configuration settings for the current server. Table 2-76 summarizes the accessible settings.

Syntax

sp_configure [ [ @configname = ] 'name'      [ , [ @configvalue = ] 'value' ] ]

sp_configure may be executed with 0, 1 or 2 arguments:

0 arguments: Lists all configuration setting names addressable with sp_configure
1 argument: Displays the current setting for the configuration name specified
2 arguments: Sets the specified configuration name to the specified value

Table 2-76. Server Configuration Settings Accessible with sp_configure

sp_configure Configuration Option	Minimum	Maximum	Default	Requires 'show advanced options'	Requires Server Stop and Restart
affinity mask	0	2147483647	0	Yes	Yes
allow updates	0	1	0
awe enabled	0	1	0	Yes	Yes
c2 audit mode	0	1	0	Yes	Yes start audit, No stop audit
cost threshold for parallelism	0	32767	5	Yes
cursor threshold	1	2147483647	–1	Yes
default full-text language	0	2147483647	1033	Yes
default language	0	9999	0
fill factor (%)	0	100	0	Yes	Yes
index create memory (KB)	704	2147483647	0	Yes
lightweight pooling	0	1	0	Yes	Yes
locks	5000	2147483647	0	Yes	Yes
max degree of parallelism	0	32	0	Yes
max server memory (MB)	4	2147483647	2147483647	Yes
max text repl size (B)	0	2147483647	65536
max worker threads	32	32767	255	Yes
media retention	0	365	0	Yes	Yes
min memory per query (KB)	512	2147483647	1024	Yes
min server memory (MB)	0	2147483647	0	Yes
nested triggers	0	1	1
network packet size (B)	512	65536	4096	Yes
open objects	0	2147483647	0	Yes	Yes
priority boost	0	1	0	Yes	Yes
query governor cost limit	0	2147483647	0	Yes
query wait (s)	–1	2147483647	–1	Yes
recovery interval (min)	0	32767	0	Yes
remote access	0	1	1		Yes
remote login timeout (s)	0	2147483647	20
remote proc trans	0	1	0
remote query timeout (s)	0	2147483647	600
scan for startup procs	0	1	0	Yes	Yes
set working set size	0	1	0	Yes	Yes
show advanced options	0	1	0
two digit year cutoff	1753	9999	2049	Yes
user connections	0	32767	0	Yes	Yes
user options (See page 202)	0	32767	0

To see the listing and current settings, execute sp_configure with no arguments. By default only a partial listing is given unless show advanced options is enabled.

SQL
EXEC sp_configure -- Lists common configuration options. Enable 'advanced options' to see all.

Result
name minimum maximum config_value run_value --------------- ----------- --------------- ------------- ----------------------- allow updates 0 1 0 0 default language 0 9999 0 0 max text repl size (B) 0 2147483647 65536 65536 nested triggers 0 1 1 1 remote access 0 1 1 1 remote login timeout (s) 0 2147483647 20 20 remote proc trans 0 1 0 remote query timeout (s) 0 2147483647 600 600 show advanced options 0 1 0 0 user options 0 32767 0 0

For a description of each item see Books Online: Setting Configuration Options.

2.8.1.6 `sp_configure` SHOW ADVANCED OPTIONS

To see all sp_configure options, not just the basic ones, enable show advanced options as shown here.

Example

SQL
EXEC sp_configure 'show advanced options' , 1 RECONFIGURE -- Must run this to make the change effective.

2.8.1.7 `sp_configure` USER OPTIONS

The sp_configure USER OPTIONS value is a single integer which is a bitset specifying global defaults for 15 settings that affect each user's session (connection). A user may override each setting using the SET statement.

See discussion and examples of sp_configure user options on page 202.

Example:

SQL
EXEC sp_configure -- Now lists ALL 36 configuration options.
Result
name minimum maximum config_value run_value ------------------------------ ----- ----------- ----------- ------------ ----------- affinity mask 0 2147483647 1 1 ... user options 0 32767 0 0

2.8.1.8 When Do `sp_configure` Changes Become Effective?

Here is the short answer to this question: They become effective when run_value matches config_value, which depends on the option.

All sp_configure changes need RECONFIGURE to be run to become effective.

- Two options ( 'allow updates' and 'recovery interval' ) sometimes require RECONFIGURE WITH OVERRIDE to be run (see RECONFIGURE below).
Some options also require server stop and restart as indicated in Table 2-76. The following do not need server stop and restart.

allow updates cost threshold for parallelism cursor threshold index create memory (KB) max degree of parallelism max server memory (MB) max text repl size (B) max worker threads	min memory per query (KB) min server memory (MB) network packet size (B) query governor cost limit query wait (s) recovery interval (min) remote login timeout (s)	remote proc trans remote query timeout (s) show advanced options user options default full-text language? default language? nested triggers? two digit year cutoff?

allow updates cost threshold for parallelism

cursor threshold index create memory (KB)

max degree of parallelism

max server memory (MB)

max text repl size (B)

max worker threads

min memory per query (KB)

min server memory (MB)

network packet size (B)

query governor cost limit query wait (s)

recovery interval (min)

remote login timeout (s)

remote proc trans remote query timeout (s)

show advanced options user options

default full-text language?

default language?

nested triggers?

two digit year cutoff?

When using sp_configure, you must always run either RECONFIGURE (or RECONFIGURE WITH OVERRIDE for the two indicated above) after setting a configuration option.

Example 1: The allow updates option requires RECONFIGURE WITH OVERRIDE.

SQL
EXEC sp_configure 'allow updates'

Result
name minimum maximum config_value run_value ---------------- ------------ ------- ---- ------- -------- --- -------- allow updates 0 1 0 0

SQL
EXEC sp_configure 'allow updates' , 1 EXEC sp_configure 'allow updates'

Result
name minimum maximum config_value run_value ---------------- ------------ ------- ---- ------- -------- --- -------- allow updates 0 1 1 0

SQL
RECONFIGURE WITH OVERRIDE EXEC sp_configure 'allow updates'

Result
name minimum maximum config_value run_value ---------------- ------------ ------- ---- ------- -------- --- -------- allow updates 0 1 1 1

Example 2: The 'fill factor' option also requires server stop and restart

SQL
EXEC sp_configure 'fill factor'

Result
name minimum maximum config_value run_value ---------------- ---------- ------------ --------------- ---------- fill factor (%) 0 100 0 0

SQL
EXEC sp_configure 'fill factor' , 80 EXEC sp_configure 'fill factor'

Result
name minimum maximum config_value run_value ---------------- ---------- ------------ --------------- ----------- fill factor (%) 0 100 80 0

Option fill factor requires reconfigure then server stop and restart so the config_value is shown as changed but not the run_value.

SQL
reconfigure EXEC sp_configure 'fill factor'

Result
name minimum maximum config_value run_value ---------------- ---------- ------------ --------------- ----------- fill factor (%) 0 100 80 0

Still no change until we stop and restart the server, which we do now.

SQL
EXEC sp_configure 'fill factor' -- After server stop and restart

Result
name minimum maximum config_value run_value ---------------- ---------- ------------ --------------- ----------- fill factor (%) 0 100 80 80

2.8.1.9 RECONFIGURE

The reconfigure command updates the currently configured value of a configuration option changed with the sp_configure system stored procedure (the config_value column in the sp_configure result set). Some configuration options require a server stop and restart to update the currently running value. Therefore, RECONFIGURE does not always update the currently running value (the run_value column in the sp_configure result set) for a changed configuration value.

Syntax

RECONFIGURE [ WITH OVERRIDE ]

2.8.1.10 RECONFIGURE—Without the WITH OVERRIDE Option

The reconfigure command without the override option specifies that, if the configuration setting does not require a server stop and restart, the currently running value should be updated. Afterward the config_value and run_value should be the same for those options not requiring server stop and restart.

RECONFIGURE also checks the new configuration value for either invalid values or nonrecommended values.

2.8.1.11 RECONFIGURE WITH OVERRIDE

Without OVERRIDE, RECONFIGURE is for allow updates and recovery interval only. This allows invalid or nonrecommended values to be to be set for:

allow updates— default of 0 does not allow updates to system tables using DML (INSERT, UPDATE, DELETE). System procedures must be used. Setting to 1 is not recommended and requires WITH OVERRIDE.

recovery interval— default is 0 (self-configuring), recommended is 0 or 1 to 60. The value is the maximum number of minutes to recover each database. Over 60 minutes is not recommended and requires WITH OVERRIDE.

Books Online says the following.

Keep recovery interval set at 0 (self-configuring) unless you notice that checkpoints are impairing performance because they are occurring too frequently. If this is the case, try increasing the value in small increments.⁵

Example:

SQL
EXEC sp_configure 'recovery interval'

Result
name minimum maximum config_value run_value ------------------- ----------- ----------- ------------ ---------- recovery interval (min) 0 32767 0 0

SQL
EXEC sp_configure 'recovery interval' , 120 -- 120 minutes = 2 hours

Result
DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'recovery interval (min)' changed from 0 to 120. Run the RECONFIGURE statement to install.

SQL
EXEC sp_configure 'recovery interval'

Result
name minimum maximum config_value run_value ------------------- ----------- ----------- ------------ ---------- recovery interval (min) 0 32767 0 0

SQL
PRINT 'RECONFIGURE' RECONFIGURE

Result
RECONFIGURE Server: Msg 5807, Level 16, State 1, Line 2 Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.

SQL
EXEC sp_configure 'recovery interval' -- No change, RECONFIGURE is not strong enough

Result
name minimum maximum config_value run_value --------------------- ----------- ----------- ------------ ---------- recovery interval (min) 0 32767 120 0

SQL
PRINT 'RECONFIGURE WITH OVERRIDE' RECONFIGURE WITH OVERRIDE

Result
RECONFIGURE WITH OVERRIDE

SQL
EXEC sp_configure 'recovery interval'

Result
name minimum maximum config_value run_value --------------------------- ----------- ----------- ------------ ---------- recovery interval (min) 0 32767 120 120

2.8.1.12 SQL Server Settings in Enterprise Manager

Primary configuration settings for SQL Server are accessible in Enterprise Manager from the server properties dialog for a selected server as shown in the figure. In EM, right click on the server name and select "Properties" (see Figure 2-4).

Figure 2-4. The Server Properties Dialog box for the AMY server.

Examine the settings available on these tabs and see Books Online for further details.

2.8.2 Database Configuration (Database Properties)

Most database settings are best left as the default unless there is a specific reason to change. Database settings that you may need to a change occasionally are MULTI_USER and RESTRICTED_USER or SINGLE_USER in order to do maintenance. One may also want to set a specific database to READ_ONLY if its users do not need to change the data. A summary of database configuration statements is given in Figure 2-5. For more detail see Books Online, Index: database options.

Figure 2-5. Summary of Database Configuration Statements.

2.8.2.1 ALTER DATABASE dbname SET option

Only ALTER DATABASE with the SET clause, which may be used to change database settings, will be discussed in this section. See page 250 for the main coverage of ALTER DATABASE. See Table 2-77 for a summary of database configuration option keywords.

SQL Server 2K database options are set using ALTER DATABASE with a SET clause. In previous versions of SQL Server, database options were set with the sp_dboption system stored procedure. SQL Server 2K continues to support sp_dboption, which has been rewritten to call ALTER DATABASE, but it may not do so in the future.

DATABASEPROPERTYEX() may be used to show current settings for database options.

Partial Syntax

ALTER DATABASE  databasename  SET <optionspec>  [WITH <termination>]

< optionspec >   See table

< termination > ::=
            ROLLBACK AFTER integer [ SECONDS ]
            | ROLLBACK IMMEDIATE
            | NO_WAIT

Table 2-77. ALTER Database Configuration Option Keywords

<optionspec> Keyword for both ALTER DATABASE and SET	Has Session Setting	Database Default	Description (DATABASEPROPERTYEX keyword)
Database State Options			See also Books Online: DATABASEPROPERTYEX and Setting Database Options
SINGLE_USER \| RESTRICTED_USER \| MULTI_USER	No	MULTI_ USER	Determines who may connect to the specified database. Example below. (UserAccess)
OFFLINE \| ONLINE	No	ONLINE	When put OFFLINE the database is shutdown and can not be accessed. (Status)
READ_ONLY \| READ_WRITE	No	READ_ WRITE	When put READ_ONLY users can not modify the database. (Updateability)
Cursor Options
CURSOR_CLOSE_ON_COMMIT { ON \| OFF }	Yes	OFF	ON: (SQL-92) All open cursors are closed when a transaction is committed. OFF: Cursors must be closed explicitly and may cross transaction boundaries. (IsCloseCursorsOnCommitEnabled)
CURSOR_DEFAULT { LOCAL \| GLOBAL }	No	GLOBAL	GLOBAL— cursors default to GLOBAL LOCAL— cursors default to LOCAL. Cursors may always be explicitly defined as LOCAL or GLOBAL. See Cursors page 638. (IsLocalCursorsDefault)
Automatic Options
AUTO_CLOSE { ON \| OFF } Comment from Books Online The AUTO_CLOSE option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users. The AUTO_CLOSE option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.	No	ON for SS 2000 Desktop Engine (MSDE 2000) OFF for all other SS 2000 editions	ON: the database is closed and shut down cleanly when the last user of the database exits and when all processes in the database are complete, thereby freeing any resources. The database reopens automatically when a user tries to use the database again. OFF: the database remains open even if no users are currently using it. (IsAutoClose)
AUTO_CREATE_STATISTICS { ON \| OFF }	No	ON	ON: statistics are automatically created on columns without an index used in a predicate so as to speed the query. OFF: statistics not automatically created; but they can be manually created. (IsAutoCreateStatistics)
AUTO_UPDATE_STATISTICS { ON \| OFF }	No	ON	ON: existing statistics are automatically updated when they become out-of-date. OFF: statistics are not automatically updated but can be manually updated. (IsAutoUpdateStatistics)
AUTO_SHRINK { ON \| OFF } Default: ON for SS 2000 Desktop Engine (MSDE 2000) OFF for all other SS 2000 editions	No	See first column	ON: the database data and log files are periodically checked for unused space. OFF: files are not periodically checked for unused space. It is not possible to shrink a read-only database. (IsAutoShrink)
ANSI SQL-92 Compliance Options
ANSI_NULL_DEFAULT { ON \| OFF} This keyword applies to ALTER DATABASE only. The corresponding session SET keywords are ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF	No	OFF but effectively ON	Sets Default Nullability of a column— If ON is specified, CREATE TABLE follows SQL-92 rules to determine whether a column allows null values. OLE DB and ODBC set this to ON. (IsAnsiNullDefault)
ANSI_NULLS { ON \| OFF }	Yes	OFF but effectively ON	ON: SQL-92 behavior, comparing to NULL with = and <> returns NULL. OFF: NULL = NULL returns TRUE. (IsAnsiNullsEnabled)
ANSI_PADDING { ON \| OFF }	Yes	OFF but effectively ON	ON: Does NOT trim explicit trailing blanks in varchar and trailing zeros in varbinary columns. OFF: Does trim them. Books Online Recommendation: Leave t ON. (IsAnsiPaddingEnabled)
ANSI_WARNINGS { ON \| OFF }	Yes	OFF but effectively ON	ON means SQL-92 standard behavior of raising error messages or warnings for conditions like divide-by-zero and arithmetic overflow. (IsAnsiWarningsEnabled)
ARITHABORT { ON \| OFF } Default: Query Analyzer sets ARITHABORT to ON for each session	Yes	OFF	ON: Terminates a query if overflow or divide-by-zero occurs during query. OFF: Warning message displayed and processing continues. (IsArithmeticAbortEnabled)
Miscellaneous SET Options
CONCAT_NULL_YIELDS_NULL	Yes	OFF but effectively ON	ON: Concatenating NULL yields NULL (ON) versus empty string (OFF) (IsNullConcat)
NUMERIC_ROUNDABORT { ON \| OFF }	Yes	OFF	ON: an error is generated when loss of precision occurs in an expression. OFF: the result is rounded to the precision of the destination with no error. (IsNumericRoundAbortEnabled)
QUOTED_IDENTIFIER { ON \| OFF }	Yes	OFF but effectively ON	See QUOTED_IDENTIFIER discussion with examples page 44. (IsQuotedIdentifiersEnabled)
RECURSIVE_TRIGGERS { ON \| OFF }	No	OFF	ON allows triggers to fire recursively. (IsRecursiveTriggersEnabled)
Recovery Mode Statements
RECOVERY { FULL \| BULK_LOGGED \| SIMPLE }	No	FULL -- except MSDE 2000 is SIMPLE	See Recovery Models page 559. (Recovery)
TORN_PAGE_DETECTION { ON \| OFF } A torn page occurs when not all 16 sectors (512 bytes) of the 8 KB database page can be written to disk, as in power loss.	No	ON	ON causes the database to be marked as suspect if a torn page is found during recovery. If a torn page is found the database should be restored. This option should be left ON. (IsTornPageDetectionEnabled)

2.8.2.2 Examples—ALTER DATABASE to Change UserAccess of a Database

Database Access Modes determines who may connect to the specified database as follows.

MULTI_USER: Allows all users with database access privilege to connect
RESTRICTED_USER: Allows only members of db_owner, dbcreator and sysadmin
SINGLE_USER: Allows only the user issuing the ALTER DATABASE statement

Examples

You may read current access mode of the pubs database as shown.

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'UserAccess' )

Result
--------------------------- MULTI_USER

Now set the access mode to any of the three values using ALTER DATABASE.

SQL	ALTER DATABASE pubs SET MULTI_USER

Setting to Either RESTRICTED_USER or SINGLE_USER Database Access

The following form waits indefinitely if unqualified users are connected to the database.

SQL	ALTER DATABASE pubs SET SINGLE_USER -- may wait indefinitely

WITH NO_WAIT causes the ALTER DATABASE to fail immediately if unqualified users are connected to the specified database.

SQL
ALTER DATABASE pubs SET RESTRICTED_USER WITH NO_WAIT ALTER DATABASE pubs SET SINGLE_USER WITH NO_WAIT

This command returns immediately. The new access can be seen with the following.

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'UserAccess' )

Result
--------------------------- SINGLE_USER

WITH ROLLBACK IMMEDIATE forces immediate rollback of open transactions and terminates the connections of all unqualified users of the database.

SQL
ALTER DATABASE pubs SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE

WITH ROLLBACK AFTER integer [SECONDS] rolls back transactions and breaks the connections of all unqualified database users after the specified number of seconds.

SQL
ALTER DATABASE pubs SET RESTRICTED_USER WITH ROLLBACK AFTER 60 ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS

Example

Set Recovery model for database mydb1 to FULL.

SQL	ALTER DATABASE mydb1 SET RECOVERY FULL

Example: Set database mydb1 access to RESTRICTED_USER (allowing only members of sysadmin and dbcreator fixed server roles and db_owner fixed database roles). Unauthorized users currently connected will be unceremoniously disconnected and open transactions rolled back 60 seconds from the time the statement is executed.

SQL
ALTER DATABASE mydb1 SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS

Change access for database mydb1 back to MULTI_USER.

SQL
ALTER DATABASE mydb1 SET MULTI_USER SELECT DATABASEPROPERTYEX( 'pubs' , 'UserAccess' )
Result
--------------------------- MULTI_USER

The code dbo stands for database owner, the predefined user name in each database who is able to perform all database operations. Any sysadmin server role member becomes dbo inside each database.

2.8.2.3 DATABASEPROPERTYEX—Displays Database Settings

This function returns the current setting of the specified property in the specified database.

Syntax

SELECT DATABASEPROPERTYEX( 'dbname' , 'propertykeyword' )

Table 2-78 below lists all of the DATABASEPROPERTYEX property keywords, and some examples appear below the table. Most of these keywords were also listed above in the ALTER DATABASE keyword table (Table 2-77). A few examples were given there.

Additional database options are listed in Table 2-78.

Table 2-78. Keywords for DATABASEPROPERTYEX

DATABASE PROPERTYEX keyword	Description	Value Returned
Collation	Default collation name for the database.	Collation name
IsAnsiNullDefault	Database follows SQL-92 rules for allowing null values.	1=TRUE, 0=FALSE, NULL=Bad input
IsAnsiNullsEnabled	All comparisons to a null evaluate to null.	1=TRUE, 0=FALSE, NULL=Bad input
IsAnsiPaddingEnabled	Strings are padded to the same length before comparison or insert.	1=TRUE, 0=FALSE, NULL=Bad input
IsAnsiWarningsEnabled	Error or warning messages are issued when standard error conditions occur.	1=TRUE, 0=FALSE, NULL=Bad input
IsArithmeticAbortEnabled	Queries are terminated when an overflow or divide-by-zero error occurs.	1=TRUE, 0=FALSE, NULL=Bad input
IsAutoClose	Database shuts down cleanly and frees resources after the last user exits.	1=TRUE, 0=FALSE, NULL=Bad input
IsAutoCreateStatistics	Existing statistics are automatically updated when they become out-of-date.	1=TRUE, 0=FALSE, NULL=Bad input
IsAutoShrink	Database files are candidates for automatic periodic shrinking.	1=TRUE, 0=FALSE, NULL=Bad input
IsAutoUpdateStatistics	Auto update statistics database option is enabled.	1=TRUE, 0=FALSE, NULL=Bad input
IsCloseCursorsOnCommitEnabled	Cursors that are open when a transaction is committed are closed.	1=TRUE, 0=FALSE, NULL=Bad input
IsFulltextEnabled	Database is full-text enabled.	1=TRUE, 0=FALSE, NULL=Bad input
IsInStandBy	Database is online as read-only, with restore log allowed.	1=TRUE, 0=FALSE, NULL=Bad input
IsLocalCursorsDefault	Cursor declarations default to LOCAL.	1=TRUE, 0=FALSE, NULL=Bad input
IsMergePublished	The tables of a database can be published for replication, if replication is installed.	1=TRUE, 0=FALSE, NULL=Bad input
IsNullConcat	Null concatenation operand yields NULL.	1=TRUE, 0=FALSE, NULL=Bad input
IsNumericRoundAbortEnabled	Errors are generated when loss of precision occurs in expressions.	1=TRUE, 0=FALSE, NULL=Bad input
IsQuotedIdentifiersEnabled	Double quotation marks can be used on identifiers.	1=TRUE, 0=FALSE, NULL=Bad input
IsRecursiveTriggersEnabled	Recursive firing of triggers is enabled.	1=TRUE, 0=FALSE, NULL=Bad input
IsSubscribed	Database can be subscribed for publication.	1=TRUE, 0=FALSE, NULL=Bad input
IsTornPageDetectionEnabled	SQL Server detects incomplete I/O operations caused by power failures, etc.	1=TRUE, 0=FALSE, NULL=Bad input
Recovery	Recovery model for the database.	FULL = full recovery model BULK_LOGGED = bulk logged model SIMPLE = simple recovery model
SQLSortOrder	SQL Server sort order ID supported in previous versions of SQL Server.	0 = Database uses Windows collation >0 = SQL Server sort order ID
Status	Database status.	ONLINE = database is available OFFLINE = db was taken offline RESTORING = db is being restored RECOVERING = db is recovering and not yet ready for queries SUSPECT = db cannot be recovered
Updateability	Indicates whether data can be modified.	READ_ONLY READ_WRITE
UserAccess	Which users can access the database.	SINGLE_USER = only one user of db_owner, dbcreator, sysadmin RESTRICTED_USER = any of db_owner, dbcreator, sysadmin MULTI_USER = all users
Version	Database Version number for internal use only by SQL Server tools.	Integer = Database is open NULL = Database is closed

Example:

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'IsFulltextEnabled' )

Result
----------------- 0

This says that full text searches are not presently enabled on the pubs database.

Many DATABASEPROPERTYEX keywords are also listed in the ALTER DATABASE table in the preceding section.

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'UserAccess' )

Result
----------------- MULTI_USER

2.8.2.4 Database Level Settings in Enterprise Manager

Primary configuration settings for SQL Server databases are accessible in Enterprise Manager from the server properties dialog for a selected server and database.

Expand the Console Tree in Enterprise Manager under the desired server.
- Select your Server Name – Databases – <database name>
- Right click on the <database name> and select Properties.
The tabs available for the database Properties dialog are:

General Data Files Transaction Log Filegroups Options Permissions

Options tab— The options tab, shown in Figure 2-6, has some settings that can be set from this tab or from the command line using ALTER DATABASE (see page 187).

Access
- Restrict Access: db_owner, dbcreator, sysadmin only or Single user
- Read-only
Recovery Model: Simple or Bulk-Logged or Full
Settings to allow or disallow features such as ANSI NULL default.
Compatibility Level: 60 or 65 or 70 or 80

Figure 2-6. The Options Tab of the Properties Dialog Box.

2.8.2.5 `sp_dboption`—Brief Description as It Is Replaced by ALTER DATABASE

The stored procedure sp_dboption displays or changes database options. It is provided only for backward compatibility and might not appear in future releases of SQL Server. ALTER DATABASE is now recommended.

sp_dboption should not be used on either the master or tempdb databases.

Syntax

sp_dboption      [   [ @dbname = ] 'database' ]
                 [ , [ @optname = ] 'option_name' ]
                 [ , [ @optvalue = ] 'value' ]

These settings display or change global configuration settings for the current server.

sp_dboption may be executed with 0, 1 or 2 arguments as follows.

0 arguments: Lists all configuration setting names addressable with sp_dboption
1 argument: Displays the current settings that are set for the database specified
2 arguments: Displays the current setting of the specified option in the named database
3 arguments: Sets the specified option in the named database to the specified value

Examples:

SQL
EXEC sp_dboption

Result
Settable database options: ----------------------------------- ANSI null default ... dbo use only ...

SQL
EXEC sp_dboption pubs -- shows pubs settings which are "set"

Result
The following options are set: ----------------------------------- trunc. log on chkpt. torn page detection auto create statistics auto update statistics

SQL
EXEC sp_dboption pubs , 'dbo use only' -- 'dbo use only' is off'

Result
OptionName CurrentSetting ----------------------------------- -------------- dbo use only off

SQL
EXEC sp_dboption pubs , 'dbo use only', TRUE -- turn in on'

Result
The command(s) completed successfully.

2.8.2.6 Database Compatibility Level —`sp_dbcmptlevel`

MSS 2000 (version 8.0) implements SQL-92 more thoroughly than earlier versions, and it also adds new keywords. When upgrading a database from an earlier version of MSS, especially MSS 6.0 or 6.5, some of these changes may conflict with your existing application code.

Though running on SQL Server 2K, you may set a database to behave like an earlier version of SQL by using sp_dbcmptlevel system stored procedure. This will keep your production database operational while giving you a chance to rewrite your code. See Table 2-79.

Table 2-79. Compatibility Levels

Compatibility Level	Version
80	SQL Server 2K (version 8.0)
70	SQL Server 7.0
65	SQL Server 6.5
60	SQL Server 6.0

sp_dbcmptlevel sets the specified database to behave according to the specified version of SQL Server.

Syntax

sp_dbcmptlevel    [ [ @dbname = ] name ]     [ , [ @new_cmptlevel = ] version ]

Argument

version
	The version of SQL Server with which the database is to be made compatible. The value must be 80, 70, 65 or 60.

References

Books Online: sp_dbcmptlevel; compatibility issues, overview

2.8.3 Session (Connection) Configuration Settings

Session or connection settings are values that apply to the current connection of a client program for the SQL Server database engine. They will remain in effect until the end of the session (when the connection is closed) or a SET statement is issued as described below.

Session settings that can be quite useful for debugging or performance testing include NOEXEC, NOCOUNT, PARSEONLY, SHOWPLAN_xxx, STATISTICS xxx, etc. These options are well worth studying and testing to see the information they provide.

To determine how your current session settings are determined, I suggest starting with the OLE DB and ODBC driver connection settings (see page 215) and Query Analyzer connection settings (see page 216) and then reading the section on Which Session Setting Is in Effect? (see page 219).

Some session settings will be changed by the client libraries (OLE DB and ODBC) and by Query Analyzer, if that is the client program. And all session option settings may be changed by the user using the SET command.

Figure 2-7 is a summary of session configuration statements. The pages that follow contain detailed information about the information in the box.

Figure 2-7. Session Configuration Statements Summary.

2.8.3.1 sp_configure user options

sp_configure is a server setting that affects future sessions. The options allow a user to set all 15 default session query processing options applicable ONLY FOR NEW LOGIN SESSIONS (CONNECTIONS). Anyone currently logged in is not affected until the next time they log in.

The sp_configure USER OPTIONS value is a single integer representing a bitset specifying global defaults for 15 settings that affect each user's session (connection). A user may override any setting by changing it with the SET statement.

Executing sp_configure 'user options', value assigns default settings for new logins.

Any user may use SET to override any setting for the current session.

If a user has SET an option then that setting is used for the current session Recall ODBC and OLE DB set some options when connecting or if the current database has a setting for the option then it will be used or if sp_configure 'user options' for the option is in effect it will be used or the default setting for the option will be used.

The options settable by sp_configure 'user options' and the setting value are the same as those visible with @@OPTIONS (page 208) and are listed in Table 2-80.

Syntax

sp_configure 'user options'     [ , [ @configvalue = ]  value ]

value = The sum of the values of all options desired to be set for future new logins.

Remember to run RECONFIGURE to make the change effective.

Table 2-80. sp_configure USER OPTIONS

Value	Option	Description — Behavior when ON
1	DISABLE_DEF_CNST_CHK	Controls interim or deferred constraint checking.
2	IMPLICIT_TRANSACTIONS	Controls whether a transaction is committed automatically (OFF) when a statement is executed or the transaction requires explicit commit (ON).
4	CURSOR_CLOSE_ON_COMMIT	Controls behavior of cursors after a commit operation has been performed.
8	ANSI_WARNINGS	Controls truncation and NULL in aggregate warnings.
16	ANSI_PADDING	Controls padding of character variables. See page 102.
32	ANSI_NULLS	Controls NULL handling when using equality operators.
64	ARITHABORT	Terminates a query when an overflow or divide-by-zero error occurs.
128	ARITHIGNORE	Returns NULL when overflow or divide-by-zero error occurs during a query.
256	QUOTED_IDENTIFIER	Differentiates between single and double quotation marks when evaluating an expression.
512	NOCOUNT	Turns off the "how many rows affected" message at the end of each statement.
1024	ANSI_NULL_DFLT_ON	Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
2048	ANSI_NULL_DFLT_OFF	Alters the session to not use ANSI compatibility for nullability. New columns defined without explicit nullability will not allow nulls.
4096	CONCAT_NULL_YIELDS_NULL	Returns NULL when concatenating a NULL value with a string.
8192	NUMERIC_ROUNDABORT	Generates an error when a loss of precision occurs in an expression.
16384	XACT_ABORT	Rolls back a transaction if a Transact- SQL statement raises a run-time error.

For an example of the use of sp_configure 'user options', see page 219.

2.8.3.2 SET

The SET statement assigns current session (connection) option settings. These settings are listed in Table 2-81.

Table 2-81. SET Statement Options

SET Command Option Keyword	Default Setting	@@ OPTIONS value^{d See p. 209}	Description
Date and Time Options
DATEFIRST { 1\|2\|3\|4\|5\|6\|7 } 1=Monday, 7=Sunday	7 (Sunday)		Sets first day of week. Ex: SET DATEFIRST 7
DATEFORMAT { mdy\|dmy\|ymd\|ydm\|myd\|dym }	mdy		Sets the order of (month/day/year) for entering datetime or smalldatetime data. Ex: SET DATEFORMAT mdy
Locking Options
DEADLOCK_PRIORITY { LOW \| NORMAL \| @deadlock_var }	NORMAL		Controls how session reacts if in deadlock. LOW — Current session is victim NORMAL — Let SQL Server decide @deadlock_var - 3=LOW, 6=NORMAL
LOCK_TIMEOUT millisec_til_timeout	1		Specifies the number of milliseconds a statement waits for a lock to be released.
Miscellaneous SET Options			See also Books Online: "SET Options"
CONCAT_NULL_YIELDS_NULL	OFF	4096	ON means concatenating with NULL yields NULL versus empty string (OFF) OLE DB and ODBC set this to ON when making a new connection.
DISABLE_DEF_CNST_CHK	OFF	1	For backward compatibility only
FIPS_FLAGGER { ENTRY \| FULL \| INTERMEDIATE \| OFF }			Specifies checking for compliance with the FIPS 127-2 standard, and specifies SQL-92 Entry, Full or Intermediate Level or None.
IDENTITY_INSERT	OFF		ON allows explicit values to be inserted into an identity column.
LANGUAGE { [ N ] 'language' \| @language_var }	us_english See p. 178.		Specifies the session language including datetime formats and system messages. EXEC sp_helplanguage — list languages Example: `SET LANGUAGE Deutsch PRINT` `CAST ( '2003-05-10 14:35'` `As DATETIME )` Okt 5 2003 2:35PM `SET LANGUAGE us_english` `PRINT CAST ( '2003-05-10` `14:35' As DATETIME )` May 10 2003 2:35PM
OFFSETS keyword_list			Use only in DB-Library applications. See Books Online.
Query Execution Statements
ARITHABORT Note on Default: See footnote c.	OFF	64	Terminates a query if overflow or divide- by-zero occurs during query.
ARITHIGNORE	OFF	128	ON means Error Message is returned from overflow or divide-by-zero.
FMTONLY	OFF		Returns only meta data, no data
NOCOUNT	OFF	512	Stops the message with number of rows affected from being returned.
NOEXEC	OFF		Parse and compile but do not execute.
NUMERIC_ROUNDABORT	OFF	8192	Sets level of error reporting when rounding causes a loss of precision.
PARSEONLY	OFF		Parse but do not execute from now on.
QUERY_GOVERNOR_COST_LIMIT integervalue	0 (unlim ited)		sysadmin setting to disallow queries whose estimated run time exceeds the spec ified number of seconds. Default is 0, unlimited time, so all queries run.
ROWCOUNT integervalue	0 (unlim)		Stops processing the query after the specified number of rows.
TEXTSIZE integervalue	4 KB		Specifies the size in bytes of text and ntext data returned from a SELECT Either 0 or 4096 sets to default of 4 KB.
SQL-92 Settings Statements
ANSI_DEFAULTS	n/a		ON sets all options in this section to ON except ANSI_NULL_DFLT_OFF to OFF. OFF leaves ANSI_NULL_DFLT_OFF unchanged and sets rest to OFF
ANSI_NULLS	OFF	32	Sets ANSI SQL-92 compliant behavior in effect when comparing to NULL with equals (=) and not equal to (<>) .
ANSI_NULL_DFLT_ON	OFF	1024	Only one of these two can be ON at a time. So setting one ON sets the other OFF. Both may be set to OFF at the same time.
ANSI_NULL_DFLT_OFF	OFF	2048
ANSI_PADDING	ON	16	Set blank padding for values shorter than the defined size of the column and for values that have trailing blanks in char and binary data.
ANSI_WARNINGS	OFF	8	ON means SQL-92 standard behavior of raising error messages or warnings for conditions like divide-by-zero and arithmetic overflow.
CURSOR_CLOSE_ON_COMMIT	OFF		As described by the name when ON
QUOTED_IDENTIFIER	OFF	256	See QUOTED_IDENTIFIER discussion with examples page 44.
IMPLICIT_TRANSACTIONS	OFF	2	See details with Transactions below.
Statistics Statements
FORCEPLAN	OFF		Makes the query optimizer process a join in the same order as tables appear in the FROM clause of a SELECT statement.
SHOWPLAN_ALL	OFF		ON: does not execute SQL statements but instead returns the detailed execution plan and estimates of the resource requirements to execute the statements.
SHOWPLAN_TEXT	OFF		ON: does not execute SQL statements but instead returns the execution plan for the statements.
STATISTICS IO	OFF		ON: displays the disk activity generated by Transact-SQL statements when executed.
STATISTICS PROFILE	OFF		ON: Displays profile information for a statement including number of rows produced and number of times the query ran.
STATISTICS TIME	OFF		Displays the time in milliseconds to parse, compile and execute each statement.
Transaction Statements		^d	See "Transaction Control," page 529.
IMPLICIT_TRANSACTIONS	OFF	2	IMPLICIT_TRANSACTION mode ON requires an explicit COMMIT/ROLL BACK for each transaction. OLE DB and ODBC set this to OFF when making a new connection. When OFF, AUTOCOMMIT MODE is in effect. See Transaction Control, page 529.
REMOTE_PROC_TRANSACTIONS	OFF		Specifies that when a local transaction is active, executing a remote stored procedure starts a Transact-SQL distributed transac tion managed by the Microsoft Distributed Transaction Manager (MS DTC).
TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED \| READ COMMITTED \| REPEATABLE READ \| SERIALIZABLE }	READ COMMITTED		Controls the default locking behavior for the session (connection). See "Transaction Control," p. 529.
XACT_ABORT	OFF	16384	ON: rolls back the entire transaction if a statement raises a run-time error OFF: rolls back just the statement and the transaction continues.

2.8.3.3 @@OPTIONS

The value @@OPTIONS returns a bitmask of session options from Table 2-82 SET for the current connection. The value includes all options currently SET by virtue of server settings including sp_configure 'user options' and SET operations including those set by OLE DB and ODBC drivers (see page 215).

Bit positions in @@OPTIONS are identical to those in sp_configure 'user options' but the @@OPTIONS value represents current session settings of the options.

@@OPTIONS reports on the following 15 settings which includes the 7 options that SESSIONPROPERTY() reports. So @@OPTIONS is more complete.

Table 2-82. @@OPTIONS Settings

Option	Default	@@OPTIONS Value
DISABLE_DEF_CNST_CHK	OFF	1
IMPLICIT_TRANSACTIONS	OFF	2
CURSOR_CLOSE_ON_COMMIT	OFF	4
ANSI_WARNINGS	OFF	8
ANSI_PADDING	ON	16
ANSI_NULLS	OFF	32
ARITHABORT	OFF	64
ARITHIGNORE	OFF	128
QUOTED_IDENTIFIER	OFF	256
NOCOUNT	OFF	512
ANSI_NULL_DFLT_ON	OFF	1024
ANSI_NULL_DFLT_OFF	OFF	2048
CONCAT_NULL_YIELDS_NULL	ON	4096
NUMERIC_ROUNDABORT	OFF	8192
XACT_ABORT	OFF	16384

See more examples displaying current session (connection) settings on page 220.

SQL
SELECT @@OPTIONS & 4096 -- Shows that CONCAT_NULL_YIELDS_NULL is currently ON

Result
4096

SQL
SELECT @@OPTIONS -- Shows the integer bitmask which includes all @@OPTIONS currently ON

Result
5496

SQL
SET CONCAT_NULL_YIELDS_NULL OFF SELECT @@OPTIONS & 4096 -- Shows that CONCAT_NULL_YIELDS_NULL is currently OFF

Result
0

SQL
SELECT @@OPTIONS -- Shows the integer bitmask which includes all @@OPTIONS currently ON

Result
1400

2.8.3.4 DBCC USEROPTIONS

DBCC USEROPTIONS returns all SET options which are active (set) for the current session (connection).

Syntax

DBCC USEROPTIONS

Example

Example of ways to display current session (connection) settings.

SQL
DBCC USEROPTIONS

Result
Set Option Value ------------------------------------ --------------------------- textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2.8.3.5 SESSIONPROPERTY

SESSIONPROPERTY returns the current setting of one of the seven session options listed in Table 2-83. Returns on the setting are listed in Table 2-84.

Returns 1 if SET, 0 if NOT SET and NULL if the input option name was invalid.

Syntax

SESSIONPROPERTY ( 'option' )

Arugment

option
	The SESSIONPROPERTY option names are the same as for ALTER DATABASE.

Table 2-83. SESSIONPROPERTY Options

Option Name	Option Name
ANSI_NULLS	CONCAT_NULL_YIELDS_NULL
ANSI_PADDING	NUMERIC_ROUNDABORT
ANSI_WARNINGS	QUOTED_IDENTIFIER
ARITHABORT

For the meaning of each option see ANSI SQL-92 Compliance Options, see page 191.

Table 2-84. Returns

Return Value	Option Is Currently
1	ON
O	OFF
NULL	Invalid Option name

Examples using SESSIONPROPERTY()

SQL
SELECT SESSIONPROPERTY( 'QUOTED_IDENTIFIER' ) -- Option ON returns 1

Result
-------- 1

SQL
SELECT SESSIONPROPERTY( 'NUMERIC_ROUNDABORT' ) -- Option OFF returns 0

Result
-------- 0

SQL
SELECT SESSIONPROPERTY( 'Foo_Foo' ) -- Invalid input option name, returns NULL

Result
-------- NULL

2.8.3.6 Comparing @@OPTIONS, DBCC USEROPTIONS and SESSIONPROPERTY()

The following methods show current session settings as indicated:

@@OPTIONS enables you to determine the setting of a specific option but it requires looking up the option number of interest and doing a bitwise AND to determine if a specific setting is on or off. Only the settings that have a value in the @@OPTIONS column of Table 2-81, page 204, may be read with this function.

DBCC USEROPTIONS is convenient since it reports all options that are currently set. It is silent on options not currently set.

SESSIONPROPERTY() returns the one option setting specified, as does @@OPTIONS, and it uses the same option keyword as SET, so it's more consistent in its use. But it is less complete than @@OPTIONS because it only reports on the seven options listed in Table 2-83.

Examples Comparing the Three:

SQL
SELECT @@OPTIONS & 1024 -- Shows that ANSI_NULL_DFLT_ON is currently ON

Result
-------- 1024

SQL
SELECT @@OPTIONS & 4096 -- Shows that CONCAT_NULL_YIELDS_NULL is currently ON

Result
-------- 4096

SQL
SELECT SESSIONPROPERTY( 'ANSI_NULL_DFLT_ON' ) -- Can't check this one

Result
-------- NULL

SQL
SELECT SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL' ) -- Option ON returns 1

Result
-------- 1

SQL
DBCC USEROPTIONS

Result
Set Option Value ------------------------------------- --------------------------- ansi_null_dflt_on SET ... concat_null_yields_null SET DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2.8.3.7 Session Configuration Functions

These built-in scalar functions return the current session setting indicated by the name. Table 2-85 provides a description.

Table 2-85. Session Configuration Functions

Function Name	Description
@@DATEFIRST	Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.
@@DBTS	Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.
@@LANGID	Returns the local language identifier (ID) of the language currently in use.
@@LANGUAGE	Returns the name of the language currently in use.
@@LOCK_TIMEOUT	Returns the current lock time-out setting, in milliseconds, for the current session.
@@MAX_CONNECTIONS	Returns the maximum number of simultaneous user connections allowed on a Microsoft SQL Server. The number returned is not necessarily the number currently configured.
@@MAX_PRECISION	Returns the precision level used by decimal and numeric data types as currently set in the server.
@@NESTLEVEL	Returns the nesting level of the current stored procedure execution (initially 0).
@@OPTIONS	Returns information about current SET options.
@@REMSERVER	Returns the name of the remote Microsoft SQL Server database server as it appears in the login record.
@@SERVERNAME	Returns the name of the local server running Microsoft SQL Server.
@@SERVICENAME	Returns the name of the registry key under which Microsoft SQL Server is running. @@SERVICENAME returns MSSQLServer if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.
@@SPID	Returns the server process identifier (ID) of the current user session.
@@TEXTSIZE	Returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns.
@@VERSION	Returns the date, version and processor type for the current installation of Microsoft SQL Server.

Example:

SQL
SELECT @@SPID -- Returns the id SQL Server has assigned the current session (connection)

Result
51

2.8.3.8 OLE DB and ODBC Driver Connection Settings

OLE DB and ODBC drivers make the following settings for every new connection.

CONCAT_NULL_YIELDS_NULL
ANSI_NULL_DEFAULT
ANSI_DEFAULTS  -- which set all of the following to ON
  ANSI_NULLS BOL -- 'SET Options' for ANSI_DEFAULTS  BOL -- 'SET Options' for ANSI_DEFAULTS

  ANSI_NULL_DFLT_ON -- sets ANSI_NULL_DFLT_OFF to OFF
  ANSI_PADDING         -- See page 112.
  ANSI_WARNINGS
  QUOTED_IDENTIFIER

OFF

CURSOR_CLOSE_ON_COMMIT
  IMPLICIT_TRANSACTIONS

See each item under "SETTING DATABASE OPTIONS" "SET CONCAT_NULL_YIELDS_NULL" for ODBC/OLE DB sessions settings.

ODBC and OLE DB first turn on the above settings identified as ON. Then they turn off the two items identified as OFF (they were set to ON when ANSI_DEFAULTS was set ON). See "SET ANSI_DEFAULTS" for ODBC/OLE DB sessions settings. These settings will be in effect for every ODBC and OLE DB client unless you change them with an explicit SET statement. See Query Analyzer additions next.

2.8.3.9 Query Analyzer Connection Settings

Query Analyzer uses ODBC, so it starts with the ODBC settings listed above in effect, then it sets the following additional options as shown.

ARITHABORT

OFF

NOCOUNT
  NOEXEC
  PARSEONLY
  SHOWPLAN_TEXT
  STATISTICS TIME
  STATISTICS IO

0	ROWCOUNT (0 or NULL means unlimited rows in result sets)

The net result of these default actions can be confirmed by executing this statement in Query Analyzer.

SQL
-- In a Query Analyzer with default settings DBCC USEROPTIONS

Result
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

These settings, except the first four, are set explicitly by ODBC and Query Analyzer as just described. The first four were inherited from the defaults as summarized in the next section. See also Books Online: Using SET Options in SQL Query Analyzer.

2.8.3.10 Changing Query Analyzer Default Connection Settings

You may change the default connection settings for your own Query Analyzer from Query—Current Connection Properties, which opens the dialog shown in Figure 2-8. Check a box for ON or uncheck for OFF and click Apply. Table 2-86 lists the default SET session settings made by Query Analyzer.

Figure 2-8. The Connection Properties Dialog Box in Query Analyzer.

Table 2-86. Summary of All Default SET Session Settings Made by Query Analyzer

Option	Setting
Set nocount	OFF
Set noexec	OFF
Set parseonly	OFF
Set concat_null_yields_null	ON
Set rowcount	0
Set ansi_defaults	ON
Set arithabort	ON
Set showplan_text	OFF
Set statistics time	OFF
Set statistics 10	OFF
Set ansi_nulls	ON
Set ansi_null_dflt_on	ON
Set ansi_padding	ON
Set ansi_warnings	ON
Set cursor_close_on_commit	OFF
Set implicit_transactions	OFF
Set quoted_identifier	ON

2.8.3.11 Which Session Setting Is in Effect?

What follows is my version of SQL Server's algorithm to decide which setting to use.

If a user has SET an option then that setting is used for the current session else if it is an option set by ODBC, OLE DB or Query Analyzer then it will be used (page 215) else if sp_configure 'user options' for the option is in effect it will be used (page 202) else if the current database has a setting for the option then it will be used (page 187) else the SQL Server default setting for the option will be used (page 203).

The case numbers are given below to identify what is happening in the following examples.

1. An explicit session SET statement takes precedence and lasts until changed by a new SET statement or the end of the session (connection).

2a. OLE DB and ODBC drivers make the following settings for each new connection.

CONCAT_NULL_YIELDS_NULL
  ANSI_NULL_DEFAULT
  ANSI_DEFAULTS (which set esach of the following to ON)
    ANSI_NULLS
    ANSI_NULL_DFLT_ON (which sets ANSI_NULL_DFLT_OFF to OFF)
    ANSI_PADDING (see page 102)
    ANSI_WARNINGS
    CURSOR_CLOSE_ON_COMMIT
    IMPLICIT_TRANSACTIONS
    QUOTED_IDENTIFIER

OFF

CURSOR_CLOSE_ON_COMMIT
              IMPLICIT_TRANSACTIONS

So these settings will be in effect for every ODBC and OLE DB client until you change them with an explicit SET statement. This is true regardless of sp_configure 'user options' or database options assigned with ALTER DATABASE.

2b. Query Analyzer sets ARITHABORT to ON (see page 216).
3. In the absence of 1 or 2, any option set with sp_configure 'user options' will be used.
4. Database default is next (can be set with ALTER DATABASE, pages 189 and 250).
5. Lastly, the SQL Server default will be used, page 202.

Examples Showing which Session Setting Is in Effect

Examples are given here to demonstrate Case 1, 2a, 2b, 3 and 4. Each example starts with a new connection and shows user actions, if any, to change a setting and the result.

Example: CASE 1—Explicit SET CONCAT_NULL_YIELDS_NULL to OFF

Open a new database connection to the pubs database.

SQL
SELECT SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL' ) -- Show it's ON

Result
------- 0

SQL
SET COmAT_NULL_YIELDS_NULL OFF -- changes OLE DB setting SELECT SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL' ) -- Show it's now OFF

Result

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'IsNullConcat' ) -- Show DB default is OFF

Result
------- 0

Table 2-87 contains a summary of actions in order of precedence. The first "Yes" from the left takes precedence.

Table 2-87. Case 1 Explicit SET of CONCAT_NULL_YIELDS_NULL Option

Explicit SET

Set by OLE DB/ODBC

User Option

Database Default

Yes—SET to OFF

Yes—ON

but overridden

Would be overridden even if set

OFF

but overridden

Example: CASE 2a—OLE DB sets CONCAT_NULL_YIELDS_NULL to ON.

Open a new database connection to the pubs database.

SQL
SELECT SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL' ) -- Session setting is ON -- (I'm using Query Analyzer and OLE DB set it ON)

Result
------- 1

SQL
SELECT @@OPTIONS & 4096 -- Same result, this is an alternative to SESSIONPROPERTY

Result
------- 4096

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'IsNullConcat' ) -- Show DB default is OFF

Result
------- 0

Table 2-88 contains a summary of actions in order of precedence. The first "Yes" from the left takes precedence.

Table 2-88. Case 2a—ODBC Set of CONCAT_NULL_YIELDS_NULL option

Explicit SET

Set by OLE DB/ODBC

User Option

Database Default

Yes — ON

Would be overridden even if set

OFF

but overridden

Example

CASE 2b— Query Analyzer sets ARITHABORT to ON.

See "Query Analyzer Connection Settings" on page 216.

Example: CASE 3—NUMERIC_ROUNDABORT, we'll change User Option to ON.

NUMERIC_ROUNDABORT is one of the few options not set by OLE DB or ODBC, so setting the default user option will have a visible effect. In Session 1 below we first demonstrate that no user options settings are in effect and that NUMERIC_ROUNDABORT defaults to OFF. Then we use sp_configure to set the new user default to ON. Session 1 won't be affected, so we open a new connection as Session 2 and see the new setting is ON.

Session 1 This session observes and changes the sp_configure 'user options', but only new login sessions will see the effect. Open a new database connection to the pubs database.

SQL
EXEC sp_configure 'user options' -- Show that no 'user options' are currently set -- (run value is 0)

Result
name minimum maximum config_value run_value --------------- ------------ -------------- ----------------- ----------- user options 0 32767 0 0

SQL
-- For fun, show that the setting in this session is off before and after the 'user option' is changed SELECT SESSIONPROPERTY( 'NUMERIC_ROUNDABORT' ) -- Session setting is OFF

Result
------- 0

Change the 'user option':

SQL
-- Set option for NUMERIC_ROUNDABORT to ON EXEC sp_configure 'user options' , 8192 RECONFIGURE -- Don't forget that reconfigure is required to make the change effective EXEC sp_configure 'user options' -- NUMERIC_ROUNDABORT 'user options is set' (8192)

Result
name minimum maximum config_value run_value --------------- ------------ -------------- ----------------- ----------- user options 0 32767 8192 8192

SQL
-- The setting is on for new sessions, but our NUMERIC_ROUNDABORT option setting is still OFF. SELECT SESSIONPROPERTY( 'NUMERIC_ROUNDABORT' )

Result
------- 0

Session 2 New Login Sessions (Connections) will see the Change Open NEW Query Analyzer CONNECTION. The session setting is now ON.

SQL
Again show that the Database default for IsNumericRoundAbortEnabled = OFF SELECT DATABASEPROPERTYEX( 'pubs' , 'IsNumericRoundAbortEnabled')

Result
------- 1

SQL
SELECT @@OPTIONS & 8192 -- Same result as SESSIONPROPERTY

Result
------- 8192

SQL
-- Show that the Database default for IsNumericRoundAbortEnabled = OFF SELECT DATABASEPROPERTYEX( 'pubs' , 'IsNumericRoundAbortEnabled')

Result
------- 0

-- Clean up by returning User Options to 0 for future sessions
EXEC sp_configure  'user options' , 0
RECONFIGURE                         -- Don't forget that reconfigure is required to make
 the change effective

Summary of actions in order of precedence: The first "Yes" from the left takes precedence.

Table 2-89. Case 3—NUMERIC_ROUNDABORT Option— Recall 'user option' Affects Only New Sessions

Explicit SET	Set by OLE DB/ODBC	User Option	Database Default
No	No	ON	OFF

Example: CASE 4—NUMERIC_ROUNDABORT option, uses the Database default. Open a new database connection to the pubs database.

SQL
SELECT DATABASEPROPERTYEX( 'pubs' , 'IsNumericRoundAbortEnabled') -- Default OFF

Result
------- 0

SQL
SELECT SESSIONPROPERTY( 'NUMERIC_ROUNDABORT' ) --Show session setting is also off

Result
------- 0

SQL
SELECT @@OPTIONS & 8192 -- Same result as SESSIONPROPERTY

Result
------- 0

Summary of actions in order of precedence: The first "Yes" from the left takes precedence.

Table 2-90. Case 4—NUMERIC_ROUNDABORT Option Uses Database Option Unless Explicitly Set

Explicit SET	Set by OLE DB/ODBC	User Option	Database Default
No	No	Not set	OFF

2.8.3.12 Examples of Displaying Session Properties in Different Clients

Example: This example starts with a new SQL Server instance with all default settings.

No Options Set: Use isql to connect to the new SQL Server instance using (old) DB-Lib

C:>    isql   -Usa  -P
1> SELECT @@OPTIONS  As  OptionSettings
2> go
OptionSettings
------ ------------
                        0

1> DBCC USEROPTIONS
2> go
 Set Option        Value
 -----------------------------
 textsize       4096
 language       us_english
 dateformat       mdy
 datefirst         7
 (4  row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact
 your system administrator.

Add ODBC initial settings: The osql utility uses ODBC to connect to SQL Server. This shows the added options set by ODBC (and OLE DB).

C:>    osql   -Usa  -P
1> SELECT @@OPTIONS  As  OptionSettings
2> go
OptionSettings
-------------------
                  5176

1> DBCC USEROPTIONS
2> go
 Set OptionValue
 ------------------------------------------
 textsize                        2147483647
 language                        us_english
 dateformat                      mdy
 datefirst                        7
 ansi_null_dflt_on               SET
 ansi_warnings                   SET
 ansi_padding                    SET
 ansi_nulls                      SET
 concat_null_yields_null         SET
(9  row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact
 your system administrator.

Other examples of SET, DBCC USEROPTIONS, @@OPTIONS, SESSIONPROPERTY()

SQL
PRINT @@OPTIONS

Result
5496

SQL
PRINT @@OPTIONS & 64 -- arithabort bitmask

Result
64

SQL
SELECT SESSIONPROPERTY( 'arithabort' )

Result
------- 1

Now turn one option off and re-run the display statements.

SQL
SET arithabort OFF DBCC USEROPTIONS

Result
Set Option Value ---------------------------- ------------ textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET ansi_null_dflt_on SET -- arithabort is missing now ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET (11 row(s) affected)

SQL
PRINT @@OPTIONS

Result
5432

SQL
PRINT @@OPTIONS & 64 -- arithabort bitmask

Result
0

SQL
SELECT SESSIONPROPERTY( 'arithabort' )

Result
------- 0

SQL
SELECT SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL' ) -- Show it's ON

Result
------- 0

Here's a nice way to show arithabort setting which uses @@OPTIONS.

SQL
PRINT 'ARITHABORT: ' + CASE WHEN @@OPTIONS & 64 > 0 THEN 'ON' ELSE 'OFF' END

Result
ARITHABORT: OFF

SQL
SET ARITHABORT ON PRINT 'ARITHABORT: ' + CASE WHEN @@OPTIONS & 64 > 0 THEN 'ON' ELSE 'OFF' END

Result
ARITHABORT: ON

2.8.4 Default Nullability of New Columns in a Table

This subject seems unduly complex. Leaving everything default as it comes out of the box seems most useful and is certainly easiest, as in the example CREATE TABLE t below. But here are the details for those who enjoy confusing topics.

What I call the default nullability setting means that if a user executes CREATE TABLE or ALTER TABLE to add a new column to a table and does not specify either NULL or NOT NULL explicitly, the default nullability setting determines the nullability of the new column, that is, whether it will be created as NULL or NOT NULL.

ANSI SQL-92 standard specifies default nullability to be nullable, that is, default is NULL.

Default nullability is determined by database and session settings. Session setting for ANSI_NULL_DFLT_ON or ANSI_NULL_DFLT_OFF determines the default nullability if either is ON. (Setting one ON sets the other OFF.) Database setting ANSI_NULL_DEFAULT will rule if both session settings are OFF.

Bottom line: ODBC drivers and OLE DB providers set ANSI_NULL_DFLT_ON to ON for each connection, so the Query Analyzer and other clients using these libraries behave with new columns defaulting to nullable.

ANSI_NULL_DFLT_ON will thus be ON unless you explicitly issue either

	SET ANSI_NULL_DFLT_ON	OFF
or	SET ANSI_NULL_DFLT_OFF	ON

This setting will remain in effect for the rest of your connection unless you change it.

It is suggested that you do not issue either of these statements and so leave the out-of-the-box defaults intact. In this case, use the following CREATE TABLE statement.

CREATE TABLE  t (
  col1  INT  NOT NULL, -- col1 will NOT allow NULL and
  col2  INT  NULL    , -- col2 will allow NULL regardless of settings
  col3  INT          ) -- col3 heeds the settings

This would result in col3 being nullable as if it had been created just like col2.

If you do issue either of the two SET statements above, then col3 would be non-nullable as if it had been created like col1.

The only way for the ANSI_NULL_DEFAULT database setting to have an effect is if SET ANSI_NULL_DFLT_ON OFF is executed, so this database option seems pretty much useless unless you want to issue that statement, or if you can find a way to connect without using either OLE DB or ODBC.

It should be noted for the record that, according to Books Online, "Microsoft SQL Server 2000 defaults to NOT NULL." So the database option ANSI_NULL_DEFAULT will be found to be OFF, but again, this is overridden by the OLE DB and ODBC drivers turning ON the ANSI_NULL_DFLT_ON option.

2.8.4.1 How to Set and Determine the Current Nullability Settings

The remaining discussion in this section is for completeness and could easily be skipped.

Three levels have a hand in determining the ultimate default nullability of a new column.

Server Configuration

This affects session options of logins created after the change.

sp_configure  'user options'  ,  1024     — Turns on ANSI_NULL_DFLT_ON
sp_configure  'user options'  ,  2048     — Turns on ANSI_NULL_DFLT_OFF

Only one may be ON or both OFF: Setting one ON sets the other OFF.

These seem to have no effect since they assign the SET options of the session, but both ODBC and ODE DB set ANSI_NULL_DFLT_ON to true for each session.

Database Configuration

ALTER DATABASE dbname SET  ANSI_NULL_DEFAULT {ON|OFF}

Default setting is OFF.

Current database setting is visible with:

SELECT  DATABASEPROPERTYEX( 'dbname' , 'IsAnsiNullDefault'  )

Session (Connection) Settings

These take precedence if either is ON.

SET   ANSI_NULL_DFLT_ON     {ON | OFF}
SET   ANSI_NULL_DFLT_OFF    {ON | OFF}

Only one may be ON or both OFF: Setting one ON sets the other OFF.

Also, SET ANSI_DEFAULTS ON includes SET ANSI_NULL_DFLT_ON ON.

Settings of the current session in the current database are visible with:

DBCC USEROPTIONS

Shows if ANSI_NULL_DFLT_ON or ANSI_NULL_DFLT_OFF is SET.

Show effective nullability settings in specified database in current session.

SELECT  GETANSINULL ( [ 'dbname' ] )

Returns 1 if NULL, 0 if NOT NULL is the effective nullability. This is what is used.

Example:

SQL
SELECT GETANSINULL ( 'pubs' ) --Shows the default nullability is NULL in pubs db in this session

Result
------- 1

GETANSINULL() result shows what will be used in a CREATE TABLE. Table 2-91 shows how.

Table 2-91. Default Nullability

Session ANSI_NULL_DFLT_ON	Session ANSI_NULL_DFLT_OFF	Default Nullability of New Columns
ON	ON	Impossible (either ON turns other OFF)
ON	OFF	New columns default to nullable DATABASE setting is IGNORED
OFF	ON	New columns default to not nullable DATABASE setting is IGNORED
OFF	OFF	DATABASE ANSI_NULL_DEFAULT SETTING RULES

2.8.5 Collation

A collation determines how sort order, case sensitivity and related issues are handled for columns of string data types, that is char, varchar, text, nchar, nvarchar and ntext.

SQL Server is installed with a default server level collation. SS 2000 default is, "=Dictionary order, case-insensitive, for use with 1252 Character Set.

SQL Server 2K supports different collations for each database down to the level of columns within a table. SQL Server 7.0 allows only a single collation for an instance.

The server level default collation will usually be the collation of every database, and the database default will be the default collation of each table column of string data type.

The COLLATE clause may specify collation for a database or for a column in a table.

A COLLATE clause may be applied at several levels including to a

database definition,
column definition in a table or
string expression

These determine comparison and sorting characteristics. See examples of each below.

New in SQL Server 2K is the capability to create a new database using the COLLATE clause to specify a different collation.

CREATE DATABASE  databasename COLLATE  <collation_name>
   
   ALTER DATABASE   databasename COLLATE  <collation_name>

See Books Online for restrictions on changing an existing database collation.

Also new with SQL Server 2K is the ability to set a collation for a single column of a table or table variable.

CREATE TABLE tablename (
   columnname   columndefinition   COLLATE <collation_name>
   ... )

The code collation_name can be a Windows collation name or SQL collation name, and is applicable only for columns of char, varchar, text, nchar, nvarchar and ntext data types.

For a list of all Windows and SQL collations, execute the following sequence.

SQL
*SELECT FROM ::fn_helpcollations()**

Result
name description ------------------------- ------------------------------------------------------------------------------------------- Albanian_BIN Albanian, binary sort Albanian_CS_AS Albanian, case-sensitive, accent-sensitive, kanatype-insensitive, width-ins... ... Latin1_General_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, ... Latin1_General_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, ... ....

Note: Unicode was designed to eliminate the code page conversion difficulties of the non-Unicode char, varchar and text data types. When you support multiple languages, use the Unicode data types nchar, nvarchar and ntext for all character data.

Two example collations follow.

Latin1_General_CI_ASCI means case insensitive
Latin1_General_CS_ASCS means case sensitive

Latin1_General is the Latin alphabet used by western European languages. It is also referred to as the 1252 character set.

Example: Create a Database with a specified collation (Case Sensitive).

SQL
CREATE DATABASE mydb COLLATE Latin1_General_CS_AS USE mydb go CREATE TABLE Table1 ( a INT , A INT ) INSERT INTO Table1 VALUES ( 1 , 2 ) *SELECT FROM Table1 WHERE a = 1**
Result
a A ------ ------ 1 2 (1 row(s) affected)

SQL
*SELECT FROM Table1 WHERE a = 2**

Result
a A ------ ------ (0 row(s) affected)

SQL
*SELECT FROM Table1 WHERE A = 1**

Result
a A ------ ------ (0 row(s) affected)

SQL
*SELECT FROM table1**

Result
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'table1'.

Example: Specify the collation of a string using CASTNotice that CI is for case insensitive and CS is for case sensitive. Without the CAST statement, the database collation is used for comparison.

SQL
USE pubs -- pubs has default case insensitive collation go IF 'abc' = 'ABC' -- We expect TRUE if case insensitive, FALSE if case sensitive PRINT 'TRUE. Yes, they compare' ELSE PRINT 'FALSE. Nope, not the same'

Result
TRUE. Yes, they compare

SQL
USE mydb -- mydb was created above with case sensitive collation, so it should be FALSE go IF 'abc' = 'ABC' -- We expect TRUE if case insensitive, FALSE if case sensitive PRINT 'TRUE. Yes, they compare' ELSE PRINT 'FALSE. Nope, not the same'

Result
FALSE. Nope, not the same

But, the string can be CAST to case insensitive.

SQL
IF 'abc' = CAST( 'ABC' as VARCHAR(10) ) COLLATE Latin1_General_CI_AS PRINT 'Yes, they compare' ELSE PRINT 'Nope, not the same'

Result
TRUE. Yes, they compare

Example: Create table columns with a specified collation. Overrides database default.

CREATE  TABLE t (
    ci VARCHAR(10) COLLATE  Latin1_General_CI_AS
  , cs VARCHAR(10) COLLATE Latin1_General_CS_AS
    )

    INSERT INTO t VALUES ( 'aaa', 'aaa' );
    INSERT INTO t VALUES ( 'AAA', 'AAA' );
Column ci is case insensitive for searches, column cs is case
 sensitive.

SQL
*SELECT FROM t WHERE ci = 'aaa'**

Result
ci cs ---------- ---------- aaa aaa AAA AAA

SQL
*SELECT FROM t WHERE cs = 'aaa'**

Result
ci cs ---------- ---------- aaa aaa

Use the string CAST on the column to get case insensitive search.

SQL
*SELECT FROM t WHERE 'aaa' = CAST( cs AS VARCHAR(10) ) COLLATE Latin1_General_CI_AS**

Result
ci cs ---------- ---------- aaa aaa AAA AAA

< Back Page 8 of 8

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address

SQL Building Blocks and Server Settings

This chapter is from the book

This chapter is from the book

This chapter is from the book 

2.8 SERVER, DATABASE AND SESSION SETTINGS

2.8.1 Settings Overview

2.8.1.1 Server Configuration

2.8.1.2 Database Configuration

2.8.1.3 Session (Connection) Configuration

Author's Opinion

2.8.1.4 Server Configuration Settings

2.8.1.5 sp_configure

Syntax

Table 2-76. Server Configuration Settings Accessible with sp_configure

2.8.1.6 sp_configure SHOW ADVANCED OPTIONS

Example

2.8.1.7 sp_configure USER OPTIONS

Example:

2.8.1.8 When Do sp_configure Changes Become Effective?

Example 1: The allow updates option requires RECONFIGURE WITH OVERRIDE.

Example 2: The 'fill factor' option also requires server stop and restart

2.8.1.9 RECONFIGURE

Syntax

2.8.1.10 RECONFIGURE—Without the WITH OVERRIDE Option

2.8.1.11 RECONFIGURE WITH OVERRIDE

Example:

2.8.1.12 SQL Server Settings in Enterprise Manager

2.8.2 Database Configuration (Database Properties)

2.8.2.1 ALTER DATABASE dbname SET option

Partial Syntax

Table 2-77. ALTER Database Configuration Option Keywords

2.8.2.2 Examples—ALTER DATABASE to Change UserAccess of a Database

Examples

Setting to Either RESTRICTED_USER or SINGLE_USER Database Access

Example

2.8.2.3 DATABASEPROPERTYEX—Displays Database Settings

Syntax

Table 2-78. Keywords for DATABASEPROPERTYEX

Example:

2.8.2.4 Database Level Settings in Enterprise Manager

2.8.2.5 sp_dboption—Brief Description as It Is Replaced by ALTER DATABASE

Syntax

Examples:

2.8.2.6 Database Compatibility Level —sp_dbcmptlevel

Table 2-79. Compatibility Levels

Syntax

Argument

References

2.8.3 Session (Connection) Configuration Settings

2.8.3.1 sp_configure user options

Syntax

Table 2-80. sp_configure USER OPTIONS

2.8.3.2 SET

Table 2-81. SET Statement Options

2.8.3.3 @@OPTIONS

Table 2-82. @@OPTIONS Settings

2.8.3.4 DBCC USEROPTIONS

Syntax

Example

2.8.3.5 SESSIONPROPERTY

Syntax

Arugment

Table 2-83. SESSIONPROPERTY Options

Table 2-84. Returns

Examples using SESSIONPROPERTY()

2.8.3.6 Comparing @@OPTIONS, DBCC USEROPTIONS and SESSIONPROPERTY()

Examples Comparing the Three:

2.8.3.7 Session Configuration Functions

Table 2-85. Session Configuration Functions

Example:

2.8.3.8 OLE DB and ODBC Driver Connection Settings

2.8.3.9 Query Analyzer Connection Settings

2.8.3.10 Changing Query Analyzer Default Connection Settings

Table 2-86. Summary of All Default SET Session Settings Made by Query Analyzer

2.8.3.11 Which Session Setting Is in Effect?

Examples Showing which Session Setting Is in Effect

Table 2-87. Case 1 Explicit SET of CONCAT_NULL_YIELDS_NULL Option

Example: CASE 2a—OLE DB sets CONCAT_NULL_YIELDS_NULL to ON.

Table 2-88. Case 2a—ODBC Set of CONCAT_NULL_YIELDS_NULL option

Example

2.8.1.6 `sp_configure` SHOW ADVANCED OPTIONS

2.8.1.7 `sp_configure` USER OPTIONS

2.8.1.8 When Do `sp_configure` Changes Become Effective?

2.8.2.5 `sp_dboption`—Brief Description as It Is Replaced by ALTER DATABASE

2.8.2.6 Database Compatibility Level —`sp_dbcmptlevel`