In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.

In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.

SET CONCAT_NULL_YIELDS_NULL

The SET CONCAT_NULL_YIELDS_NULL option controls if the string concatenation results with the NULL value are treated as null or empty string values. The T-SQL statement that can be used to control the CONCAT_NULL_YIELDS_NULL option is:

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

When you set the CONCAT_NULL_YIELDS_NULL option to ON, the result of concatenating a null value with a string is the NULL value. If you set the CONCAT_NULL_YIELDS_NULL option to OFF, the result of concatenating a null value with a string will be the string itself, treating the null value as an empty string.

In order to execute a distributed query or to create or change indexes on computed columns or indexed views, the SET CONCAT_NULL_YIELDS_NULL option should be set to ON. Otherwise, the operation will fail and the SQL Server will return an error that lists all SET options that violate the required values. The SET CONCAT_NULL_YIELDS_NULL setting is set at run time, not at the parse time.

Let us see how the CONCAT_NULL_YIELDS_NULL setting work. If we try to concatenate the below string with NULL value after setting the CONCAT_NULL_YIELDS_NULL option to ON:

The concatenation result is NULL as shown below:

Concatenating the same string with the NULL value, but this time after setting the CONCAT_NULL_YIELDS_NULL option to OFF:

The concatenation result is the same string followed by blank, treating the NULL value as empty string as shown below:

SET NUMERIC_ROUNDABORT

The NUMERIC_ROUNDABORT option identifies the level of error reporting that is generated when rounding in an expression causes a loss of precision. The T-SQL syntax used to control the NUMERIC_ROUNDABORT option is like below:

SET NUMERIC_ROUNDABORT { ON | OFF }

When setting the NUMERIC_ROUNDABORT option to ON, an error will be generated if a loss of precision occurs in an expression. If you set the NUMERIC_ROUNDABORT option to OFF, losses of precision will not generate error messages and the result will be rounded to fit the precision of the column or the variable that will store the result. The precision loss can occur when you try to store a value with a specific precision in a column or variable with a precision less than this value. The NUMERIC_ROUNDABORT option specifies how the SQL Server will react in this situation.

Recall the SET ARITHABORT option described in the previous article that terminates the query when an overflow or divide-by-zero error occurs during the execution of the query. If you set the ARITHABORT option to ON:

• Setting the NUMERIC_ROUNDABORT to ON, an error message will be generated and no result will be returned from the query.
• Setting the NUMERIC_ROUNDABORT to OFF, no error or warning messages will be generated and the query will succeed returning a rounded result that fits the destination column or variable precision.

If you set the ARITHABORT option to OFF:

• Setting the NUMERIC_ROUNDABORT to ON, a warning message will be generated and NULL value will be returned from the query.
• Setting the NUMERIC_ROUNDABORT to OFF, no error or warning messages will be generated and the query will succeed returning a rounded result that fits the destination column or variable precision.

Again, to be able to execute the distributed queries or to be able to create or change indexes on computed columns or indexed views, the SET NUMERIC_ROUNDABORT option must be set to ON. Otherwise, the operation will fail and the SQL Server will return an error that lists all SET options that violate the required values. The SET NUMERIC_ROUNDABORT setting is also set at run time, not at parse time.

Let us see how the NUMERIC_ROUNDABORT setting work. If we try to add two decimal values with precision equal to 3 and store the result to a decimal variable with precision equal to 2, after setting both NUMERIC_ROUNDABORT and ARITHABORT options to ON:

The query will fail and an error message will be generated indicating that an overflow issue is detected as shown below:

Trying to add the same two decimal values with precision equal to 3 and store the result to a decimal variable with precision equal to 2, after setting the NUMERIC_ROUNDABORT option to OFF and the ARITHABORT options to ON:

No error or waning message will be generated:

And the query will succeed, with the result rounded to fit the destination variable precision size, which is 2 in our case, as shown below:

Again, if we try to add the same two decimal values with precision equal to 3 and store the result to a decimal variable with precision equal to 2, after setting the NUMERIC_ROUNDABORT option to ON and the ARITHABORT options to OFF:

The query will not fail, and a warning message will be generated indicating that an arithmetic overflow issue detected:

And the result will be a NULL value returned from the query:

SET QUOTED_IDENTIFIER

The QUOTED_IDENTIFIER option controls if the SQL Server will follow the ISO rules regarding the quotation mark delimiting identifiers and literal strings. The Identifiers that are delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters that are not generally allowed by the Transact-SQL syntax rules for the identifiers. The T-SQL syntax used to control the QUOTED_IDENTIFIER option is like the below:

SET QUOTED_IDENTIFIER { ON | OFF }

If you set the QUOTED_IDENTIFIER option to ON, which is the default setting, any set of characters that are delimited by double quotation marks will be treated as T-SQL identifiers, such as table name, column name or stored procedure name, and any set of characters that are delimited by single quotation marks will be treated as literals. The single quotation mark can be represented by two single quotation marks (“), if that single quotation mark is part of the literal string. To be able to use the reserved keywords for object names in the database, you should set the QUOTED_IDENTIFIER option to ON.

Setting the QUOTED_IDENTIFIER option to OFF, any set of characters that are delimited by double quotation marks or delimited by single quotation marks will be treated as literal. Moreover, the identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. If the literal string is delimited by double quotation marks, the string can contain single quotation marks within the string.

To be able to Create or change indexes on computed columns or indexed views, the SET QUOTED_IDENTIFIER option must be set to ON. Otherwise, the operation will fail and the SQL Server will return an error with all SET options that violate the required values. The SET NUMERIC_ROUNDABORT setting is set at parse time, which means that the SET QUOTED_IDENTIFIER statement will take effect before any statements are executed.

Now, we will see how the QUOTED_IDENTIFIER setting work. If you try to create the below table, using the UPDATE reserved keyword as a table name and the UNIQUE and CREATE reserved keywords as column names, after setting the QUOTED_IDENTIFIER option to OFF:

The result will show that the query is failed, due to using reserved keywords delimited by double quotations as identifier names, as shown below:

Trying to create the same table, using the UPDATE reserved keyword as a table name and the UNIQUE and CREATE reserved keywords as column names, after setting the QUOTED_IDENTIFIER option to ON:

This operation is completed successfully now, even though you are using reserved keywords delimited by double quotations as identifier names because the QUOTED_IDENTIFIER option is set to ON, as shown below:

If you try to define the below literal strings using the combination of single and double quotation marks, after setting the QUOTED_IDENTIFIER option to OFF:

The query will succeed, treating the outer double quotation and single quotation marks as literal string delimiters. Defining two single quotation marks in the literal string, the first one will be treated as one string delimiter and the second one will be treated as part of the literal string, and defining two double quotation marks in the literal string, the first one will be treated as one string delimiter and the second one will be treated as part of the literal string.

The different cases are clear in the result shown below:

Trying to define the same literal strings using the combination of single and double quotation marks, after setting the QUOTED_IDENTIFIER option to ON:

The first string and the last two strings that contain the double quotation marks as string delimiter will fail, as the double quotation delimiter can be used only to define the identifiers when setting the QUOTED_IDENTIFIER option to ON, as shown in the result below:

SET NOCOUNT

The NOCOUNT option stops displaying the number of rows affected by a T-SQL statement or stored procedure from being returned as part of the result set in the Messages tab. The T-SQL syntax that is used to control the NOCOUNT option is shown below:

SET NOCOUNT { ON | OFF }

If you set the NOCOUNT option to ON, the number of affected rows will not be returned. Setting the NOCOUNT option to OFF, the number of affected rows will be returned. The setting of the NOCOUNT option will not affect the @@ROWCOUNT function, that will be updated in all cases even if the NOCOUNT option is set to ON.

Setting the NOCOUNT option to ON helps significantly in enhancing the performance of stored procedures that contain loops and many statements that don’t return too much actual data, by reducing the network traffic caused by writing such type of messages. The SET NOCOUNT option takes effect at a run time and not at a parse time.

Let us see how the SET NOCOUNT option simply works. If we try to run the below simple SELECT statement after setting the NOCOUNT option to OFF:

The number of returned rows is displayed in the Messages tab as shown below:

Running the same SELECT statement after setting the NOCOUNT option to ON:

The number of returned rows will not be displayed in the Messages tab, and only a simple message indicating that the command is executed successfully will be displayed, as shown below:

SET XACT_ABORT

By default, T-SQL statements that are executed within the BEGIN TRANSACTION and COMMIT TRANSACTION commands will not be rolled back automatically, as one batch, in the case of any failure. The XACT_ABORT option controls if the T-SQL statements inside the current transaction will be rolled back automatically if any of these T-SQL statements raises a run-time error. The T-SQL syntax used to control the XACT_ABORT option is like the below:

SET XACT_ABORT { ON | OFF }

The XACT_ABORT option is OFF by default. If you set the XACT_ABORT option to ON, the entire transaction will be terminated and the changes will be rolled back if any T-SQL statement inside that transaction raises an exception, depending on the severity of the generated error. Compile errors, such as syntax errors, will not be affected by XACT_ABORT option value.

Setting the XACT_ABORT option to OFF, only the Transact-SQL statement that raised the error will be rolled back and the transaction will continue processing with no issue. Again, depending on the severity of the generated error, the transaction may be completely rolled back regardless of the XACT_ABORT option value. SET XACT_ABORT option takes effect at run time.

Let us see how the SET XACT_ABORT option works. A simple table will be created with one INT column and try to insert three values twice to that table within transactions, with the middle insertion process causing a divide-by-zero error on each transaction. The first three records will be inserted with the XACT_ABORT option set to OFF and the second three records will be inserted with the XACT_ABORT option set to ON:

The result below will show that both transactions returned an error message due to divide-by-zero problem detected:

Trying to retrieve the inserted data to the table:

The result will show that the first transaction, with the XACT_ABORT option set to OFF, the first and the last INSERT statements executed successfully, and only the middle INSERT statement with divide-by-zero error is terminated. The second transaction with the XACT_ABORT option set to ON, the whole transaction is rolled back with no value inserted to that table from that transaction, as shown clearly below:

SQL Server provides us with many built-in functions that help in getting the SQL Server metadata information. One of these built-in functions is the @@OPTIONS function, that helps in getting the current values of the SET options for the current session. It returns an integer value that contains the current value of 15 SET options on the current session, with each option value stored in one bit of that integer value.

The default values of the SET options that is returned from the @@OPTIONS function is 5496, with no option value overridden yet:

Where the 5496 value is equivalent to 1010101111000 in binary, with each bit reflects the value of a specific option. The @@Option function can display the values of the nine SET options described in this articles series in more readable way using the T-SQL script below:

After running the previous T-SQL script within a new session in my local SQL Server 2016 instance, the returned result will be as shown below:

Some of the previously described SET options can be set globally at the SQL Server instance level to be applied automatically to all users’ sessions using the SQL Server Management Studio, from the Connections tab of the SQL Server Instance Properties window as shown below:

Take into consideration that setting these options values, at the session level, using the previously listed SET options will override the server level values for the current session.

Conclusion

In this article, we described deeply the widely used nine SET options that control the way SQL Server works and returns data.

We demonstrated, in practical terms, for each option how the SQL Server behavior differs when you set these options ON and OFF. Due to the critical importance of these settings, you should be careful when you change the default SET options value, as these SET options change the way the SQL Server Database Engine behaves and the query results. It is better to test the behavior in the DEV environment first before applying it to the production environment.

Table of contents

• Author
• Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021