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

Deliver advanced functionality faster and cheaper by exploiting SQL Server's ever-growing amount of built-in support for modern data formats. Learn about the growing support within SQL Server for operations and data transformations that have previously required third-party software and all the associated licensing and development costs. Benefit through a better understanding of what can be done inside the database engine with no additional costs or development time invested in outside software.Widely used types such as JSON and XML are well-supported by the database engine. The same is true of hierarchical data and even temporal data. Knowledge of these advanced types is crucial to unleashing the full power that's available from your organization's SQL Server database investment. __SQL Server Advanced Data Types__ explores each of the complex data types supplied within SQL Server. Common usage scenarios for each complex data type are discussed, followed by a detailed discussion on how to work with each data type. Each chapter demystifies the complex data and you learn how to use the data types most efficiently.The book offers a practical guide to working with complex data, using real-world examples to demonstrate how each data type can be leveraged. Performance considerations are also discussed, including the implementation of special indexes such as XML indexes and spatial indexes.**What You'll Learn** * Understand the implementation of basic data types and why using the correct type is so important * Work with XML data through the XML data type * Construct XML data from relational result sets * Store and manipulate JSON data using the JSON data type * Model and analyze spatial data for geographic information systems * Define hierarchies and query them efficiently through the HierarchyID type **Who This Book Is For**SQL Server developers and application developers who need to store and access complex data structures Table of Contents......Page 5 About the Author......Page 10 About the Technical Reviewer......Page 11 Introduction......Page 12 Numeric Data Types......Page 13 Character Strings......Page 22 Binary Data Types......Page 26 Dates and Times......Page 29 Miscellaneous Standard Data Types......Page 35 Summary of Advanced Data Types......Page 37 Why Is Using the Correct Data Type Important?......Page 38 Summary......Page 39 Understanding XML......Page 41 Well-Formed XML......Page 50 Understanding XSD Schemas......Page 55 XML Usage Scenarios in SQL Server......Page 58 Summary......Page 59 Using FOR XML RAW......Page 61 Using FOR XML AUTO......Page 89 Using FOR XML PATH......Page 104 Using FOR XML EXPLICIT......Page 113 Summary......Page 123 Querying XML......Page 125 Using exist()......Page 128 Using value( )......Page 130 Using query( )......Page 133 Using Relational Values in XQuery......Page 135 FLWOR......Page 139 Modifying XML Data......Page 145 Shredding XML with OPENXML( )......Page 153 Shredding XML with Nodes......Page 158 Using Schemas......Page 164 Summary......Page 167 Preparing the Environment......Page 169 Clustered Indexes......Page 171 Tables Without a Clustered Index......Page 172 Tables with a Clustered Index......Page 173 Clustering the Primary Key......Page 174 Performance Considerations for Clustered Indexes......Page 176 Creating a Clustered Index......Page 177 Primary XML Indexes......Page 179 Creating Primary XML Indexes......Page 180 Secondary XML Indexes......Page 183 Creating Secondary XML Indexes......Page 184 Performance Considerations for XML Indexes......Page 187 Summary......Page 191 Understanding the JSON Format......Page 192 JSON vs. XML......Page 199 De-Normalizing Data......Page 206 Config As Code......Page 208 Summary......Page 210 FOR JSON AUTO......Page 212 Working with Root Nodes......Page 215 Working with NULL Values......Page 218 Using Column Aliases......Page 223 Automatic Nesting......Page 226 FOR JSON PATH......Page 235 Summary......Page 239 OPENJSON() with Default Schema......Page 240 Shredding a Column......Page 243 Dynamic Shredding Based on Document Content......Page 246 OPENJSON( ) with Explicit Schema......Page 248 OPENJSON( ) with Path Expressions......Page 253 Shredding Data into Tables......Page 257 Summary......Page 260 Using ISJSON( )......Page 262 Using JSON_VALUE( )......Page 265 Using JSON_QUERY( )......Page 272 Using JSON_MODIFY()......Page 277 Indexing JSON Data......Page 282 Summary......Page 287 Understanding Spatial Data......Page 289 Spatial Data Standards......Page 296 Well-Known Text......Page 297 Well-Known Binary......Page 299 Spatial Reference Systems......Page 301 SSMS and Spatial Data......Page 304 Summary......Page 306 Chapter 11: Working with Spatial Data......Page 308 Constructing Spatial Data......Page 309 Querying Spatial Data......Page 316 Understanding Spatial Indexes......Page 341 Creating Spatial Indexes......Page 343 Summary......Page 349 Chapter 12: Working with Hierarchical Data and HierarchyID......Page 350 Hierarchical Data Use Cases......Page 351 Modeling Traditional Hierarchies......Page 353 Modeling Hierarchies with HierarchyID......Page 359 HierarchyID Methods......Page 365 Using ToString( )......Page 367 Using Parse( )......Page 369 Using GetRoot( )......Page 371 Using GetLevel( )......Page 372 Using GetDescendant( )......Page 373 Using GetReparentedValue( )......Page 376 Using GetAncestor( )......Page 379 Using IsDescendantOf( )......Page 380 Indexing HierarchyID Columns......Page 386 Summary......Page 391 Index......Page 393 Deliver advanced functionality faster and cheaper by exploiting SQL Server's ever-growing amount of built-in support for modern data formats. Learn about the growing support within SQL Server for operations and data transformations that have previously required third-party software and all the associated licensing and development costs. Benefit through a better understanding of what can be done inside the database engine with no additional costs or development time invested in outside software. Widely used types such as JSON and XML are well-supported by the database engine. The same is true of hierarchical data and even temporal data. Knowledge of these advanced types is crucial to unleashing the full power that's available from your organization's SQL Server database investment. SQL Server Advanced Data Types explores each of the complex data types supplied within SQL Server. Common usage scenarios for each complex data type are discussed, followed by a detailed discussion on how to work with each data type. Each chapter demystifies the complex data and you learn how to use the data types most efficiently. The book offers a practical guide to working with complex data, using real-world examples to demonstrate how each data type can be leveraged. Performance considerations are also discussed, including the implementation of special indexes such as XML indexes and spatial indexes. What You'll Learn Understand the implementation of basic data types and why using the correct type is so important Work with XML data through the XML data type Construct XML data from relational result sets Store and manipulate JSON data using the JSON data type Model and analyze spatial data for geographic information systems Define hierarchies and query them efficiently through the HierarchyID type Who This Book Is For SQL Server developers and application developers who need to store and access complex data structures