Flexible attributes stop looking simple when a report needs two attributes at once. An entity-attribute-value design turns one ordinary product filter into several row comparisons. Decide which flexibility is real before making every reader reconstruct a product.
Understand the Row Shape You Are Buying
In a normal product table, one row can contain color, size, and price. In an EAV design, each attribute occupies a separate row. Product identity repeats while the attribute name determines what the value means.
That arrangement makes adding a new attribute look easy. It also moves type rules, required-field rules, and relationships out of ordinary column definitions. Queries must recover those rules whenever they read the data.
I ask which attributes the application filters and joins most frequently. I also ask which attributes need strict types and guaranteed presence. Those answers reveal whether flexible storage actually supports the workload or simply postpones its schema decisions.
EAV is not automatically wrong for every metadata problem. A genuinely open set of optional, rarely queried properties can justify specialized storage. The trouble begins when core business columns disappear into a universal string field.
Build the Smallest Useful Entity-Attribute-Value Example
The sample enforces one value per product and attribute name. Without that key, duplicate attribute rows could multiply self-join results. Keep the example in one connection because its tables are temporary.
DROP TABLE IF EXISTS #ProductAttributes;
CREATE TABLE #ProductAttributes
(
ProductId int NOT NULL,
AttributeName varchar(30) NOT NULL,
AttributeValue nvarchar(100) NOT NULL,
PRIMARY KEY(ProductId, AttributeName)
);
INSERT #ProductAttributes VALUES
(1,'Color',N'red'),(1,'Size',N'M'),(1,'Price',N'12.50'),
(2,'Color',N'blue'),(2,'Size',N'M'),(2,'Price',N'8.00'),
(3,'Color',N'red'),(3,'Size',N'L'),(3,'Price',N'unknown');The key prevents duplicate names, but it does not require every product to have Color or Size. It also cannot tell whether Price contains a valid decimal. The example's unknown price is structurally valid for this string column.
Attribute naming becomes another data-quality problem. Color, color, and Shade can behave differently depending on collation and validation. A controlled attribute catalog helps, but it still does not make a string value into a typed price.
Answer One Entity-Attribute-Value Filter With Multiple Row References
The business question is products with color red and size M. Those facts reside in different rows, so a simple pair of column predicates is unavailable. A self-join reconstructs the required combination.
SELECT color.ProductId
FROM #ProductAttributes AS color
JOIN #ProductAttributes AS size
ON size.ProductId = color.ProductId
WHERE color.AttributeName = 'Color'
AND color.AttributeValue = N'red'
AND size.AttributeName = 'Size'
AND size.AttributeValue = N'M'
ORDER BY color.ProductId;The declared inputs make product 1 the expected match. Adding another condition introduces another attribute lookup or a different reconstruction method. Duplicate attributes would further complicate those results without the enforced product-and-name key.
Conditional aggregation offers an alternative shape. It groups attribute rows into one product result and evaluates the reconstructed values. This is useful for comparison, but it still asks the engine to rebuild the entity from multiple records.
SELECT ProductId
FROM #ProductAttributes
GROUP BY ProductId
HAVING MAX(CASE WHEN AttributeName = 'Color' THEN AttributeValue END) = N'red'
AND MAX(CASE WHEN AttributeName = 'Size' THEN AttributeValue END) = N'M';MAX is safe here because the key allows at most one value for each attribute. Without that guarantee, MAX selects a string according to collation rather than resolving a business conflict. A pivot can produce a similar reconstruction with the same underlying concerns.
Keep Types and Indexes in the Discussion
Text comparisons do not provide numeric price ordering. A string containing 100 can sort before a string containing 20. Converting the value restores numeric interpretation only for records that pass conversion.
SELECT ProductId, AttributeValue,
TRY_CONVERT(decimal(12,2), AttributeValue) AS NumericPrice
FROM #ProductAttributes
WHERE AttributeName = 'Price'
ORDER BY ProductId;
CREATE INDEX IX_ProductAttributes_NameValue
ON #ProductAttributes(AttributeName, AttributeValue, ProductId);TRY_CONVERT exposes invalid prices as NULL, but it does not enforce a correct price at write time. Decide whether invalid values should fail validation or enter a rejection queue. A NULL conversion should not quietly become a zero-dollar product.
The added index can help attribute-name and value equality lookups. It cannot remove the need to combine several attributes for one product. Distribution statistics also mix many value domains unless the design isolates them carefully.
Range queries involving conversion have another cost. Applying TRY_CONVERT to the generic value column complicates direct index navigation for numeric ranges. Typed attribute tables can address that issue, but introduce explicit type-specific schema rather than preserving one universal field.
Put Stable Business Attributes in Real Columns
When color and size are core filters, ordinary columns make their meaning and type visible. A composite index can follow the actual filtering pattern. Required attributes become NOT NULL rules instead of checks scattered throughout application code.
DROP TABLE IF EXISTS #TypedProducts;
CREATE TABLE #TypedProducts
(
ProductId int NOT NULL PRIMARY KEY,
Color varchar(20) NOT NULL,
SizeCode varchar(5) NOT NULL,
Price decimal(12,2) NOT NULL CHECK (Price >= 0)
);
INSERT #TypedProducts VALUES (1,'red','M',12.50),(2,'blue','M',8.00);
CREATE INDEX IX_TypedProducts_ColorSize
ON #TypedProducts(Color, SizeCode);
SELECT ProductId, Price
FROM #TypedProducts
WHERE Color = 'red' AND SizeCode = 'M';Sparse columns suit a different requirement: many defined attributes with a high proportion of NULL values. They retain named, typed columns and supported column constraints. Their storage tradeoffs and feature restrictions still require measurement for the intended population.
Do not make every optional field sparse automatically. Populated sparse values have additional overhead, and not every type or table feature is compatible. Review the real NULL proportion and supported combinations before choosing that representation.
Use JSON for Optional Properties With Boundaries
A JSON document can keep flexible optional properties beside stable relational columns. SQL Server supports JSON functions in SQL Server 2016 and later. The example uses nvarchar storage, so it does not depend on the SQL Server 2025 native json type.
DROP TABLE IF EXISTS #JsonProducts;
CREATE TABLE #JsonProducts
(
ProductId int NOT NULL PRIMARY KEY,
Attributes nvarchar(max) NOT NULL CHECK (ISJSON(Attributes) = 1),
Color AS CONVERT(nvarchar(20), JSON_VALUE(Attributes, '$.color')),
SizeCode AS CONVERT(nvarchar(5), JSON_VALUE(Attributes, '$.size'))
);
INSERT #JsonProducts(ProductId, Attributes) VALUES
(1,N'{"color":"red","size":"M","finish":"matte"}'),
(2,N'{"color":"blue","size":"M"}');
CREATE INDEX IX_JsonProducts_ColorSize
ON #JsonProducts(Color, SizeCode);
SELECT ProductId
FROM #JsonProducts
WHERE Color = N'red' AND SizeCode = N'M';ISJSON validates JSON syntax, not the full business schema or required property types. The bounded computed columns also need input-length validation to avoid ambiguous truncation. Prefer real columns for identities and critical business values rather than hiding them inside optional metadata.
I compare entity-attribute-value queries with the real-column alternative using actual filters and plans. I also include invalid and missing attributes in the comparison. Flexible storage should not turn every report into an archaeological dig.
Which properties are genuinely open-ended, and which already have stable business definitions? Separate those groups before choosing storage. Keep the schema rules where writers and readers can both enforce them consistently.
For entity-attribute-value storage, define attribute ownership and validation at write time. Required core attributes need a reliable enforcement path. A nightly report that discovers invalid prices is too late for transactions already using them.
Migration also needs explicit conflict rules. Resolve duplicate attribute names, invalid numeric strings, and missing required fields before inserting into typed columns. Preserve rejected records with their entity identifiers so the move improves data quality without silently losing evidence.
Related reading on this blog: SQL SERVER Performance: JSON vs XML and Performance Benefit of Using SPARSE Columns?.
A flexible attribute model is not a free schema, it is a design that still needs explicit types and rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
