SDU Tools: Set ANSI NULLS on for SQL Server Table

When you create script out a table in SQL Server using SQL Server Management Studio, you’ll notice that it scripts more than just the table itself. Before the table, it scripts the values for ANSI_NULLS and QUOTED_IDENTIFIER.

A common problem that I see is that someone highlights the CREATE TABLE statement and runs it, without highlighting the SET options above it. That has the chance of leading to the wrong values. I don’t run into big issues with QUOTED_IDENTIFIER but I certainly run into issues with ANSI_NULLS.

At a client site a few weeks back, I saw some odd behaviour, and checked the settings for all tables. They had a mixture of ON and OFF.

The question is how to then fix it.

The general advice is to copy the data off to somewhere else, drop and recreate the table, and copy the data back. Doing that might be awkward for a number of reasons.

Let’s create a table with what I consider the wrong value:

Notice that it shows uses_ansi_nulls as 0.

One of the tools that we added in our free SDU Tools for January 2018 was SetAnsiNullsOnForTable. You can see an example of using it in the main image above.

Notice that when it’s complete the table has the ANSI_NULLS value ON. The tool uses a trick involving a partition switch with a single partition.

Note that you’ll need to recreate constraints (including foreign keys) and indexes when it’s finished, but we hope you’ll find this useful.

You can see it in action here: