CODEWITHSUNDEEP
sql-servert-sql
Jaie
Jaie

Reputation: 117

TSQL Unique filtered Index does not prevent insert

CREATE TABLE [dbo].[MessageTest]
(
    [ID] [uniqueidentifier] NOT NULL,
    [GatewayMessageId] [nvarchar](255) NULL,
    [GatewayOriginalMessageId] [nvarchar](255) NULL,

    CONSTRAINT [MessageTest_PK] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO

CREATE UNIQUE INDEX Message_GatewayIDs_IDX 
ON [dbo].[MessageTest] (GatewayMessageId, GatewayOriginalMessageId) 
WHERE GatewayMessageId IS NOT NULL
  AND GatewayOriginalMessageId IS NOT NULL

-- Should work fine
INSERT INTO [dbo].[MessageTest] VALUES (NEWID(), NULL, NULL)
INSERT INTO [dbo].[MessageTest] VALUES (NEWID(), NULL, NULL)
INSERT INTO [dbo].[MessageTest] VALUES (NEWID(), NULL, NULL)

-- Should work
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', NULL)

-- Should not work???
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', NULL)

-- Should work
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', '257DC188-D687-4071-8A1A-04AAD50411E4')

-- Does not work as expected
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', '257DC188-D687-4071-8A1A-04AAD50411E4')

Why does the unique filtered index not prevent the insert statement when there is a null on the second attempt?

INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', NULL)

What am I missing here?

I know if I change the index to

CREATE UNIQUE INDEX Message_GatewayIDs_IDX 
ON [dbo].[MessageTest] (GatewayMessageId, GatewayOriginalMessageId) 
WHERE GatewayMessageId IS NOT NULL

Then this works as I would expected the previous one to providing GatewayMessageId is always populated first.

Is this a bug?

Upvotes: 0

Views: 164

Answers (2)

gotqn
gotqn

Reputation: 43636

When a index is created several settings can be used:

  • unique - guarantees there is no duplicated values and like in set operators (UNION, EXCEPT, INTERSECT), here NULL = NULL which means that you can have only one value as two NULLs are find as duplicates of each other;
  • clustered/nonclustered - the logical order of the key values determines the physical order of the corresponding rows in a table if the index is clustered
  • INCLUDE - allows to specify the non-key columns to be added to the leaf level of the nonclustered index (in order to reduce key-lookups)
  • WHERE - specifying which rows to include in the index

So, the WHERE clause is limiting the rows that are included in the index. From there, it may have significantly smaller size than index without the filtering clause. And the smaller size, means less data is read (less IO) and potentially better performance. The requirement for a query to use it is to contain the same WHERE clause as the index.

And from the above your code:

WHERE GatewayMessageId IS NOT NULL
  AND GatewayOriginalMessageId IS NOT NULL

is filtering which rows to be part of the index, not enforcing data integrity.

Also, declaring a PRIMARY KEY or UNIQUE constraint causes SQL Server to automatically create an index.

An unique index can be created without matching a constraint, but a constraint (either primary key or unique) cannot exist without having a unique index.

And as we said, a UNIQUE constraint allows one null value to be inserted, so if you don't want to have NULL values, you need to declare the column as such:

ALTER TABLE [...]
ALTER COLUMN [...] [type] NOT NULL;

and if the table has already NULL rows need to populate them first - one technique is to create a DEFAULT CONSTRAINT with the WITH VALUES option. But note that, this will lock your table. Another way is to perform batch update, first.

Upvotes: 0

seanb
seanb

Reputation: 6685

The UNIQUE constraint on the index means values within the index must be unique.

Your filter on the index is

where GatewayMessageId IS NOT NULL
    AND GatewayOriginalMessageId IS NOT NULL

(in other words - both GatewayMessageId and GatewayOriginalMessageId must have values).

However, as you are inserting a row into the table with GatewayOriginalMessageId = NULL, it is not included in the filtered index - and therefore is not subject to the UNIQUE constraint within that index.

Upvotes: 3

Related Questions