DBAs vs Developers: JSON in SQL Server

DBAs vs Developers: JSON in SQL Server

• 1.
  DBAs vs Developers: JSONin SQL Server | Bert Wagner | September 30, 2017 1
• 3.
  Background • BI developer@ Progressive Insurance for 6+ years • I ❤ JSON – I use it in APIs, hardware projects, websites • I also ❤ SQL – relational database structures 3
• 4.
  Overview • What isJSON? • Why use JSON? • When is it appropriate to store JSON in SQL? • Usage examples: • ETL and reporting • Database object maintenance • Performance parsing • Performance comparisons 4
• 5.
  5 What does JSONlook like? { “Make” : “Volkswagen”, “Year” : 2003, “Model” : { “Base” : “Golf”, “Trim” : “GL” }, “Colors” : [“White”, “Pearl”, “Rust”], “PurchaseDate” : “2006-10-05T00:00:00.000Z” }
• 6.
  6 Why use JSON? EasyProcessing var car = { "Make" : "Volkswagen" }; console.log(car.Make); // Output: Volkswagen car.Year = 2003; console.log(car); // Output: { "Make" : "Volkswagen", "Year" : 2003" } Javascript:
• 7.
  7 Why use JSON? APIs
• 8.
  8 Why use JSON? StorageSize <Car> <Make>Volkswagen</Make> <Year>2003</Year> <Model> <Base>Golf</Base> <Trim>GL</Trim> </Model> <Colors> <Color>White</Color> <Color>Pearl</Color> <Color>Rust</Color> </Colors> <PurchaseDate> 2006-10-05 00:00:00.000 </PurcaseDate> </Car> { “Make” : “Volkswagen”, “Year” : 2003, “Model” : { “Base” : “Golf”, “Trim” : “GL” }, “Colors” : [“White”, “Pearl”, Rust”], “PurchaseDate” : “2006-10-05T00:00:00.000Z” } XML: 225 Characters JSON: 145 Characters
• 9.
  9 Appropriate Usage Staging Data •Load data raw • Validate • Transform
• 10.
  10 Appropriate Usage Error Logging ErrorDateComponent Data 2016-03-17 21:23:39 GetInventory { "Make : "Volkswagen", "Year" : 2003} 2016-03-19 12:59:31 Login { "User" : "Bert", "Referrer" : "http://google.com", "AdditionalDetails" : "Invalid number of login attempts" }
• 11.
  11 Appropriate Usage Non-Analytical Data •Sessions • User preferences • Non-frequently changing variables • Admin emails • Static dropdown menus
• 12.
  12 Inappropriate Usage High-Performance Requirements
• 13.
  13 Inappropriate Usage Validation/Integrity Requirements
• 14.
  14 Inappropriate Usage Being Lazy
• 15.
  Demos 1. ETL andreporting 2. Database object maintenance 3. Performance parsing w/ computed column indexes 4. SQL JSON vs XML vs .NET performance comparisons 15
• 16.
  Performance Results -XML 16 • JSON faster in almost all categories • If considering entire app performance, maybe faster in all categories
• 17.
  Performance Results -.NET 17 • Competitive with C# libraries • Indexes on computed columns are BLAZING!
• 18.
  18 JSON – What’snew in SQL Server 2017? • Clustered column store indexes support nvarchar(max) • Compression • Faster (maybe) • In memory-optimized tables • Computed columns • All JSON functions supported
• 19.
  Recap 19 • Many good(and bad) uses for JSON in SQL exist • JSON can be fully manipulated in SQL Server 2016 • JSON is preferable to XML for new projects • JSON performance is comparable to .NET, faster with computed column indexes
• 20.
  Thank you! Twitter: @bertwagner Blog:https://bertwagner.com <- new post every Tuesday Vlog: https://bertwagner.com <- new video every Tuesday Email: bert@bertwagner.com 20
• 21.
  21 Appendix Software for keepingscreen region on top • On Top Replica Blog posts and YouTube videos: • SQL Server JSON Usage - Parsing • SQL Server JSON Usage - Creating • SQL Server JSON Usage - Updating, Adding, Deleting • Performance Comparisons - .NET • Performance Comparisons - XML • Performance Comparisons - .NET and XML Redux • JSON Computed Column Indexes • Jovan Popovic’s JSON posts Microsoft Connect • Add an option to JSON_MODIFY() to fully delete values from arrays