Indexing data in JSON product catalogs

In the previous post Designing Product Catalogs in SQL Server 2016 using JSON, I have described how you can design product catalog with JSON. Product catalog contains few columns that are common to all types of products and all custom/specific columns are stored as JSON key:value pairs: This is simple, flexible and generic structure that… Read more

Designing Product Catalogs in SQL Server using JSON

Product catalog is a common pattern in retail and e-commerce. The products in retail and e-commerce apps might have various properties/attributes. Depending on the type, some products might have dimensions and weight, others color, taste, price, number of doors, CPU, memory, etc. Attributes for this data may vary and can change over time to fit application… Read more

WITHOUT_ARRAY_WRAPPER – New important change in FOR JSON

In SQL Server 2016 CTP3.2 is added new option in FOR JSON clause – WITHOUT_ARRAY_WRAPPER see This option enables you to remove square brackets [ and ] that surround JSON text generated by FOR JSON clause. I will use the following example: SELECT 2015 as year, 12 as month, 15 as day FOR JSON… Read more

Columnstore Index: Real-Time Operational Analytics using In-Memory Technology

Starting with SQL Server 2016, you can implement real-time operational analytics by combining the OLTP workload with columnstore index. SQL Server offers three possible configurations (a) disk-based tables with nonclustered columnstore index (b) memory-optimized tables with columnstore index (c) clustered columnstore index with one or more btree indexes. I will blog in detail about these… Read more

CHECKSUM and BINARY_CHECKSUM might not return unique values for different inputs

CHECKSUM and BINARY_CHECKSUM create checksums for text inputs. Checksum values should be different for different input text. However, in some cases, returned values are not always unique for different inputs. You can find patterns of inputs that return same values. This is similar to modulo operator that returns the same remainder if you find the… Read more

Create relational view over JSON text

SQL Server 2016 enables you to use JSON_VALUE, JSON_QUERY, and OPENJSON functions to fetch data from JSON text. However, if you want to to query JSON data using standard relational models you can create views that encapsulate these functions. In AdventureWorks2016CTP3 database is added Sales.SalesOrder_json table with two columns: Info that contains various information about… Read more

Built-in functions for compression/decompression in SQL Server 2016

SQL Server 2016 provides built in functions for compression and decompression: COMPRESS – compress data using GZip algorithm and returns binary data. DECOMPRESS – decompress binary data using GZip algorithm and returns binary data. You will need to cast binary data to text if you have originally compressed text. These functions use standard GZip algorithm so you… Read more

AdventureWorksCTP3 JSON Sample

New database and samples for AdventureWorks 2016 CTP3 database are published on AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3. In file (attached in this post) you can find various example scripts that show how to use various features added in SQL Server 2016.  One of the folders in this archive is JSON folder… Read more

Using non-relational models in SQL Server

In this post I will explain how you can use both relational and non-relational data models in your database schema. Problem Relational database schema might contain a lot of tables that require many JOINs or separate queries to retrieve all necessary data in the query. Also, in order to insert new records, we need to… Read more