Posts

Shred JSON Data into Rows and Columns with T-SQL

Image
Use OPENJSON with a WITH schema and CROSS APPLY to convert JSON objects and nested arrays into relational rows and columns; validate with ISJSON , set database compatibility level ≥130, and add computed/indexed columns for performance. Quick recipe (what to run) Validate JSON : use ISJSON() before shredding. Parse root object : CROSS APPLY OPENJSON(... ) WITH (...) to map properties to typed columns. Shred arrays : CROSS APPLY OPENJSON(rootJson, '$.ArrayName') to return rows, then OPENJSON(... ) WITH (...) to extract fields from each array element. Example: order JSON → rows and columns sql DECLARE @j NVARCHAR(MAX) = N' { "OrderId":"0001", "Customer":{"Name":"Amanda","Email":"a@example.com"}, "Items":[ {"Name":"Heated Blanket","Qty":1,"Price":49.99}, {"Name":"Glove Warmers","Qty":2,"Price":9.99} ...
Post a Comment
Read more

Improve SQL Server Query Performance with Searchable Arguments

Image
  Problem My SQL Server query is running slowly. The WHERE clause is comparing against a column that is indexed, but the SQL query isn't doing an index seek. It sure seems like it shouldn't be scanning. Why might that be? Solution This is a common problem, especially for newer TSQL developers that are less familiar with the inner working of the SQL Server query engine. This tip will look at 2 different queries where indexes aren't used by the optimizer, but could be if the query was tweaked. Next, it will explain why this small coding change will work. Finally, we will perform the tweak to see what sort of performance improvement can be expected. Glossary of Terms This tutorial will use 2 terms that might be new to readers. Argument –  An argument is another name for a Boolean expression that may appear in a join predicate, WHERE clause, or HAVING clause. Each of these clauses may have many arguments combined with AND and OR keywords. An argument might be "OrderDate = ...
Post a Comment
Read more