Desiderata Errata

Continuing the JSON in SQL Server posts found  here  and  here , a co-worker asked the interesting question "can you index the key values in a small SQL Server text column using JSON?" I didn't know the answer to that, so here we go. Setup The test box remains the same: Core i7 CPU, 16 GB RAM, 1 TB 7200 RPM data disk. SQL Server 2019. The DicomFile table had a new column added, populated from the indexed SQL columns, and a new nonclustered index added. alter table DicomFile add IndexedJson varchar(1000); go update DicomFile  set IndexedJson = concat('{'         , '"SopClassUid": "', SopClassUid, '",'         , '"SopInstanceUid": "', SopInstanceUid, '",'         , '"PatientId": "', PatientId, '",'         , '"StudyInstanceUid": "', StudyInstanceUid, '",'         , '"SeriesInstanceUid": "', SeriesInsta...

As a continuation of this performance comparison , I wanted to also compare performance in SQL Server's supposed strong point: reporting on very large relational data sets. Does it truly perform better than MongoDB? Again, this will attempt to be a mostly vanilla comparison, without the different optimizations that can be done in both platforms, for a couple of typical reporting queries. Setup The test box remains the same: Core i7 CPU, 16 GB RAM, 1 TB 7200 RPM data disk. SQL Server 2019, MongoDB 6.0.1. Data was randomly generated using a console app and inserted into both platforms. For SQL Server, I created both a relational table structure, and a columnstore indexed table that is more typically seen in reporting databases. The columnstore table was created as a flat copy of the relational tables, and a SELECT FROM INSERT statement populated the data. MongoDB received the same data, with the Customer and Product data embedded in each document to avoid doing any lookups or joins....