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
Product catalog is a common pattern in e-commerce. The fact is that products might have various properties. Some products might have weight, some color, taste, price, etc. If you want to create generic table that should store different products, this table could contain few columns that are common to all products and a lot of custom columns… Read more
In SQL Server 2016 CTP3.2 is added new option in FOR JSON clause – WITHOUT_ARRAY_WRAPPER see https://msdn.microsoft.com/en-us/library/mt631354.aspx 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
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 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
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
Please refer to the post http://blogs.technet.com/b/dataplatforminsider/archive/2015/12/08/speeding-up-business-analytics-using-in-memory-technology-in-sql-server-2016-and-azure-sql-database.aspx Thanks Sunil Agarwal… Read more
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
New database and samples for AdventureWorks 2016 CTP3 database are published on AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3. In SQLServer2016CTP3Samples.zip 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
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