Why is using proper ANSI settings important

You might have been wondering what all those ANSI settings are and how they can affect you work.

In my opinion most SQL server people are familiar with ANSI NULLS and likely with QUOTED IDENTIFIER, but they do not worry, or they do not have to worry about the others.

What I would like to write today about is the impact of not having your database, objects, or your SQL server sessions compliant with ANSI settings and why it can stop you from using advanced performance tuning techniques.

Table 1: overview of ANSI setting affecting tuning options

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#filtered-indexes

What I call advanced tuning are from my point of view:

• Indexed computed columns
• Indexed views

Both behave in the same way so I will be demonstrating the issue only on the computed column scenario.

The good news is that the default settings of SQL Server Management Studio are the correct ones, but you never know who, when on what machine or based on what script the database or tables were created and believe me, we saw that in the past on client's environment. That is one of the reasons to write this post and why we had to prepare a script to resolve a troublesome situation for our customer.

From what I experienced the database settings do apply only for cases when somehow the setting for session is not defined. For example, if the ANSI_NULLS cannot be determined from the session the settings from the database will apply. Those are not affected by your session parameters when you are creating the database but can be changed either in GUI or through ALTER DATABASE script.

ALTER DATABASE [test_ANSIon] SET ANSI_WARNINGS ON
GO

Figure 1: ANSI database options

On the database objects level, we care about the properties of a table.

Figure 2: ANSI settings of the table

There are only two ANSI related settings on the table level, but they are the most critical, because there is no way of changing them other than re-creating the whole table, which can be a big deal for big tables requiring an outage of a production system and once you take into account objects related though foreign key or schema binding it becomes quite a massive job.

Let’s have a look at the actual impacts.

Scenario 1

Somebody created a table with wrongly ANSI configuration and after a while we want to add a computed column and index it.

SET ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON, CONCAT_NULL_YIELDS_NULL OFF
GO

CREATE TABLE ansi_off (
id int identity(1,1),
value varchar(20))

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

ALTER table ansi_off ADD comp_id AS (id * 2)

CREATE INDEX NC_ansi_on_comp_id ON ansi_off(comp_id)

Code 3: create table with ANSI settings off

Creating the index will result into an error;

Msg 1935, Level 16, State 1, Line 13

Cannot create index. Object 'ansi_off' was created with the following SET options off: 'ANSI_NULLS'.

Even enabling all the ANSI settings (important for this job) for the current session will not help.

SET ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON, CONCAT_NULL_YIELDS_NULL ON
GO

CREATE INDEX NC_ansi_on_comp_id ON ansi_off(comp_id)

Still returns the same error because the table has following ANSI NULLS setting.

Figure 3: ANSI NULLS disabled on the table level.

Scenario 2

Database table is aligned with recommended ANSI as it was created from session with correct settings. However, the session that tries to insert into the table with a computed column, which is indexed, has QUOTED IDENTIFIERS set to OFF.

SET ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT, ANSI_NULL_DFLT_ON, CONCAT_NULL_YIELDS_NULL ON

CREATE TABLE ansi_on (
id int identity(1,1),
value varchar(20),
comp_id AS (id*2))
GO

CREATE INDEX NC_ansi_on_comp_id ON ansi_on(comp_id)
GO

Code for session 1

SET QUOTED_IDENTIFIER OFF 
GO

INSERT INTO ansi_on VALUES ('ANSI OFF');

Code for session 2

Result of this insert query:

Msg 1934, Level 16, State 1, Line 78

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Imagine an impact of this. You have indexed a computed column, everything seems to be working fine and then all of a sudden one session with wrongly set parameters will not be able to insert into the table and it might take a while until you realize this.

Keep in mind that one of another requirements for being able to build an index on a computed column field it must be deterministic.

Do you want to check your session settings? You can use @@OPTIONS system function with a bitwise operator to determine the values. You can read more here; https://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/