Sample Header Ad - 728x90

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