Conditional indices

[codefrenzy]

By default, Shaolinq generates an index with a NOT NULL condition on each of the columns included in the index, e.g.:

CREATE UNIQUE NONCLUSTERED INDEX [Person_IsAdult_Name] ON [dbo].[Person] ([Name], [IsAdult]) WHERE ([Name] IS NOT NULL AND [IsAdult] IS NOT NULL)

But sometimes we might don't want the IS NOT NULL conditions at all, e.g.:

CREATE UNIQUE NONCLUSTERED INDEX [Person_IsAdult_Name] ON [dbo].[Person] ([Name], [IsAdult])

In other cases, we might want to be able to specify some specific NOT NULL conditions, e.g.:

CREATE UNIQUE NONCLUSTERED INDEX [Person_IsAdult_Name] ON [dbo].[Person] ([Name], [IsAdult]) WHERE ([Name] IS NOT NULL)

And in other cases, we need to be able to specify a totally different condition, e.g.:

CREATE UNIQUE NONCLUSTERED INDEX [Person_IsAdult_Name] ON [dbo].[Person] ([Name], [IsAdult]) WHERE ([Name] IS NOT NULL AND [IsAdult] = 1)

Would it be possible to implement this type of flexibility via the [Index] attribute in Shaolinq?

[tumtumtum]

The IS NOT NULLS in the first example is to work around a peculiarity in MS SQL Server where it considers NULLs to be identical for the purposes of unique indexes (whereas the SQL spec says nulls aren't values so can't be equal). This usually affects cases where you have an index across multiple columns. Every server (except SQL Server) would allow [A, null] and [A, null] whereas SQL server would give a unique constraint error when inserting the second record.

Let me think about how to allow this to be specified without making the IndexAttribute too MSSQL specific :-).

The custom expression (last) case isn't a problem to add.

[codefrenzy]

Yup, you're absolutely right about the uniqueness of the IS NOT NULLs - that's why you added this in the first place ;)

But we don't always want unique indices. When we just want non clustered indices for lookup purposes, it's fine if there are duplicates as you described. It's sort of on a case-by-case basis. Perhaps if creating a unique constraint, you should always by default do the IS NOT NULL on all columns, but if it's just a standard index, then no?

[tumtumtum]

If it's not unique I'll take out the NOT NULLS :-D That's a quick win.

[tumtumtum]

Closing this because it has been solved as follows:

New configuration options added:

• SqlServerSqlDatabaseContextInfo.ExplicitIndexConditionOverridesNullAnsiCompliance
• SqlServerSqlDatabaseContextInfo.UniqueNullIndexAnsiComplianceFixerClassicBehaviour

By default, the additional IS NOT NULL conditions added by Shaolinq in order to keep SQL Server ANSI compliant will only be included if the index is unique. This behaviour can be overriden by setting SqlServerSqlDatabaseContextInfo.UniqueNullIndexAnsiComplianceFixerClassicBehaviour to true but it is recommended that classic behaviour be kept off as it is unnecessary and is only there to help with systems where there are existing schemas that can't be migrated in a single-step.

IndexAttribute now has a IndexAttribute.Condition property which can be used to specify custom and complex conditions on an index (the index's WHERE clause). By default, these custom conditions will be added in addition to the IS NOT NULL conditions added by Shaolinq in order to keep SQL Server ANSI compliant. If you set SqlServerSqlDatabaseContextInfo.ExplicitIndexConditionOverridesNullAnsiCompliance to true then any custom condition set on an index will suppress all the IS NOT NULL conditions usually added by Shaolinq to keep SQL Server ANSI compliant. In this cases, it is up to the user to add the custom IS NOT NULL conditions if ANSI complaince is needed.

ANSI compliance refers to the fact that the ANSI SQL spec treats NULL <> NULL. SQL server treats NULL = NULL for determining uniqueness for indexes. This means unexpected constraint violations can occur when switching from another database provider to SQL server. Shaolinq makes SQL server ANSI compliant by simply not adding NULL values to unique indexes.