Projanco.com

SQL Server Advanced Data Types

JSON, XML, and Beyond

Peter A. Carter

SQL Server Advanced Data Types: JSON, XML, and Beyond

Peter A. Carter London, UK ISBN-13 (pbk): 978-1-4842-3900-1 ISBN-13 (electronic): 978-1-4842-3901-8 Library of Congress Control Number: 2018955129

Copyright ? 2018 by Peter A. Carter

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the book's product page, located at apress. com/9781484239001. For more detailed information, please visit source-code.

Contents

Introduction xv

Chapter 1: SQL Server Data Types1 Numeric Data Types1 Character Strings10 Binary Data Types14 Dates and Times17 Miscellaneous Standard Data Types23 Summary of Advanced Data Types25 Why Is Using the Correct Data Type Important?26 Summary 27

Chapter 2: Understanding XML29 Understanding XML29 Well-Formed XML38 Understanding XSD Schemas43 XML Usage Scenarios in SQL Server46 Summary 47

Chapter 3: Constructing XML with T-SQL49 Using FOR XML RAW49 Using FOR XML AUTO77 Using FOR XML PATH92 Using FOR XML EXPLICIT101 Summary 111

Chapter 4: Querying and Shredding XML113 Querying XML113 Using exist()116 Using value( )118 Using query( )121 Using Relational Values in XQuery123 FLWOR 127 Modifying XML Data133 Shredding XML141 Shredding XML with OPENXML( )141 Shredding XML with Nodes146 Using Schemas152 Summary 155

Chapter 5: XML Indexes157 Preparing the Environment157 Clustered Indexes159 Tables Without a Clustered Index160 Tables with a Clustered Index161 Clustering the Primary Key162

Performance Considerations for Clustered Indexes164 Creating a Clustered Index165 Primary XML Indexes167 Creating Primary XML Indexes168 Secondary XML Indexes171 Creating Secondary XML Indexes172 Performance Considerations for XML Indexes175 Summary 179

Chapter 6: Understanding JSON181 Understanding the JSON Format181 JSON vs. XML188 JSON Usage Scenarios195 n-Tier Applications with Rest APIs195 De-Normalizing Data195 Config As Code197 Analyzing the Log Data199 Summary 199

Chapter 7: Constructing JSON from T-SQL201 FOR JSON AUTO201 Working with Root Nodes204 Working with NULL Values207 Using Column Aliases212 Automatic Nesting215 FOR JSON PATH224 Summary 228

Chapter 8: Shredding JSON Data229 OPENJSON() with Default Schema229 Shredding a Column232 Dynamic Shredding Based on Document Content235 OPENJSON( ) with Explicit Schema237 OPENJSON( ) with Path Expressions242 Shredding Data into Tables246 Summary 249

Chapter 9: Working with the JSON Data Type251 Querying JSON Data 251 Using ISJSON( )251 Using JSON_VALUE( )254 Using JSON_QUERY( )261 Using JSON_MODIFY()266 Indexing JSON Data271 Summary 276

Chapter 10: Understanding Spatial Data279 Understanding Spatial Data279 Spatial Data Standards286 Well-Known Text287 Well-Known Binary289 Spatial Reference Systems291 SSMS and Spatial Data294 Summary 296

Chapter 11: Working with Spatial Data299 Constructing Spatial Data300 Querying Spatial Data307 Indexing Spatial Data332 Understanding Spatial Indexes332 Creating Spatial Indexes334 Summary 340

Chapter 12: Working with Hierarchical Data and HierarchyID341 Hierarchical Data Use Cases342 Modeling Traditional Hierarchies344 Modeling Hierarchies with HierarchyID350 HierarchyID Methods356 Working with HierarchyID Methods358 Indexing HierarchyID Columns377 Summary 382

Index385

Introduction

SQL Server Advanced Data Types attempts to demystify the complex data types that are available to developers in modern versions of SQL Server. Over the last couple of years, I have noticed that many SQL developers have heard of each of the complex data types available in SQL Server but often avoid using them, as they are not sure how these are best utilized. This has led to suboptimal solutions being developed, such as an incident that I recently experienced in which a very good and seasoned SQL developer implemented complex hierarchical logic using self joins, because he did not feel confident implementing the HierarchyID data type.

That inspired me to write this book--to help SQL and other developers responsible for writing T-SQL, as part of their applications, to better understand the complex data types available in SQL Server and give them the confidence to use these complex structures appropriately.

The book starts by exploring the simple, conventional data types that are available in SQL Server and reminds readers why making the right choices about data types can be so important. The book then moves on to offer in-depth discussions about the complex data types in SQL Server, namely, XML, JSON, HierarchyID, GEOGRAPHY, and GEOMETRY. Many of the code examples in the book are based on real-world problems and solutions that I have encountered in my time as an SQL Server consultant in London.

Many of the code examples in this book use the WideWorldImporters sample database. The GitHub repo for this database can be found at Microsoft/sql-server-samples/tree/master/samples/ databases/wide-world-importers and the .bak file can be downloaded from Microsoft/sql-server-samples/releases/download/ wide-world-importers-v1.0/WideWorldImporters-Standard.bak.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download