bertwagner’s gists

bertwagner / Example JSON data.sql

Created February 12, 2017 15:14

TSQL Tuesday 87 - Example JSON Data

	-- This version of that data has new lines removed to save space.
	-- Please see https://gist.github.com/bertwagner/965acde93706a9a5d772509e56247a1c for the neatly formatted version of the data.

	DECLARE @WebsiteJson nvarchar(max) = '{ "Users": [ { "_id": "589f14e8427b0030d59615b1", "index": 0, "guid": "4a5ecd66-8c41-4553-9c1b-7597124b46e3", "isActive": true, "balance": "$1,418.71", "picture": "http://placehold.it/32x32", "age": 26, "eyeColor": "brown", "name": "Hanson Larson", "gender": "male", "company": "KENEGY", "email": "[email protected]", "phone": "+1 (804) 447-3852", "address": "698 Durland Place, Hachita, Louisiana, 1920", "about": "Consectetur laboris sunt proident ullamco ex excepteur duis cillum sit dolor occaecat officia. Sunt amet cupidatat enim mollit esse non minim dolore ullamco minim duis do. Sunt sint aliqua sit excepteur anim proident consequat magna reprehenderit laborum. Voluptate officia et duis sit laborum.\r\n", "registered": "2016-11-09T10:59:38 +05:00", "latitude": -72.754583,

bertwagner / JSON Parse 2014.sql

Created February 12, 2017 19:07

TSQL Tuesday 87 Parsing JSON Pre 2016

bertwagner / Parsing JSON 2016.sql

Created February 12, 2017 19:18

TSQL Tuesday 87 Parsing JSON in SQL

bertwagner / SQL Json Performance.sql

Last active February 19, 2017 13:41

Test data for SQL performance comparison

This file has been truncated, but you can view the full file.

	-- Car data source: https://github.com/arthurkao/vehicle-make-model-data
	IF OBJECT_ID('dbo.Cars') IS NOT NULL
	BEGIN
	DROP TABLE dbo.Cars;
	END
	CREATE TABLE dbo.Cars
	(
	Id INT IDENTITY(1,1),
	CarDetails NVARCHAR(MAX)
	);

bertwagner / SQL Json Performance Truncated.sql

Created February 19, 2017 13:43

Test data for SQL performance comparison (truncated)

	-- Car data source: https://github.com/arthurkao/vehicle-make-model-data
	IF OBJECT_ID('dbo.Cars') IS NOT NULL
	BEGIN
	DROP TABLE dbo.Cars;
	END
	CREATE TABLE dbo.Cars
	(
	Id INT IDENTITY(1,1),
	CarDetails NVARCHAR(MAX)
	);

bertwagner / SQL JSON Computed column.sql

Created February 19, 2017 14:00

Adding a computed column to a table storing JSON

	-- Remember to turn on "Include Actual Execution Plan" for all of these examples

	-- Before we add any computed columns/indexes, let's see our execution plan for our SQL statement with a JSON predicate
	SELECT * FROM dbo.Cars WHERE JSON_VALUE(CarDetails, '$.model') = 'Golf'
	/*
	Output:
	Id CarDetails
	----------- --------------------------------------------------
	1113 {"year":2001,"make":"VOLKSWAGEN","model":"GOLF"}
	2410 {"year":2002,"make":"VOLKSWAGEN","model":"GOLF"}

bertwagner / JSON SQL Index performance.sql

Last active February 20, 2017 17:10

Adding a nonclustered index to our computed column

	-- Add an index onto our computed column
	CREATE CLUSTERED INDEX CL_CarModel ON dbo.Cars (CarModel)

	-- Check the execution plans again
	SELECT DISTINCT * FROM dbo.Cars WHERE JSON_VALUE(CarDetails, '$.model') = 'Golf'
	SELECT DISTINCT * FROM dbo.Cars WHERE CarModel = 'Golf'
	-- We now get index seeks!

bertwagner / SQL pre2016 JSON performance.sql

Created February 19, 2017 14:29

Parsing JSON in pre-2016 SQL Server

	-- Let's compare how quick Phil Factor's JSON parsing function does against the new SQL 2016 functions
	-- Phil's parseJSON function can be downloaded from https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

	SELECT years.StringValue AS Year, makes.StringValue AS Make, models.StringValue AS Model FROM dbo.parseJSON(@cars) models
	INNER JOIN dbo.parseJSON(@cars) years ON models.parent_ID = years.parent_ID
	INNER JOIN dbo.parseJSON(@cars) makes ON models.parent_ID = makes.parent_ID
	WHERE models.NAME = 'model' AND models.StringValue = 'Golf' AND years.NAME = 'year' AND makes.NAME = 'make'

bertwagner / SQL 2016 indexed computed performance.sql

Last active February 20, 2017 17:24

SQL Server 2016 reutrns data in 1ms for indexed computed column

	-- Indexed computed column returns results in ~1ms
	SELECT * FROM dbo.Cars WHERE CarModel = 'Golf'

bertwagner / SQL JSON performance comparison with Json.Net.sql

Last active February 20, 2017 17:21

Comparing performance of SQL Server 2016 vs Json.Net

	-- Turn on stats and see how long it takes to parse the ~20k JSON array elements
	SET STATISTICS TIME ON

	-- Test #1
	-- Test how long it takes to parse each property from all ~20k elements from the JSON array
	-- SQL returns this query in ~546ms
	SELECT JSON_VALUE(value, '$.year') AS [Year], JSON_VALUE(value, '$.make') AS Make, JSON_VALUE(value, '$.model') AS Model FROM OPENJSON(@cars, '$')

	-- Test #2
	-- Time to deserialize and query just Golfs without computed column + index

Bert Wagner bertwagner