Should unique filtered indexes include their filtering column?
1
vote
1
answer
120
views
### Context
Suppose that you have a unique filtered index on a table. Said index exists [only to enforce a constraint](https://dba.stackexchange.com/questions/340941/are-unique-filtered-indexes-considered-an-antipattern-for-enforcing-constraints) . A classic example of such an index would be one making sure that a non-nullable bit column called
Active
only takes the value 1
at most once for each value of another column.
Should such indexes have their filtering columns in the INCLUDES
clause? The answers to [this question](https://dba.stackexchange.com/questions/224149/should-the-filtering-columns-always-be-in-the-keys-includes) suggests that **all** filtered indexes should include their filtering columns. However, I find that indexes that exist **only as constraints** blur the lines enough that they merit me asking this question specifically about them.
To be very specific: What are the disadvantages of not putting the filtering columns in the INCLUDES
clause of a unique filtered index that is intended for the sole purpose of enforcing constraints?
### Example Code
CREATE TABLE #OwnersAndMachines
(
OwnersAndMachines INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
OwnerId INT NOT NULL,
MachineId INT NOT NULL,
Active BIT NOT NULL,
);
CREATE UNIQUE NONCLUSTERED INDEX NobodyOwnsSameMachineActively ON #OwnersAndMachines
(
MachineId
)
WHERE Active = 1
INSERT INTO #OwnersAndMachines
VALUES
(1, 200, 1),
(1, 185, 0),
(1, 150, 0),
(2, 300, 1),
(3, 100, 0),
(3, 185, 1)
SELECT * FROM #OwnersAndMachines;
/*
Violates constraint
*/
INSERT INTO #OwnersAndMachines
VALUES
(4, 200, 1)
[dbfiddle](https://dbfiddle.uk/dzD6VWmv) .
Asked by J. Mini
(1237 rep)
Jul 31, 2024, 07:37 AM
Last activity: Aug 2, 2024, 06:46 AM
Last activity: Aug 2, 2024, 06:46 AM