Microsoft® SQL Server® 2012 Bible

518

Part V: Enterprise Data Management

ANSI Padding

The ANSI_PADDING connection setting affects only newly created columns. When set

to ON, data stored in char, varchar, binary, and varbinary data types retain any padded

zeros to the left of variable binary numbers, and any padded spaces to the right or left of

variable-length characters. When set to OFF, all leading and trailing blanks and zeros are

trimmed. The default database setting for ANSI_PADDINGis OFF.

To enable ANSI_PADDING in code at connection level, do the following:

SET ANSI_PADDING ON;

If SET ANSI_PADDING is not specifi ed, then the settings of ANSI_PADDING of the current

database applies. To enable ANSI_PADDING for the AdventureWorks2012 sample database

in code, do the following:

ALTER DATABASE AdventureWorks2012 SET ANSI_PADDING ON;

To enable ANSI_PADDING at the server level in code, do the following:

EXEC sp_configure 'user options', 16;

RECONFIGURE;

ANSI_PADDINGoption is deprecated and will always be ON in a future version of SQL Server.

ANSI Warnings

The ANSI_WARNINGS connection setting can handle ANSI errors and warnings such as

arithmetic overfl ow, divide-by-zero, and null values appearing in aggregate functions. The

default database setting for ANSI_WARNINGSis OFF. When this setting is OFF, no warn-

ings are raised when null values appear in aggregate functions, and null values are returned

when divide-by-zero occurs and overfl ow errors occur. When the setting is ON, query is

aborted and errors are raised when arithmetic overfl ow errors and divide-by-zero occur.

To set ANSI_WARNINGS in code at connection level, do the following:

SET ANSI_WARNINGS ON;

If SET ANSI_WARNINGS is not specifi ed, then the settings of ANSI_WARNINGS of the cur-

rent database apply. To enable ANSI_WARNINGS for the AdventureWorks2012 sample data-

base in code, do the following:

ALTER DATABASE AdventureWorks2012 SET ANSI_WARNINGS ON;

To enable ANSI_WARNINGS at the server level in code, do the following:

EXEC sp_configure 'user options', 8;

RECONFIGURE;

c19.indd 518c19.indd 518 7/30/2012 5:43:10 PM7/30/2012 5:43:10 PM

http://www.it-ebooks.info