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 Contents......Page 3 Intro......Page 8 Numeric Data Types......Page 9 Character Strings......Page 18 Binary Data Types......Page 22 Dates and Times......Page 25 Miscellaneous Standard Data Types......Page 31 Summary of Advanced Data Types......Page 33 Why Is Using the Correct Data Type Important?......Page 34 Summary......Page 35 Understanding XML......Page 37 W ell-Formed XML......Page 46 Understanding XSD Schemas......Page 51 XML Usage Scenarios in SQL Server......Page 54 Summar y......Page 55 Using FOR XML RAW......Page 57 Using FOR XML AUTO......Page 85 Using FOR XML PATH......Page 100 Using FOR XML EXPLICIT......Page 109 Summary......Page 119 Quer ying XML......Page 121 Shredding XML......Page 149 Using Schemas......Page 160 Summary......Page 163 Preparing the Environment......Page 165 Clustered Indexes......Page 167 Primary XML Indexes......Page 175 Secondary XML Indexes......Page 179 Performance Considerations for XML Indexes......Page 183 Summary......Page 187 Understanding the JSON Format......Page 188 JSON vs. XML......Page 195 JSON Usage Scenarios......Page 202 Summary......Page 206 FOR JSON AUTO......Page 208 FOR JSON PATH......Page 231 Summary......Page 235 OPENJSON() with Default Schema......Page 236 OPENJSON( ) with Explicit Schema......Page 244 OPENJSON( ) with Path Expressions......Page 249 Summar y......Page 256 Querying JSON Data......Page 258 Indexing JSON Data......Page 278 Summary......Page 283 Understanding Spatial Data......Page 285 Spatial Data Standards......Page 292 SSMS and Spatial Data......Page 300 Summar y......Page 302 Working with Spatial Data......Page 304 Constructing Spatial Data......Page 305 Querying Spatial Data......Page 312 Indexing Spatial Data......Page 337 Summary......Page 345 Hierarchical Data & HierarchyID......Page 346 Hierarchical Data Use Cases......Page 347 Modeling Traditional Hierarchies......Page 349 Modeling Hierarchies with HierarchyID......Page 355 HierarchyID Methods......Page 361 Indexing HierarchyID Columns......Page 382 Summary......Page 387 Index......Page 389 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