Microsoft® SQL Server® 2012 Bible

520

Part V: Enterprise Data Management

Numeric Round Abort

The NUMERIC_ROUNDABORT connection setting controls the behavior of numeric

decimal-precision-rounding errors in process. When NUMERIC_ROUNDABORT is set to

ON and ARITHABORT is set to ON, an error is generated, and no result is returned if the

numeric-decimal precision is lost in an expression value. Loss of numeric-decimal preci-

sion can occur when a value with fi xed precision is stored in a column or variable with less

precision. If ARITHABORT is set to OFF and NUMERIC_ROUNDABORT is set to ON, warning

is returned and null is returned. When NUMERIC_ROUNDABORT is set to OFF, the process

proceeds without errors or warnings, and the result is rounded down to the precision of

the object in which the number is stored. The default database setting for NUMERIC_

ROUNDABORT is OFF.

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

SET NUMERIC_ROUNDABORT ON;

If NUMERIC_ROUNDABORT is not specifi ed, then the settings of the current database apply.

To enable NUMERIC_ROUNDABORT for the AdventureWorks2012 sample database in code, do

the following:

ALTER DATABASE AdventureWorks2012 SET NUMERIC_ROUNDABORT ON;

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

EXEC sp_configure 'user options', 8192;

RECONFIGURE;

Concatenation Null Yields Null

The CONCAT_NULL_YIELDS_NULLsetting controls the behavior of the result when concat-

enating a string with a null. When set to ON, any string concatenated with a null results

in a null. When set to OFF, any string concatenated with a null results in the original

string, ignoring the null. The default database setting for CONCAT_NULL_YIELDS_NULL

is OFF.

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

SET CONCAT_NULL_YIELDS_NULL ON;

If CONCAT_NULL_YIELDS_NULL is not specifi ed, then the settings of the current database

apply. To enable CONCAT_NULL_YIELDS_NULL for the AdventureWorks2012 sample data-

base in code, do the following:

ALTER DATABASE AdventureWorks2012 SET CONCAT_NULL_YIELDS_NULL ON;

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

EXEC sp_configure 'user options', 4096;

RECONFIGURE;

c19.indd 520c19.indd 520 7/30/2012 5:43:11 PM7/30/2012 5:43:11 PM

http://www.it-ebooks.info