Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

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.

02fig03.gifFigure 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
                  graphics/ccc.gif 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
                  graphics/ccc.gif 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
                     graphics/ccc.gif 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?

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
                  graphics/ccc.gif 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.5

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 --
                     graphics/ccc.gif 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
                     graphics/ccc.gif change, RECONFIGURE is
                  not
                     graphics/ccc.gif 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).

02fig04.jpgFigure 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.

02fig05.gifFigure 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
                  graphics/ccc.gif 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
                  graphics/ccc.gif ROLLBACK IMMEDIATE
               ALTER DATABASE pubs SET SINGLE_USER  WITH ROLLBACK
                  graphics/ccc.gif 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' , 
                     graphics/ccc.gif '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

02fig06.jpgFigure 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
                     graphics/ccc.gif 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
                     graphics/ccc.gif use only' is off'
                  
 

Result

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

SQL

EXEC sp_dboption pubs , 'dbo use only', TRUE -- 
                     graphics/ccc.gif 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.

02fig07.jpgFigure 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 valued 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
                  graphics/ccc.gif CONCAT_NULL_YIELDS_NULL is currently ON
               
 

Result

4096

SQL

SELECT @@OPTIONS -- Shows the integer bitmask
                  graphics/ccc.gif which   includes all @@OPTIONS currently ON
               
 

Result

5496

SQL

SET CONCAT_NULL_YIELDS_NULL   OFF
               SELECT @@OPTIONS & 4096      -- Shows that
                  graphics/ccc.gif 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' )  
                     graphics/ccc.gif -- Option ON returns 1
                  
 

Result

--------
1

SQL

SELECT  SESSIONPROPERTY( 'NUMERIC_ROUNDABORT' )  
                     graphics/ccc.gif -- Option OFF returns 0
                  
 

Result

--------
0

SQL

SELECT  SESSIONPROPERTY( 'Foo_Foo' ) -- Invalid
                     graphics/ccc.gif 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
     graphics/ccc.gif ANSI_NULL_DFLT_ON   is currently ON
                  
 

Result

--------
1024

SQL

SELECT @@OPTIONS & 4096 -- Shows that
                     graphics/ccc.gif CONCAT_NULL_YIELDS_NULL   is currently ON
                  
 

Result

--------
4096

SQL

SELECT  SESSIONPROPERTY( 'ANSI_NULL_DFLT_ON' ) --
                     graphics/ccc.gif Can't check this one
                  
 

Result

--------
NULL

SQL

SELECT  SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL'
                     graphics/ccc.gif )   -- 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
                     graphics/ccc.gif 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.

ON

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.

ON

ARITHABORT

OFF

NOCOUNT
  NOEXEC
  PARSEONLY
  SHOWPLAN_TEXT
  STATISTICS TIME
  STATISTICS IO

0

ROWCOUNT (0 or NULL means unlimited rows in result
graphics/ccc.gif 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
                  graphics/ccc.gif 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.

02fig08.jpgFigure 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.

    ON

    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'
                     graphics/ccc.gif ) -- Show it's ON
                  
 

Result

-------
0

SQL

SET  COmAT_NULL_YIELDS_NULL  OFF             --
                     graphics/ccc.gif changes OLE DB setting
                  
                  SELECT SESSIONPROPERTY( 'CONCAT_NULL_YIELDS_NULL'
                     graphics/ccc.gif ) -- 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
                     graphics/ccc.gif 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

No

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
                     graphics/ccc.gif 'user
                  options' are
                     graphics/ccc.gif 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
                     graphics/ccc.gif is off before and after the 'user option' is changed
                  SELECT SESSIONPROPERTY( 'NUMERIC_ROUNDABORT' ) --
                     graphics/ccc.gif Session setting is OFF
                  
 

Result

-------
0

Change the 'user option':

SQL

-- Set option for NUMERIC_ROUNDABORT to ON EXEC
                     graphics/ccc.gif sp_configure 'user options' , 8192
                  
                  RECONFIGURE -- Don't forget that reconfigure is
                     graphics/ccc.gif 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
                 graphics/ccc.gif IsNumericRoundAbortEnabled = OFF
                  SELECT DATABASEPROPERTYEX( 'pubs' ,
                 graphics/ccc.gif 'IsNumericRoundAbortEnabled')
                  
 

Result

-------
1

SQL

SELECT  @@OPTIONS & 8192 -- Same result as
                     graphics/ccc.gif SESSIONPROPERTY
                  
 

Result

-------
8192

SQL

-- Show that the Database default for
             graphics/ccc.gif IsNumericRoundAbortEnabled = OFF
                  SELECT DATABASEPROPERTYEX( 'pubs' ,
             graphics/ccc.gif '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
graphics/ccc.gif 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
        graphics/ccc.gif 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
graphics/ccc.gif 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
graphics/ccc.gif 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'
                  graphics/ccc.gif ) -- 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 >
                  graphics/ccc.gif 0 THEN 'ON' ELSE  'OFF' END
               

Result

ARITHABORT:  OFF

SQL

SET ARITHABORT ON

PRINT 'ARITHABORT: ' + CASE WHEN @@OPTIONS & 64 >
graphics/ccc.gif 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
                     graphics/ccc.gif   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
                     graphics/ccc.gif collation
                  go
                  IF 'abc' = 'ABC'   -- We expect TRUE if case
                     graphics/ccc.gif 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
                     graphics/ccc.gif sensitive collation, so it should be FALSE
                  go
                  IF 'abc' = 'ABC'   -- We expect TRUE if case
                     graphics/ccc.gif 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 
                     graphics/ccc.gif 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
graphics/ccc.gif 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) )
                     graphics/ccc.gif COLLATE  Latin1_General_CI_AS
                  
 

Result

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

  • + Share This
  • 🔖 Save To Your Account