An entity-attribute-value table turns each object into hundreds of rows, so broad self-joins and scans grow quickly. For searches that require several attributes, use one selective EXISTS predicate per requirement and give each lookup an index whose leading columns match the equality filters.
Last updated: October 23, 2026.
CREATE INDEX IX_AttributeValue_Name_String_Object
ON dbo.AttributeValue (AttributeName, StringValue, ObjectId);
CREATE INDEX IX_AttributeValue_Name_Date_Object
ON dbo.AttributeValue (AttributeName, DateValue, ObjectId);
SELECT object.ObjectId
FROM dbo.Object AS object
WHERE EXISTS (
SELECT 1
FROM dbo.AttributeValue AS value
WHERE value.ObjectId = object.ObjectId
AND value.AttributeName = N'Status'
AND value.StringValue = N'Open'
)
AND EXISTS (
SELECT 1
FROM dbo.AttributeValue AS value
WHERE value.ObjectId = object.ObjectId
AND value.AttributeName = N'CreatedAt'
AND value.DateValue >= @FromDate
AND value.DateValue < @ToDate
);Adapt the index order to the actual predicates and data distribution. Equality columns usually lead, followed by the range column and then the entity key used to correlate the EXISTS subquery.
Keep values typed and searchable
Do not store dates and numbers only as strings if the application performs range comparisons. Separate typed columns, validated computed columns, or type-specific attribute tables avoid runtime conversions and make ordered indexes useful. The Microsoft index design guide recommends putting columns used in equality, range, and join predicates in the index key in an order suited to the query.
Use a unique constraint on (ObjectId, AttributeName) when each object may have only one value for an attribute. That both protects data integrity and prevents an accidental duplicate attribute from multiplying results.
Measure the plan before adding more indexes
Capture the actual execution plan and logical reads for representative parameter values. Each EXISTS branch should seek a small part of the attribute table rather than repeatedly scanning it. If a few attributes dominate searches, a carefully designed filtered index can be smaller, but its predicate must match a well-defined subset and the query predicate must be compatible with it.
Avoid creating one wide index for every possible combination. Extra indexes increase insert and update work, which is already significant in an EAV model. For attributes that define most business queries, consider promoting them to ordinary typed columns or a dedicated table. Continue with finding Query Store plans by index, designing composite keys, and understanding SQL Server join choices.
