SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.
In this article series, we will list nine most common and heavily used session-level SET options and how SQL Server behaves before and after changing its default configuration. In this article, the first in a two-part series, we will describe the first four options.
SET ANSI_NULLS
The ANSI_NULLS session-level option, as the name indicates, controls if the SQL Server Database Engine will follow the ANSI SQL Server standard in prohibiting the usage of the equality operators to compare with the NULL value, as the NULL is never equal to anything. Instead of the equality operators, the IS NULL and IS NOT NULL can be used to compare the current value with the NULL value regardless of the current ANSI_NULLS option value.
The T-SQL syntax that is used to control the ANSI_NULLS option is shown below:
SET ANSI_NULLS { ON | OFF }
When setting ANSI_NULLS to ON, all comparisons against the NULL value will evaluate UNKNOWN; the (column_name=NULL) and (column_name<>NULL) in the WHERE clause will return no rows even there are NULL and non-NULL values in that column, with no mean of using the equality operators to compare with the NULL value.
Setting the ANSI_NULLS to OFF, the Equals (=) and Not Equal To (<>) comparison operators will not follow the ISO standard for the current session and can be used to compare with the NULL value. In this case, the (column_name=NULL) in the WHERE statement will return all rows with NULL value in that column, and the (column_name<>NULL) will exclude the rows that have NULL value in that column, considering the NULL as a valid value for comparison.
Take into consideration that, for executing distributed queries or creating or changing indexes on computed columns or indexed views, the SET ANSI_NULLS 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 ANSI_NULLS setting will be set at run time and not at parse time.
Let us see how the ANSI_NULLS setting work. We will create a simple table and insert four records into that table:
|
1 2 3 4 5 6 7 |
CREATE TABLE ANSI_NULLS_Test (ID INT IDENTITY (1,1), NAME VARCHAR(10) NULL) GO INSERT INTO ANSI_NULLS_Test VALUES ('John'), (NULL), ('Jenny'), (NULL) |
In addition, we will see how the SQL Server behaves by default, when using the equality operators to compare the table values with the NULL value, and how it differs from using the IS NULL and IS NOT NULL operators:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT 'Default Setting in SQL Server 2016' SELECT * FROM ANSI_NULLS_Test WHERE [NAME] = NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] <> NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NOT NULL |
The result will show us that, no result will be returned when using the equality operators in evaluating the NULL values, which means that, by default, SQL Server evaluates the NULL value to UNKNOWN and will not consider it as a normal value that can be compared using the equality operators. This is not the case for the result returned from SELECT queries that use IS NULL and IS NOT NULL to evaluate the NULL values, which return correct values, as shown in the result below:
Setting the ANSI_NULL option to ON then executing the same SELECT statements:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT 'ANSI_NULLS ON' SET ANSI_NULLS ON GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] = NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] <> NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NOT NULL |
The same previous result will be returned from the query. Which means that by default, the ANSI_NULLS option is ON:
If we set the ANSI_NULLS option to OFF and try the same SELECT statements:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT 'ANSI_NULLS OFF' SET ANSI_NULLS OFF GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] = NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] <> NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NULL GO SELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NOT NULL |
You will see clearly that the NULL value is comparable now using equality operators; where the Equal to (=) operator works same as IS NULL operator and returns all rows with name column values equal to NULL.
On the other hand, the Not Equal to (<>) operators works same as IS NOT NULL operator and returns all rows with name column values not equal to NULL, evaluating the NULL value as any other normal value, as shown in the result below:
It is better to use the IS NULL and IS NOT NULL operators for the NULL value comparison that will work in all cases regardless of the ANSI_NULLS option setting.
SET ANSI_PADDING
The ANSI_PADDING setting controls how trailing spaces are handled in columns with CHAR and VARCHAR data types, and trailing zeroes in columns with BINARY and VARBINARY data types. In other words, it specifies how the column stores the values shorter than the column defined size for that data types.
The T-SQL syntax that is used to control the ANSI_ PADDING option is shown below:
SET ANSI_PADDING { ON | OFF }
When the ANSI_ PADDING option is set to ON, which is the default setting:
- The original value of the char(n) column with trailing blanks will be padded with spaces to the length of the column. The spaces will be retrieved with the column value.
- The original value of the binary(n) column with trailing zeroes will be padded with zeroes to the length of the column. The zeroes will be retrieved with the column value.
- The trailing blanks in the varchar(n) column will not be trimmed and the column original value will not be padded with spaces to the length of the column.
- The trailing zeroes in the varbinary(n) column will not be trimmed and the column original value will not be padded with zeroes to the length of the column.
Setting the ANSI_ PADDING option is set to OFF:
- The original value of the char(n) NOT NULL column with trailing blanks will be padded with spaces to the length of the column. The spaces will not be retrieved with the column value.
- The original value of the binary(n) NOT NULL column with trailing zeroes will be padded with zeroes to the length of the column. The zeroes will not be retrieved with the column value.
- The trailing blanks in the varchar(n) column will be trimmed and the column original value will not be padded with spaces to the length of the column.
- The trailing zeroes in the varbinary(n) column will be trimmed and the column original value will not be padded with zeroes to the length of the column.
- The original value of the Null-able char(n) and Null-able binary(n) columns with trailing blanks and zeroes will not be padded, and these spaces and zeroes will be trimmed from the original column value.
The SET ANSI_PADDING option is always ON for the columns with nchar, nvarchar, ntext, text, image data types. Which means that trailing spaces and zeros are not trimmed from the original value.
The SET ANSI_PADDING option affects only newly created columns. Once created, the value will be stored in the column based on the setting configured when that column was created, and this column will not be affected by any new change performed after its creation.
Again, taking into consideration that, for creating or changing indexes on computed columns or indexed views, the SET ANSI_ PADDING should be set to ON. The SET ANSI_ PADDING setting will be set at run time and not at parse time.
Let us see now, how the SQL Server will act with different ANSI_ PADDING settings. We will create a simple table that contains the targeted data types and insert two records into that table; the first record without spaces and zeroes and the second one with spaces and zeroes. As the ANSI_ PADDING setting affects only the newly created columns, we will create new table each time the ANSI_ PADDING option setting is changed. We will start with setting the ANSI_ PADDING to ON:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SET ANSI_PADDING ON; CREATE TABLE Test_ANSI_PADDING_ON ( Nullable_char char(15) NULL, NotNull_char char(15) NOT NULL, Nullable_varbinary varbinary(15) NULL, Nullable_varchar varchar(15) NULL, Nullable_Nvarchar nvarchar(15) NULL ) INSERT INTO Test_ANSI_PADDING_ON VALUES ('Jack','Jack',0x00ee,'Jack','Jack'), ('Jack ','Jack ',0x00ee00,'Jack ','Jack '); |
Checking the length of each column data inserted into the table, with and without spaces using the DATALENGTH function:
|
1 2 3 4 5 6 7 8 |
SELECT DATALENGTH(Nullable_char) AS Nullable_char, DATALENGTH(NotNull_char) AS NotNull_char, DATALENGTH(Nullable_varbinary) AS Nullable_varbinary, DATALENGTH(Nullable_varchar) AS Nullable_varchar, DATALENGTH(Nullable_Nvarchar) AS Nullable_Nvarchar FROM Test_ANSI_PADDING_ON; |
The result will show that, the char data type will consume all the column size, which is 15. The case is different with the varchar, varbinary and nvarchar data types, in which the space will be calculated in the column length, but the rest of the column size that is not consumed will not be padded, as shown in the result below:
Retrieving the data inserted in each column, using the “<” character to specify the end of the column value for string data types:
|
1 2 3 4 5 6 7 8 |
SELECT Nullable_char + '<' AS Nullable_char , NotNull_char + '<' AS NotNull_char , Nullable_varbinary AS Nullable_varbinary , Nullable_varchar +'<' AS Nullable_varchar , Nullable_Nvarchar +'<' AS Nullable_Nvarchar FROM Test_ANSI_PADDING_ON |
It is clear from the result below that, for the char data type, the original value will be padded with spaces to the size of the column, which is 15 in our case. This is why we see the spaces in both records, although we did not inset spaces to that column in the first record.
For the varchar, varbinary and nvarchar, the case is different, where the spaces and zeroes that are inserted will be retrieved with the column value, but the rest of the column size will not be padded with spaces and zeroes, and will be empty. This is why we see the spaces and zeroes in the second record that is not appeared in the first record:
Let us see if it will behave in a different way when setting the ANSI_PADDING to OFF. We will create a new table with the same data types and sizes, and insert the same two records again:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SET ANSI_PADDING OFF; CREATE TABLE Test_ANSI_PADDING_OFF ( Nullable_char char(15) NULL, NotNull_char char(15) NOT NULL, Nullable_varbinary varbinary(15) NULL, Nullable_varchar varchar(15) NULL, Nullable_Nvarchar nvarchar(15) NULL ) INSERT INTO Test_ANSI_PADDING_OFF VALUES ('Jack','Jack',0x00ee,'Jack','Jack'), ('Jack ','Jack ',0x00ee00,'Jack ','Jack '); |
Checking the length of each column data inserted into the table again, with and without spaces using the DATALENGTH function:
|
1 2 3 4 5 6 7 8 |
SELECT DATALENGTH(Nullable_char) AS Nullable_char, DATALENGTH(NotNull_char) AS NotNull_char, DATALENGTH(Nullable_varbinary) AS Nullable_varbinary, DATALENGTH(Nullable_varchar) AS Nullable_varchar, DATALENGTH(Nullable_Nvarchar) AS Nullable_Nvarchar FROM Test_ANSI_PADDING_OFF; |
The result will show that, the NOT NULL char data type will act in the same way by consuming all the column size, which is 15.
The case is different with the Null-able char varchar and varbinary data types, in which the space will not be calculated in the column length, and the rest of the column size that is not consumed will not be padded. The nvarchar data type will act in the same previous way, in which the spaces will be calculated in the column length but the rest of the column size will not be padded, as shown in the result below:
If we Retrieve the data inserted in each column, using the “<” character to specify the end of the column value for string data types:
|
1 2 3 4 5 6 7 8 |
SELECT Nullable_char + '<' AS Nullable_char , NotNull_char + '<' AS NotNull_char , Nullable_varbinary AS Nullable_varbinary , Nullable_varchar +'<' AS Nullable_varchar , Nullable_Nvarchar +'<' AS Nullable_Nvarchar FROM Test_ANSI_PADDING_OFF |
You will see clearly from the result below that, the NOT NULL char data type will act same as previously, where the original value will be padded with spaces to the size of the column, which is 15 in this case. That is why you can see the spaces in both records, although we did not inset spaces to that column in the first record.
For the Null-able char, varchar and varbinary data types, the spaces and zeroes that are inserted will not be retrieved with the column value, and the rest of the column size will not be padded with spaces and zeroes, and will be empty. This is why we cannot see the spaces and zeroes in the second record although we inserted these spaces and zeroes in the original value.
Nothing will be changed for the nvarchar data type, that will work always and ANSI_PADDING ON, the spaces will be retrieved but the rest of the column size will not be padded:
Be careful when changing the ANSI_PADDING setting and create new column, as that setting will not be changed for that column until you drop it and create it again.
SET ANSI_WARNINGS
The ANSI_WARNING controls the ISO standard behavior of the SQL Server Database Engine for several error conditions. The T-SQL syntax that is used to control the ANSI_ WARNING option is shown below:
SET ANSI_WARNINGS { ON | OFF }
Setting the ANSI_WARNING option to ON, the SQL Server Database Engine will follow the ISO standard in:
- A warning message is generated when null values appear in aggregate functions, such as SUM, AVG, MAX, MIN or COUNT.
- The T-SQL statement is rolled back and error message is generated when the divide-by-zero and arithmetic overflow errors detected.
- The INSERT or UPDATE T-SQL statement is canceled and an error message is generated if the length of a new value specified in that statement for the string column exceeds the maximum size of the column.
Setting the ANSI_WARNING option to OFF, the SQL Server Database Engine will follow a non-standard behavior, in which:
- No warning is issued when null values appear in aggregate functions, such as SUM, AVG, MAX, MIN or COUNT.
- A Warning message is generated when the divide-by-zero and arithmetic overflow errors detected and NULL values will be returned.
- The INSERT or UPDATE T-SQL statement will succeed if the length of a new value specified in that statement for the string column exceeds the maximum size of the column, and the data inserted will be truncated to the size of that column.
Take into consideration that, for executing the distributed queries or creating or changing indexes on computed columns or indexed views, the SET ANSI_ WARNINGS 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 ANSI_ WARNINGS setting will be set at run time and not at parse time.
Let us see how the ANSI_ WARNINGS setting work. We will create a simple table and insert three records into that table:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE ANSI_WARNINGS_Test ( ID INT IDENTITY(1,1), Num INT NULL, EmpName VARCHAR(15)) GO INSERT INTO ANSI_WARNINGS_Test VALUES (NULL, NULL), (1,NULL),(2,NULL) |
The first case that we will check is the NULL values that appear in the aggregate functions, which is the SUM function in our demo below. If we try to get the SUM of the NUM column values, with the ANSI_ WARNINGS set to ON:
|
1 2 3 4 5 6 7 8 |
SET ANSI_WARNINGS ON; GO SELECT ID,SUM(Num) AS TotalIDs FROM ANSI_WARNINGS_Test GROUP BY ID GO |
The query will succeed, but a warning message will be displayed, showing that the detected NULL values will be eliminated from the aggregate function result as shown below:
Setting the ANSI_ WARNINGS option to OFF and executing the same query again:
|
1 2 3 4 5 6 7 8 |
SET ANSI_WARNINGS OFF; GO SELECT ID,SUM(Num) AS TotalIDs FROM ANSI_WARNINGS_Test GROUP BY ID GO |
The query will succeed and no warning message will be displayed:
Now we will test how the SQL Server Database Engine will act when inserting a long string to a column, exceeding the column’s defined size. If we try to insert the below long string to the EmpName with size 15, with the ANSI_WARNINGS option set to ON in the first case:
|
1 2 3 4 5 6 |
SET ANSI_WARNINGS ON; INSERT INTO ANSI_WARNINGS_Test VALUES (5, 'Hi From SQL Shack Site Where You Can Always Learn New'); GO |
The INSERT statement will fail and an error message will be displayed, showing that the string should be truncated as it exceeds the column size:
Trying to insert the same long string to the EmpName column, with the ANSI_WARNINGS option set to OFF this time:
|
1 2 3 4 5 6 |
SET ANSI_WARNINGS OFF; INSERT INTO ANSI_WARNINGS_Test VALUES (6, 'Hi From SQL Shack Site Where You Can Always Learn New'); GO |
The insert statement will succeed with no error message displayed:
What happened to the inserted long string? Executing the below SELECT statement that retrieve the inserted record:
|
1 2 3 |
SELECT * FROM ANSI_WARNINGS_Test WHERE Num=6 |
You will see that the insert succeeded but the string will be truncated automatically to the column size. Which means that only 15 characters of the inserted string will be saved to the column as shown below:
The last test for the ANSI_WARNINGS option is the divide by zero case. We will drop the existing table and create new one with three decimal columns, and insert three new records to that table:
|
1 2 3 4 5 6 7 8 9 10 11 |
DROP TABLE ANSI_WARNINGS_Test CREATE TABLE ANSI_WARNINGS_Test ( ID DECIMAL (5,2), Num DECIMAL (5,2), DivRest DECIMAL (5,2), ) GO INSERT INTO ANSI_WARNINGS_Test VALUES (1, 0,NULL),(2, 1,NULL),(2, 2,NULL) |
Then we will update that table to fill the DivRest column with the result generated by dividing the ID column by the Num column value, after setting the ANSI_WARNINGS option to ON in the first case, with the ARITHABORT option set to OFF in all cases, that will be described in details later within this article:
|
1 2 3 4 5 6 7 8 9 10 11 |
SET ARITHABORT OFF GO SET ANSI_WARNINGS ON GO UPDATE ANSI_WARNINGS_Test SET DivRest= ID / NUM ; GO SELECT * FROM ANSI_WARNINGS_Test GO |
An error message will be displayed indicating that you are trying to divide by zero, and the update statement will be rolled back as shown below:
The select statement result will show that the update statement fail with no change performed on the DivRest column:
Setting the ANSI_WARNINGS option to OFF, and execute the same UPDATE statement:
|
1 2 3 4 5 6 7 8 9 10 11 |
SET ARITHABORT OFF GO SET ANSI_WARNINGS OFF GO UPDATE ANSI_WARNINGS_Test SET DivRest= ID / NUM ; GO SELECT * FROM ANSI_WARNINGS_Test GO |
The update statement will succeed and a warning message will be displayed showing that division by zero is caught:
Moreover, the select statement will show that the DivRest column is updated for all values except for the one with divide by zero issue, which returns NULL value:
SET ARITHABORT
The ARITHABORT option terminates the query when an overflow or divide-by-zero error occurs during the execution of the query. The T-SQL syntax that is used to control the ARITHABORT option is shown below:
SET ARITHABORT { ON | OFF }
If you set the ARITHABORT option to ON and the ANSI WARNINGS is set to ON, the query will terminate and error messages will be generated.
If you set the ARITHABORT option to ON and the ARITHABORT is set to OFF, all the batch will terminate.
Setting the ARITHABORT option to OFF and overflow or divide-by-zero error occurs, a warning message is displayed, and NULL will be returned to the result of the arithmetic operation. If the target column does not allow NULL, the insert or update action on that column fails and the user will receive an error.
The ARITHABORT option should be set to ON when you are creating or changing indexes on computed columns or indexed views. 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 ARITHABORT setting will be set at run time and not at parse time.
Let us see how the ARITHABORT setting work. We will create a simple table and insert three records into that table after setting the ARITHABORT option to ON:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE ARITHABORT_Test ( Num1 TINYINT, Num2 TINYINT ) SET ARITHABORT ON GO INSERT INTO ARITHABORT_Test VALUES (1,0),(2,10),(3,512) |
The insert statement will fail, showing that you are inserting a value of 512 that exceeds that TINYINT column limit:
Trying the same INSERT statement after setting the ARITHABORT option to OFF:
|
1 2 3 4 5 |
SET ARITHABORT OFF GO INSERT INTO ARITHABORT_Test VALUES (1,0),(2,10),(3,512) |
A warning message will be displayed showing that one of the values, to be inserted, exceeds the column limit, but the INSERT statement succeeds:
Retrieving the inserted values:
|
1 2 3 |
SELECT * FROM ARITHABORT_Test |
The result will show that, all values will be inserted successfully except for the one that exceeds the column limit, which will be replaced by NULL:
If we try to perform a division operation that contains division by zero problem, after setting the ARITHABORT option to ON:
|
1 2 3 4 5 |
SET ARITHABORT ON GO SELECT Num1/Num2 AS DivRes FROM ARITHABORT_Test |
The statement will fail with error message showing that you are trying to divide by zero:
Trying the same division operation that contains division by zero problem, after setting the ARITHABORT option to OFF:
|
1 2 3 4 5 |
SET ARITHABORT OFF GO SELECT Num1/Num2 AS DivRes FROM ARITHABORT_Test |
The statement will succeed and the division by zero value will be returned as NULL as shown below:
Conclusion
Within this article, the first in a two-part series, we describe the first four SET options; SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT and show practically how setting these options ON and OFF affect the SQL Server Database Engine behavior and the query result.
In the second part of this series, we will go through the five rest options in the same detailed way with clear practical demos.
Table of contents
| SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT |
| SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT |
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
- 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