JSON In Clustered Columnstore Indexes

Jovan Popovic gives a use case for JSON data being part of a clustered columnstore index:

This is equivalent to collections that you might find in classic NoSQL database because they store each JSON document as a single entity and optionally create indexes on these documents. The only difference is CLUSTERED COLUMNSTORE index on this table that provides the following benefits:

  1. Data compression – CCI uses various techniques to analyze your data and choose optimal compression algorithms to compress data.

  2. Batch mode analytic – queries executed on CCI process rows in the batches from 100 to 900 rows, which might be much faster than row-mode execution.

I think it’s worth reading this in conjunction with Niko Neugebauer’s comments regarding strings in columnstore.

Related Posts

Triggering KB 4462481

Joe Obbish shows how you can recreate the error described in KB 4462481: Consider a query execution that meets all of the following criteria: 1. A parallel INSERT INTO… SELECT into a columnstore table is performed 2. The SELECT part of the query contains a batch mode hash join 3. The query can’t immediate get […]

Read More

Searching Complex JSON With SQL Server

Bert Wagner gives us a way that you can quickly search through complicated JSON: Computed column indexes make querying JSON data fast and efficient, especially when the schema of the JSON data is the same throughout a table. It’s also possible to break out a well-known complex JSON structure into multiple SQL Server tables. However, what […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728