CODEWITHSUNDEEP
sql-server
Robyn
Robyn

Reputation: 1374

Why does SQL Server require ansi_nulls to be on to create an index?

Some actions in SQL Server will throw an error if executed after SET ANSI_NULLS OFF. The remedy is simple, I just have to SET ANSI_NULLS ON (and make sure my queries aren't adversely affected by the different way it compares nulls).

This is known behaviour. The official documentation says this:

ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views.

But why is it so? Is there a technical reason why this variable must be set to ON?

Upvotes: 0

Views: 761

Answers (2)

Martin Cairney
Martin Cairney

Reputation: 1767

It has a base in being deterministic.

A calculated column or an indexed view for example must always return the same data no matter what the session settings are, and therefore they are forced to behave in a consistent manner by the engine.

The example here in the indexed view page shows one example of where different options can change the behaviour. Because a calculation could in some cases use an equality comparison between columns, consistent behaviour is needed here too

Upvotes: 1

Charlieface
Charlieface

Reputation: 71805

In this document, Microsoft describe all deprecated features in SQL Server. ANSI_NULLS OFF is one of them.

It is not worth it for them to build and test new features against a deprecated feature they intend to remove soon, and do not recommend using. Between you and me, I can think of a hundred things I would prefer they do with their time.

Upvotes: 1

Related Questions