Microsoft® SQL Server® 2012 Bible

515

Chapter 19: Confi guring SQL Server

19

TABLE 19-9 SQL ANSI–Confi guration Properties

Property Level* Graphic Control Code Option

ANSI Defaults C Management Studio SET ANSI_DEFAULTS

ANSI Null

Behavior

SDC Management Studio ALTER DATABASE <DB Name> SET

ANSI_NULL_DFLT_OFF

SET ANSI_NULL_DFLT_ON

ANSI Nulls SDC Management Studio ALTER DATABASE <DB Name> SET

ANSI_NULLS

ANSI Padding SDC Management Studio ALTER DATABASE <DB Name> SET

ANSI_PADDING

ANSI Warnings SDC Management Studio ALTER DATABASE <DB Name> SET

ANSI_WARNINGS

Arithmetic Abort SDC Management Studio ALTER DATABASE <DB Name> SET

arithabort

Arithmetic Ignore SC - SET ARITHIGNORE

Numeric Round

Abort

SDC Management Studio ALTER DATABASE <DB Name> SET

NUMERIC_ROUNDABORT{ON | OFF}

Null

Concatenation

SDC Management Studio ALTER DATABASE <DB Name> SET

CONCAT_NULL_YIELDS_NULL

Use Quoted

Identifi er

SD Management Studio ALTER DATABASE <DB Name> SET

QUOTED_IDENTIFIER

* The confi guration level refers to server, database, or connection.

The connection default properties (there are several) affect the environment of batches

executed within a connection. Most of the connection properties change SQL Server behav-

ior so that it complies with the ANSI standard. Because so few SQL Server installations

modify these properties, it’s much safer to modify them in code at the beginning of a batch

than to set them at the server or database level.

For example, T-SQL requires a begin transaction to start a logical unit of work. Oracle

assumes a begin transaction is at the beginning of every batch. If you prefer to work

with implicit (nonstated) transactions, then you’re safer setting the implicit transaction

connection property at the beginning of your batch. For these reasons, you should leave

the connection properties at the default values and set them in code if needed.

The SQL ANSI-confi guration settings are set at three levels: server, database, and connec-

tion, as indicated in Table 19-9. The sp_confi gure system stored procedure has the User

Options setting that enables manipulation of serverwide ANSI settings, and it works across

databases. The ALTER DATABASE command can set the default database setting for ANSI.

Connection level settings are set with the SET command and override the default database

setting.

c19.indd 515c19.indd 515 7/30/2012 5:43:09 PM7/30/2012 5:43:09 PM

http://www.it-ebooks.info