JSON or Real Columns: Deciding Where Attributes Live

JSON or real columns is a question about how you use an attribute: filter on it, join on it, or just carry it along. Keys and search columns belong in real columns. Sparse extras can live in JSON. Flexible storage does not remove the need for indexes and rules.

The product table that keeps growing columns

One product has a color. Another has a voltage. A third has a size and a warranty. Soon the table has forty nullable columns, and most rows use five of them.

So someone says, “Put it all in a JSON column.” That can be a fine answer. It can also hide problems until the table is big. Let me build both designs and look.

This uses the native json type in SQL Server 2025. Each design gets 20,000 products. The document design has one json column. The column design has Color and Voltage as real columns.

DROP TABLE IF EXISTS #ProductDoc, #ProductCol, #Seed;
CREATE TABLE #ProductDoc (ProductId int PRIMARY KEY, Name nvarchar(50) NOT NULL, Attributes json NOT NULL);
CREATE TABLE #ProductCol (ProductId int PRIMARY KEY, Name nvarchar(50) NOT NULL,
                          Color nvarchar(40) NULL, Voltage int NULL);

WITH n AS (
    SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)
SELECT i AS ProductId, N'Product ' + CAST(i AS nvarchar(10)) AS Name,
       CHOOSE(i % 4 + 1, N'Blue', N'Red', N'Green', N'Black') AS Color,
       CASE WHEN i % 5 = 0 THEN 12 END AS Voltage
INTO #Seed FROM n;

INSERT #ProductCol SELECT ProductId, Name, Color, Voltage FROM #Seed;
INSERT #ProductDoc
SELECT ProductId, Name,
       CAST(CASE WHEN Voltage IS NULL THEN CONCAT('{"color":"', Color, '"}')
                 ELSE CONCAT('{"color":"', Color, '","voltage":', Voltage, '}') END AS json)
FROM #Seed;

SELECT TOP (3) ProductId, Attributes FROM #ProductDoc ORDER BY ProductId;

What an index does for each design

Count the blue products in both tables, with no index on color yet. Both queries scan. But the JSON table is bigger and every document must be opened. In my run, the JSON scan read 254 pages and the column scan read 142.

SET STATISTICS IO ON;

SELECT COUNT(*) AS blue_doc FROM #ProductDoc
WHERE JSON_VALUE(Attributes, '$.color') = N'Blue';

SELECT COUNT(*) AS blue_col FROM #ProductCol
WHERE Color = N'Blue';

SET STATISTICS IO OFF;

A real column can take an index right away. JSON cannot, but a computed column can. It pulls out the property, and you index that. Cast it to a sensible size first.

Now run three queries. The first two use the indexes and read 17 pages each. The third repeats JSON_VALUE in the WHERE clause, and it still reads 254. In my test, only the computed column name found the index.

ALTER TABLE #ProductDoc ADD ColorValue AS CONVERT(nvarchar(40), JSON_VALUE(Attributes, '$.color'));
CREATE INDEX IX_ProductDoc_Color ON #ProductDoc (ColorValue);
CREATE INDEX IX_ProductCol_Color ON #ProductCol (Color);
GO
SET STATISTICS IO ON;

SELECT COUNT(*) AS blue_by_computed_column FROM #ProductDoc WHERE ColorValue = N'Blue';
SELECT COUNT(*) AS blue_by_real_column FROM #ProductCol WHERE Color = N'Blue';
SELECT COUNT(*) AS blue_by_json_value FROM #ProductDoc
WHERE JSON_VALUE(Attributes, '$.color') = N'Blue';

SET STATISTICS IO OFF;

So JSON can be searched fast, if you plan for it. Each searched property needs its own computed column and index. At that point you are halfway to a real column.

The type check you lose

Here is the quieter cost. Say a bad import writes the voltage as the word lots. The JSON column accepts it without a word. The real column refuses it with error 245.

BEGIN TRY
    INSERT #ProductDoc (ProductId, Name, Attributes)
    VALUES (30001, N'Bad voltage', N'{"color":"Blue","voltage":"lots"}');
    SELECT 'JSON column accepted the row' AS result;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

BEGIN TRY
    INSERT #ProductCol (ProductId, Name, Color, Voltage)
    VALUES (30001, N'Bad voltage', N'Blue', N'lots');
    SELECT 'Real column accepted the row' AS result;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

The bad row now sits in the JSON table, waiting for the first report that sums voltage. Foreign keys and CHECK constraints work on real columns. For JSON, you must build each rule yourself.

Missing is not the same as null

One more trap. A document with no color key and a document with a color set to null both come back from JSON_VALUE as NULL. If your rule treats them differently, use JSON_PATH_EXISTS to tell them apart.

INSERT #ProductDoc (ProductId, Name, Attributes)
VALUES (30002, N'No color key', N'{"voltage":5}'),
       (30003, N'Null color', N'{"color":null,"voltage":5}');

SELECT ProductId, JSON_VALUE(Attributes, '$.color') AS color_value,
       JSON_PATH_EXISTS(Attributes, '$.color') AS has_color_key
FROM #ProductDoc
WHERE ProductId IN (30001, 30002, 30003)
ORDER BY ProductId;

Promote the attribute when it grows up

This decision is not permanent. If an attribute becomes central to filtering or integrity, move it into a real column. Before you convert, find the values that will not fit. This query counts them. The one bad voltage from earlier shows up.

SELECT COUNT(*) AS cannot_convert
FROM #ProductDoc
WHERE JSON_VALUE(Attributes, '$.voltage') IS NOT NULL
  AND TRY_CONVERT(int, JSON_VALUE(Attributes, '$.voltage')) IS NULL;

DROP TABLE IF EXISTS #ProductDoc, #ProductCol, #Seed;

Fix those rows first, then add the column and fill it. Keep the JSON property until every caller has moved over.

Start with real columns for what you search and join, and let JSON carry the rest.

A flexible document is not a data model, it is one part of the model.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.