How often does adding an index in SQL Server break something? Let’s say it’s under normal operating conditions, no corruption, not adding unique or other constraints.
As a consultant and with 15+ years experience, it’s just not something I’ve seen very often at all. It is so rare, that even “seasoned pros” like yours truly may run into this and be surprised.
What I’m talking about here is the conflict between the ANSI_NULLS setting and certain types of indexes, that when combined, cause a query to simply error out. The error message is actually very clear and descriptive. It is even documented, which would be great, if I thought I needed to check there first!
Microsoft has a good primer here on ANSI_NULLS settings and its effect on Boolean logic. There are several people who have written detailed explanations elsewhere, but please just try to keep ANSI_NULLS ON for any new work. This story involves some legacy code that has been around for a long time and whose author is now unknown.
The Error:
DELETE failed because the following SET options have incorrect settings: ‘ANSI_NULLS’. 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.
In this case it was a delete statement in a stored procedure that started to fail and I had just added an index on a computed column.
The Cause:
That error message pretty much said it all. A new index was added to a computed column on a table and this seemingly unrelated delete statement in a stored procedure started to fail. Which lead me to question, where exactly was this ANSI_NULLS being set incorrectly (to FALSE)?
The uses_ansi_nulls flag can be set in various places such on the table itself, on stored procedures, and various other objects.
In this case the table had uses_ansi_nulls set to 1 but the stored procedure was set to 0.
It’s important to note here that uses_ansi_nulls is not part of the stored procedure’s definition so you wont find it after the CREATE PROC. It’s part of the schema object and can be viewed through system views such as sys.sql_modules and sys.tables. It is defined when the object is created or altered based on the session’s execution setting. This can be changed explicitly via the SET ANSI_NULLS option. Most people will likely just roll with the defaults that SSMS or Azure Data Studio provides and not even realize this option is there.
The fix:
In this case I had to remove the index until such time when I can go back and test the stored procedure altered with ANSI_NULLS ON. Sure I could have just ran an ALTER PROC with the same definition with correct session options but that carries a risk of changing the semantics of code I hadn’t reviewed yet. I could imagine a scenarios where the original developer had not used “IS NULL” anywhere in the WHERE clauses and my simple change would potential change data being modified. Not something I wanted to do right now!
The lesson learned:
Check the uses_ansi_nulls settings for any objects dependent on the table where I’m about to add the index. This won’t catch ad-hoc statements but it’s a start. Beyond that, have a test environment and test plan!
The repro:
Here’s a code snippet that will show how to reproduce this error using a trivial table and computed column. Keep in mind that this is just the computed column scenario. The filtered index scenario is also just as likely and then there’s XML, Spatial, and Query Notifications (as mentioned in the error) which I haven’t tried to reproduce.
SET ANSI_NULLS ON
GO
CREATE TABLE dbo.Person
(
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
FullName AS (CONCAT(FirstName, ' ', LastName))
)
GO
CREATE INDEX IX_Person_FullName on dbo.Person(FullName)
GO
SET ANSI_NULLS OFF
GO
CREATE OR ALTER PROC dbo.PopulatePerson
as
BEGIN
DELETE FROM dbo.Person
INSERT INTO dbo.Person (FirstName, LastName)
VALUES ('Harold', 'Potter')
END
GO
SET ANSI_NULLS ON
GO
EXEC dbo.PopulatePerson
GO
Addition reading / references:
SQL Docs: Indexes on Computed Columns
SQL Docs: CREATE INDEX > Filtered Indexes