Set Clauses

SET ANSI_NULL_DFLT_OFF (Transact-SQL)

This Topic Applies To: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Alters the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is true.

Syntax

-- Syntax for SQL Server and Azure SQL Database 

      SET ANSI_NULL_DFLT_OFF {ON | OFF} 

Remarks

This setting only affects the nullability of new columns when the nullability of the column is not specified in the CREATE TABLE and ALTER TABLE statements. By default, when SET ANSI_NULL_DFLT_OFF is ON, new columns that are created by using the ALTER TABLE and CREATE TABLE statements are NOT NULL if the nullability status of the column is not explicitly specified. SET ANSI_NULL_DFLT_OFF does not affect columns that are created by using an explicit NULL or NOT NULL.

Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON cannot be set ON at the same time. If one option is set ON, the other option is set OFF. Therefore, either ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON can be set ON, or both can be set OFF. If either option is ON, that setting (SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON) takes effect. If both options are set OFF, SQL Server uses the value of the is_ansi_null_default_on column in the sys.databases catalog view.

For a more reliable operation of Transact-SQL scripts that are used in databases with different nullability settings, it is better to always specify NULL or NOT NULL in CREATE TABLE and ALTER TABLE statements.

The setting of SET ANSI_NULL_DFLT_OFF is set at execute or run time and not at parse time.

To view the current setting for this setting, run the following query.

DECLARE @ANSI_NULL_DFLT_OFF VARCHAR(3) = 'OFF'; 

IF ( (2048 & @@OPTIONS) = 2048 ) SET @ANSI_NULL_DFLT_OFF = 'ON'; 

SELECT @ANSI_NULL_DFLT_OFF AS ANSI_NULL_DFLT_OFF; 

 

Permissions

Requires membership in the public role.

           

Examples

The following example shows the effects of SET ANSI_NULL_DFLT_OFF with both settings for the ANSI null default database option.

USE AdventureWorks2012; 

GO 

 

-- Set the 'ANSI null default' database option to true by executing  

-- ALTER DATABASE. 

GO 

ALTER DATABASE AdventureWorks2012 SET ANSI_NULL_DEFAULT ON; 

GO 

-- Create table t1. 

CREATE TABLE t1 (a TINYINT); 

GO 

-- NULL INSERT should succeed. 

INSERT INTO t1 (a) VALUES (NULL); 

GO 

 

-- SET ANSI_NULL_DFLT_OFF to ON and create table t2. 

SET ANSI_NULL_DFLT_OFF ON; 

GO 

CREATE TABLE t2 (a TINYINT); 

GO  

-- NULL INSERT should fail. 

INSERT INTO t2 (a) VALUES (NULL); 

GO 

 

-- SET ANSI_NULL_DFLT_OFF to OFF and create table t3. 

SET ANSI_NULL_DFLT_OFF OFF; 

GO 

CREATE TABLE t3 (a TINYINT) ; 

GO  

-- NULL INSERT should succeed. 

INSERT INTO t3 (a) VALUES (NULL); 

GO 

 

-- This illustrates the effect of having both the database 

-- Option and SET option disabled. 

-- Set the 'ANSI null default' database option to false. 

ALTER DATABASE AdventureWorks2012 SET ANSI_NULL_DEFAULT OFF; 

GO 

-- Create table t4. 

CREATE TABLE t4 (a tinyint) ; 

GO  

-- NULL INSERT should fail. 

INSERT INTO t4 (a) VALUES (null); 

GO 

 

-- SET ANSI_NULL_DFLT_OFF to ON and create table t5. 

SET ANSI_NULL_DFLT_OFF ON; 

GO 

CREATE TABLE t5 (a tinyint); 

GO  

-- NULL insert should fail. 

INSERT INTO t5 (a) VALUES (null); 

GO 

 

-- SET ANSI_NULL_DFLT_OFF to OFF and create table t6. 

SET ANSI_NULL_DFLT_OFF OFF; 

GO 

CREATE TABLE t6 (a tinyint);  

GO  

-- NULL insert should fail. 

INSERT INTO t6 (a) VALUES (null); 

GO 

 

-- Drop tables t1 through t6. 

DROP TABLE t1, t2, t3, t4, t5, t6; 

SET ANSI_NULL_DFLT_ON (Transact-SQL)

Applies To: SQL Server 2016 Preview

THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Modifies the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is false.

Syntax

-- Syntax for SQL Server and Azure SQL Database 

SET ANSI_NULL_DFLT_ON {ON | OFF} 

Remarks

This setting only affects the nullability of new columns when the nullability of the column is not specified in the CREATE TABLE and ALTER TABLE statements. When SET ANSI_NULL_DFLT_ON is ON, new columns created by using the ALTER TABLE and CREATE TABLE statements allow null values if the nullability status of the column is not explicitly specified.

SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.

Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON cannot be set ON at the same time. If one option is set ON, the other option is set OFF. Therefore, either ANSI_NULL_DFLT_OFF or ANSI_NULL_DFLT_ON can be set ON, or both can be set OFF. If either option is ON, that setting (SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON) takes effect. If both options are set OFF, SQL Server uses the value of the is_ansi_null_default_on column in the sys.databases catalog view.

For a more reliable operation of Transact-SQL scripts that are used in databases with different nullability settings, it is better to specify NULL or NOT NULL in CREATE TABLE and ALTER TABLE statements.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_NULL_DFLT_ON to ON when connecting. The default for SET ANSI_NULL_DFLT_ON is OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULL_DFLT_ON is enabled.

The setting of SET ANSI_NULL_DFLT_ON is set at execute or run time and not at parse time.

The setting of SET ANSI_NULL_DFLT_ON does not apply when tables are created using the SELECT INTO statement.

To view the current setting for this setting, run the following query.

DECLARE @ANSI_NULL_DFLT_ON VARCHAR(3) = 'OFF'; 

IF ( (1024 & @@OPTIONS) = 1024 ) SET @ANSI_NULL_DFLT_ON = 'ON'; 

SELECT @ANSI_NULL_DFLT_ON AS ANSI_NULL_DFLT_ON; 

 

Permissions

Requires membership in the public role.

SET ANSI_NULLS (Transact-SQL)

THIS TOPIC APPLIES TO:SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server 2016.

Important

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Syntax

-- Syntax for SQL Server 

SET ANSI_NULLS { ON | OFF } 

Remarks

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non null values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <>NULL returns the rows that have non null values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies.

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS should be set to ON for executing distributed queries.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

Note

ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to an instance of SQL Server.

The default for SET ANSI_NULLS is OFF.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

The setting of SET ANSI_NULLS is set at execute or run time and not at parse time.

To view the current setting for this setting, run the following query.

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF'; 

IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON'; 

SELECT @ANSI_NULLS AS ANSI_NULLS; 

 

Permissions

Requires membership in the public role.

Examples

The following example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL and nonnull values in a table. The example also shows that IS NULL is not affected by the SET ANSI_NULLS setting.

-- Create table t1 and insert values. 

CREATE TABLE dbo.t1 (a INT NULL); 

INSERT INTO dbo.t1 values (NULL),(0),(1); 

GO 

 

-- Print message and perform SELECT statements. 

PRINT 'Testing default setting'; 

DECLARE @varname int;  

SET @varname = NULL; 

 

SELECT a 

FROM t1  

WHERE a = @varname; 

 

SELECT a  

FROM t1  

WHERE a <> @varname; 

 

SELECT a  

FROM t1  

WHERE a IS NULL; 

GO 

 

-- SET ANSI_NULLS to ON and test. 

PRINT 'Testing ANSI_NULLS ON'; 

SET ANSI_NULLS ON; 

GO 

DECLARE @varname int; 

SET @varname = NULL 

 

SELECT a  

FROM t1  

WHERE a = @varname; 

 

SELECT a  

FROM t1  

WHERE a <> @varname; 

 

SELECT a  

FROM t1  

WHERE a IS NULL; 

GO 

 

-- SET ANSI_NULLS to OFF and test. 

PRINT 'Testing SET ANSI_NULLS OFF'; 

SET ANSI_NULLS OFF; 

GO 

DECLARE @varname int; 

SET @varname = NULL; 

SELECT a  

FROM t1  

WHERE a = @varname; 

 

SELECT a  

FROM t1  

WHERE a <> @varname; 

 

SELECT a  

FROM t1  

WHERE a IS NULL; 

GO 

 

-- Drop table t1. 

DROP TABLE dbo.t1;

SET ANSI_PADDING (Transact-SQL)

THIS TOPIC APPLIES TO:SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

Syntax

-- Syntax for SQL Server 

SET ANSI_PADDING { ON | OFF } 

Remarks

Columns defined with char, varchar, binary, and varbinary data types have a defined size.

This setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.

Note

We recommend that ANSI_PADDING always be set to ON.

The following table shows the effects of the SET ANSI_PADDING setting when values are inserted into columns with char, varchar, binary, andvarbinary data types.

Setting	char(n) NOT NULL or binary(n) NOT NULL	char(n) NULL or binary(n) NULL	varchar(n) or varbinary(n)
ON	Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the column.	Follows same rules as for char(n) or binary (n)NOT NULL when SET ANSI_PADDING is ON.	Trailing blanks in character values inserted into varchar columns are not trimmed. Trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.
OFF	Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the column.	Follows same rules as for varchar or varbinary when SET ANSI_PADDING is OFF.	Trailing blanks in character values inserted into a varchar column are trimmed. Trailing zeros in binary values inserted into a varbinary column are trimmed.
Note
When padded, char columns are padded with blanks, and binary columns are padded with zeros. When trimmed, char columns have the trailing blanks trimmed, and binary columns have the trailing zeros trimmed.

SET ANSI_PADDING must be ON when you are creating or changing indexes on computed columns or indexed views. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

The default for SET ANSI_PADDING is ON.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_PADDING to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties set in the application before connecting. The default for SET ANSI_PADDING is OFF for connections from DB-Library applications.

The SET ANSI_PADDING setting does not affect the nchar, nvarchar, ntext, text, image, varbinary (max), varchar(max),

and nvarchar(max) data types. They always display the SET ANSI_PADDING ON behavior. This means trailing spaces and zeros are not trimmed.

When SET ANSI_DEFAULTS is ON, SET ANSI_PADDING is enabled.

The setting of SET ANSI_PADDING is set at execute or run time and not at parse time.

To view the current setting for this setting, run the following query.

DECLARE @ANSI_PADDING VARCHAR(3) = 'OFF'; 

IF ( (16 & @@OPTIONS) = 16 ) SET @ANSI_PADDING = 'ON'; 

SELECT @ANSI_PADDING AS ANSI_PADDING; 

 

Permissions

Requires membership in the public role.

Examples

The following example shows how the setting affects each of these data types.

PRINT 'Testing with ANSI_PADDING ON' 

SET ANSI_PADDING ON; 

GO 

 

CREATE TABLE t1 ( 

   charcol CHAR(16) NULL,  

   varcharcol VARCHAR(16) NULL,  

   varbinarycol VARBINARY(8) 

); 

GO 

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee); 

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00); 

 

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<', 

   varbinarycol 

FROM t1; 

GO 

 

PRINT 'Testing with ANSI_PADDING OFF'; 

SET ANSI_PADDING OFF; 

GO 

 

CREATE TABLE t2 ( 

   charcol CHAR(16) NULL,  

   varcharcol VARCHAR(16) NULL,  

   varbinarycol VARBINARY(8) 

); 

GO 

INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee); 

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00); 

 

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<', 

   varbinarycol 

FROM t2; 

GO 

 

DROP TABLE t1; 

DROP TABLE t2;

SET ANSI_WARNINGS (Transact-SQL)

THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Specifies ISO standard behavior for several error conditions.

Syntax

-- Syntax for SQL Server and Azure SQL Database 

 

SET ANSI_WARNINGS { ON | OFF } 

SET ANSI_WARNINGS affects the following conditions:

Warning: Null value is eliminated by an aggregate or other SET operation

When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.

Division by zero occurred.

When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned.

DECLARE @sales TABLE (ItemID INT, TotalQty INT, PricePerCase MONEY, TotalPrice MONEY)

INSERT INTO @sales (ItemID, TotalQty, TotalPrice) SELECT 1, 0, 0

UPDATE @sales SET

 PricePerCase = TotalPrice / TotalQty

/*

Msg 8134, Level 16, State 1, Line 4

Divide by zero error encountered.

The statement has been terminated.

*/

But setting ANSI_WARNINGS to OFF and ARITHABORT to OFF this error can be avoided.

SET ARITHABORT OFF;

GO

SET ANSI_WARNINGS OFF;

GO

DECLARE @sales TABLE (ItemID INT, TotalQty INT, PricePerCase MONEY, TotalPrice MONEY)

INSERT INTO @sales (ItemID, TotalQty, TotalPrice) SELECT 1, 0, 0

UPDATE @sales SET

 PricePerCase = TotalPrice / TotalQty

Warning: Division by zero occurred.

SELECT * FROM @Sales

ItemID      TotalQty    PricePerCase          TotalPrice

----------- ----------- --------------------- ---------------------

1           0           NULL                  0.00

In case of a divide-by-zero error, NULL value will be returned. The error is changed to a warning and the execution can continue. In the above example, PricePerCase will be updated with NULL.

The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column.

Value exceeds the maximum size of the column

IF OBJECT_ID ('test_table') IS NOT NULL

drop table test_table

create table test_table

(

id int check (id>100),

name varchar(20)

)

set ansi_warnings ON

insert into test_table values (155,'this is test to check the truncation of data in case of ANSI_Warnings off')

Go

Msg 8152, Level 16, State 14, Line 2

String or binary data would be truncated.

The statement has been terminated.

set ansi_warnings OFF

insert into test_table values (155,'this is test to check the truncation of data in case of ANSI_Warnings off')

Go

select * from test_table

id          name

----------- --------------------

155         this is test to chec

(1 row(s) affected)

If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.

Note: When truncation occurs in any conversion to or from binary or varbinary data, no warning or error is issued, regardless of SET options.

ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char (3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

IF OBJECT_ID ('test_table') IS NOT NULL

drop table test_table

create table test_table

(

id int,

name varchar(8)

)

declare

@name varchar(4)

set @name = 'test truncation'

select @name

insert into test_table values(12,@name)

select * from test_table

----

test

id          name

----------- --------

12          test

(1 row(s) affected)

You can use the user options option of sp_configure to set the default setting for ANSI_WARNINGS for all connections to the server. For more information, see sp_configure (Transact-SQL).

SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

SQL Server includes the ANSI_WARNINGS database option. This is equivalent to SET ANSI_WARNINGS. When SET ANSI_WARNINGS is ON, errors or warnings are raised in divide-by-zero, string too large for database column, and other similar errors. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised. The default value in the model database for SET ANSI_WARNINGS is OFF. If not specified, the setting of ANSI_WARNINGS applies. If SET ANSI_WARNINGS is OFF, SQL Server uses the value of the is_ansi_warnings_on column in the sys.databases catalog view.

ANSI_WARNINGS should be set to ON for executing distributed queries.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_WARNINGS to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, set in the application before connecting. The default for SET ANSI_WARNINGS is OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is ON, SET ANSI_WARNINGS is enabled.

The setting of SET ANSI_WARNINGS is set at execute or run time and not at parse time.

If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

To view the current setting for this setting, run the following query.

DECLARE @ANSI_WARN VARCHAR(3) = 'OFF'; 

IF ( (8 & @@OPTIONS) = 8 ) SET @ANSI_WARN = 'ON'; 

SELECT @ANSI_WARN AS ANSI_WARNINGS; 

Permissions

Requires membership in the public role.

Connection Level or Database Level

The default setting of ANSI_WARNINGS is OFF. This option can be set on a database level or on connection level. If a setting is specified in the connection level, it will override the setting specified at the database level.

For example, if your database level setting of ANSI_WARNINGS is ON all queries that you run on the database will run with ANSI_WARNINGS set to ON by default. If you specify a different option in your query, the option specified in the query will take effect.

You can query the database level setting of ANSI_WARNINGS by running the following queries.

-- SQL Server 2005/2008

-- if you are running from the current database

SELECT

    is_ansi_warnings_on

FROM sys.databases

WHERE database_id = DB_ID()

-- SQL Server 2005/2008

SELECT

    is_ansi_warnings_on

FROM sys.databases

WHERE name = 'dbname'

-- SQL Server 2000/2005/2008

SELECT DATABASEPROPERTYEX('dbname','IsAnsiWarningsEnabled')

Changing ANSI_WARNINGS setting of a database

You can use any of the following options to change the setting of ANSI_WARNINGS on a specific database.

-- Using ALTER DATABASE

ALTER DATABASE xmlworkshop SET ANSI_WARNINGS ON;

GO

-- Using sp_dboption

EXEC sp_dboption 'xmlworkshop', 'ANSI_WARNINGS', 'ON';

GO

Note: It is not recommended to alter these settings at the database level. Always try to alter the setting for your current session if needed. ANSI_WARNINGS needs to be set to ON to have certain SQL Server features like XML Indexes, Indexed views, indexes on computed columns etc. So change it at the database level only if you are very sure of what you are doing. If not, always use it at the session level (by setting the required option as part of the batch query).

ARITHABORT:

Terminates a query when an overflow or divide-by-zero error occurs during query execution.

Before we examine ARITHABORT, let us look at a TSQL sample script.

DECLARE @t1 TINYINT, @t2 TINYINT

SELECT @t1 = 120, @t2 = 190

PRINT 'Performing Arith Operation 1'

SELECT @t1 + @t2

PRINT 'Performing Arith Operation 2'

SELECT @t1 - @t2

Can you predict the output of this query?

Both SELECT statements given above will generate an 'Arithmetic overflow' error. When you perform an Arithmetic operation on two or more TINYINT values, the result will also be a TINYINT. The range of values a TINYINT column or variable can store is 0 to 255. The first operation returns a value that is greater than 255 and the second operation results in a negative value. TINYINT cannot store any of those values and hence both operations will generate an error.

Let us run the code again and see what is the output:

DECLARE @t1 TINYINT, @t2 TINYINT

SELECT @t1 = 120, @t2 = 190

PRINT 'Performing Arith Operation 1'

SELECT @t1 + @t2

PRINT 'Performing Arith Operation 2'

SELECT @t1 - @t2

/*

Performing Arith Operation 1

Msg 8115, Level 16, State 2, Line 5

Arithmetic overflow error converting expression to data type tinyint.

Performing Arith Operation 2

Msg 8115, Level 16, State 2, Line 8

Arithmetic overflow error converting expression to data type tinyint.

*/

Note that the first SELECT operation generated an error. SQL Server aborts the statement and continue executing the next statement. The second statement generates an error as well and the statement will be aborted again.

ARITHABORT controls the behavior of SQL Server, when it encounters an overflow error or division by 0 error. The setting of ANSI_WARNINGS affects the behavior suggested by ARITHABORT. We examined ANSI_WARNINGS in a previous post.

Let us see the result of operations with different combination of settings for ANSI_WARNINGS and ARITHABORT.

ARITHABORT = ON and ANSI_WARNINGS = ON

When ARITHABORT and ANSI_WARNINGS are ON, SQL Server will abort a TSQL statement that generates a 'division by zero' or 'arithmetic overflow' error and continue executing the next statement.

SET ARITHABORT ON;

SET ANSI_WARNINGS ON;

PRINT 'Generating overflow error'

SELECT CAST(-1 AS TINYINT) AS Result1

PRINT 'Generating division by zero error'

SELECT 10/0 AS Result2

/*

Generating overflow error

Result1

-------

Msg 220, Level 16, State 2, Line 5

Arithmetic overflow error for data type tinyint, value = -1.

Generating division by zero error

Result2

-----------

Msg 8134, Level 16, State 1, Line 8

Divide by zero error encountered.

*/

ARITHABORT = OFF and ANSI_WARNINGS = OFF

When ARITHABORT and ANSI_WARNINGS are OFF, SQL Server does not generate an error when one of the conditions mentioned earlier occurs. Instead, a warning message is generated. SQL Server returns NULL as the result of the operation and continue executing the next statement.

SET ANSI_WARNINGS OFF;

SET ARITHABORT OFF;

PRINT 'Generating overflow error'

SELECT CAST(-1 AS TINYINT) AS Result1

PRINT 'Generating division by zero error'

SELECT 10/0 AS Result2

/*

Generating overflow error

Result1

-------

NULL

Arithmetic overflow occurred.

(1 row(s) affected)

Generating division by zero error

Result2

-----------

NULL

Division by zero occurred.

(1 row(s) affected)

*/

ARITHABORT = ON and ANSI_WARNINGS = OFF

When ARITHABORT is ON and ANSI_WARNINGS is OFF, SQL Server generates an error in any of the conditions mentioned previously. In this case, SQL Server will abort the entire batch (not just the current statement).

SET ANSI_WARNINGS OFF;

SET ARITHABORT ON;

PRINT 'Generating overflow error'

SELECT CAST(-1 AS TINYINT) AS Result1

PRINT 'Generating division by zero error'

SELECT 10/0 AS Result2

/*

Generating overflow error

Result1

-------

Msg 220, Level 16, State 2, Line 5

Arithmetic overflow error for data type tinyint, value = -1.

*/

Note that the second query is not executed. SQL Server aborted the entire batch after it encountered the first error. None of the statements after the first error is executed.

ARITHABORT = OFF and ANSI_WARNINGS = ON

When ARITHABORT is OFF and ANSI_WARNINGS is ON, the behavior is the same as the first example where both ARITHABORT and ANSI_WARNINGS were ON. SQL Server generates an error, terminates the current statement and continue executing the next statement.

SET ANSI_WARNINGS ON;

SET ARITHABORT OFF;

PRINT 'Generating overflow error'

SELECT CAST(-1 AS TINYINT) AS Result1

PRINT 'Generating division by zero error'

SELECT 10/0 AS Result2

/*

Generating overflow error

Result1

-------

Msg 220, Level 16, State 2, Line 5

Arithmetic overflow error for data type tinyint, value = -1.

Generating division by zero error

Result2

-----------

Msg 8134, Level 16, State 1, Line 8

Divide by zero error encountered.

*/

What is the recommended setting?

The recommended setting of ARITHABORT is ON. ARITHABORT should be ON when running CREATE, INSERT, UPDATE, DELETE statements on tables having computed columns or indexed views. ARITHABORT should be ON while using the XML data type methods.

How to check the current setting of ARITHABORT?

You can check the current setting of ARITHABORT by calling the system function SESSIONPROPERTY. Here is an example:

SET ARITHABORT ON;

SELECT SESSIONPROPERTY('ARITHABORT') AS ArithAbortSetting

/*

ArithAbortSetting

-----------------

1

*/

SET ARITHABORT OFF;

SELECT SESSIONPROPERTY('ARITHABORT') AS ArithAbortSetting

/*

ArithAbortSetting

-----------------

0

*/

If neither SET ARITHABORT nor SET ARITHIGNORE is set, SQL Server returns NULL and returns a warning message after the query is executed.

To view the current setting for this setting, run the following query.

DECLARE @ARITHABORT VARCHAR(3) = 'OFF'; 

IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON'; 

SELECT @ARITHABORT AS ARITHABORT; 

 

Permissions

Requires membership in the public role.

SET ARITHIGNORE (Transact-SQL)

THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Controls whether error messages are returned from overflow or divide-by-zero errors during a query.

Syntax

-- Syntax for SQL Server and Azure SQL Database 

   

SET ARITHIGNORE { ON | OFF } 

[ ; ] 

Remarks

The SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting.

The SET ARITHABORT setting can be used to determine whether the query is terminated. This setting does not affect errors occurring during INSERT, UPDATE, and DELETE statements.

If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

The setting of SET ARITHIGNORE is set at execute or run time and not at parse time.

To view the current setting for this setting, run the following query.

DECLARE @ARITHIGNORE VARCHAR(3) = 'OFF'; 

IF ( (128 & @@OPTIONS) = 128 ) SET @ARITHIGNORE = 'ON'; 

SELECT @ARITHIGNORE AS ARITHIGNORE; 

 

Permissions

Requires membership in the public role.

SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

THIS TOPIC APPLIES TO:SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Controls whether concatenation results are treated as null or empty string values.

Important

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Syntax

-- Syntax for SQL Server 

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }  

Remarks

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULLyields NULL.

When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

If SET CONCAT_NULL_YIELDS_NULL is not specified, the setting of  the CONCAT_NULL_YIELDS_NULL database option applies.

Note

SET CONCAT_NULL_YIELDS_NULL is the same setting as the CONCAT_NULL_YIELDS_NULL setting of ALTER DATABASE.

The setting of SET CONCAT_NULL_YIELDS_NULL is set at execute or run time and not at parse time.

SET CONCAT_NULL_YIELDS_NULL must be ON when you are creating or changing indexes on computed columns or indexed views. If SET CONCAT_NULL_YIELDS_NULL is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

When CONCAT_NULL_YIELDS_NULL is set to OFF, string concatenation across server boundaries cannot occur.

To view the current setting for this setting, run the following query.

DECLARE @CONCAT_NULL_YIELDS_NULL VARCHAR(3) = 'OFF'; 

IF ( (4096 & @@OPTIONS) = 4096 ) SET @CONCAT_NULL_YIELDS_NULL = 'ON'; 

SELECT @CONCAT_NULL_YIELDS_NULL AS CONCAT_NULL_YIELDS_NULL; 

 

Examples

The following example showing using both SET CONCAT_NULL_YIELDS_NULL settings.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON'; 

GO 

-- SET CONCAT_NULL_YIELDS_NULL ON and testing. 

SET CONCAT_NULL_YIELDS_NULL ON; 

GO 

SELECT 'abc' + NULL;    --Results is NULL

GO 

 

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing. 

SET CONCAT_NULL_YIELDS_NULL OFF; 

GO 

SELECT 'abc' + NULL;     --Results is abc

GO 

Checking at session Level

SELECT concat_null_yields_null

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID;

concat_null_yields_null

-----------------------

0

Checking at Database Level

select is_concat_null_yields_null_on

from sys.databases

where name ='test'

is_concat_null_yields_null_on

-----------------------------

0

However, in SQL Server 2012 the behavior of the ‘+’ operator remains the same but the behavior of function CONCAT is bit different and it totally ignores this value. Here is the example of it.

--- SQL Server 2012 and onwards

-- SET CONCAT_NULL_YIELDS_NULL ON

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT CONCAT('Some Value', NULL) AS SS2012;

GO

O/P: Some Value     --The CONCAT consider NULL as empty string

-- SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT CONCAT('Some Value', NULL) AS SS2012;

GO

O/P: Some Value

I assume this may be intentional as in future the value of CONCAT_NULL_YIELDS_NULL will be always ON. Irespectively I find it very interesting how CONCAT function works with NULLs.

SET CONTEXT_INFO (Transact-SQL)

Associates up to 128 bytes of binary information with the current session or connection.

Applies to: SQL Server (SQL Server 2008 through current version).

Syntax

SET CONTEXT_INFO { binary_str | @binary_var } 

Arguments

binary_str

Is a binary constant, or a constant that is implicitly convertible to binary, to associate with the current session or connection.

@ binary_var

Is a varbinary or binary variable holding a context value to associate with the current session or connection.

Remarks

The preferred way to retrieve the context information for the current session is to use the CONTEXT_INFO function. Session context information is also stored in the context_info columns in the following system views:

sys.dm_exec_requests

sys.dm_exec_sessions

sys.sysprocesses

SET CONTEXT_INFO cannot be specified in a user-defined function. You cannot supply a null value to SET CONTEXT_INFO because the views holding the values do not allow for null values.

SET CONTEXT_INFO does not accept expressions other than constants or variable names. To set the context information to the result of a function call, you must first include the result of the function call in a binary or varbinary variable.

When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger is completed.

Examples

A. Setting context information by using a constant

The following example demonstrates SET CONTEXT_INFO by setting the value and displaying the results. Note that querying sys.dm_exec_sessionsrequires SELECT and VIEW SERVER STATE permissions, whereas using the CONTEXT_INFO function does not.

SET CONTEXT_INFO 0x01010101; 

GO 

SELECT context_info  

FROM sys.dm_exec_sessions 

WHERE session_id = @@SPID; 

GO 

B. Setting context information by using a function

The following example demonstrates using the output of a function to set the context value, where the value from the function must be first placed in a binary variable.

DECLARE @BinVar varbinary(128); 

SET @BinVar = CAST(REPLICATE( 0x20, 128 ) AS varbinary(128) ); 

SET CONTEXT_INFO @BinVar; 

 

SELECT CONTEXT_INFO() AS MyContextInfo; 

GO