Should unique filtered indexes include their filtering column?

Question

Context

Suppose that you have a unique filtered index on a table. Said index exists only to enforce a constraint. 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 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.

Answer 1

The disadvantages are the same as listed in Should the filtering column(s) always be in the keys / includes?

You might intend the unique filtered index to only ever be used to enforce the constraint, but the implementation is still a physical index. SQL Server will consider that index as an access path for any statement (query, DML, or DDL) matching the filter.

Including the filtering column(s) broadens the potential index uses and avoids a number of counterintuitive traps, at the cost of widening the index. It's a normal index design decision with the same trade-offs, in other words.

Some people prefer to separate integrity constraints from performance indexes as a matter of principle. Others don't, for pragmatic reasons.