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:
To join our SDU Insiders and get our free SDU Tools for Developers and DBAs and to get our other free resources, visit here:
So, all well and good – but ZERO information on what ANSI_NULLS does, or WHY you consider OFF to be the wrong value? Or why SQL Server apparently defaults to OFF, but SSMS apparently thinks that ON is better?
Article is on changing it. Most finding it will already be looking to change it.
I'll look to add an article explaining it in the future. Thanks for the feedback.