SQL Server Advanced Data Types JSON, XML, and Beyond — Taking full advantage of SQL Server’s ever-growing support for modern data — Peter A. Carter 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 https://doi.org/10.1007/978-1-4842-3901-8 Library of Congress Control Number: 2018955129 Copyright © 2018 by Peter A. Carter This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image, we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the author nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover designed by eStudioCalamar Cover image designed by Freepik (www.freepik.com) Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science+Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail [email protected], or visit www.apress.com/ rights-permissions. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at www.apress.com/bulk-sales. 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 www.apress. com/9781484239001. For more detailed information, please visit www.apress.com/source-code. Printed on acid-free paper Given the cover art, this book could only be dedicated to my son, Reuben. Table of Contents About the Author ���������������������������������������������������������������������������������xi About the Technical Reviewer �����������������������������������������������������������xiii Introduction ����������������������������������������������������������������������������������������xv Chapter 1: SQL Server Data Types ��������������������������������������������������������1 Numeric Data Types ����������������������������������������������������������������������������������������������1 Character Strings ������������������������������������������������������������������������������������������������10 Binary Data Types �����������������������������������������������������������������������������������������������14 Dates and Times �������������������������������������������������������������������������������������������������17 Miscellaneous Standard Data Types �������������������������������������������������������������������23 Summary of Advanced Data Types ����������������������������������������������������������������������25 Why Is Using the Correct Data Type Important? ��������������������������������������������������26 Summary�������������������������������������������������������������������������������������������������������������27 Chapter 2: Understanding XML �����������������������������������������������������������29 Understanding XML ���������������������������������������������������������������������������������������������29 Well-Formed XML������������������������������������������������������������������������������������������������38 Understanding XSD Schemas �����������������������������������������������������������������������������43 XML Usage Scenarios in SQL Server �������������������������������������������������������������������46 Summary�������������������������������������������������������������������������������������������������������������47 v TTaabbllee ooff CCoonnTTeennTTss Chapter 3: Constructing XML with T-SQL ��������������������������������������������49 Using FOR XML RAW �������������������������������������������������������������������������������������������49 Using FOR XML AUTO ������������������������������������������������������������������������������������������77 Using FOR XML PATH ������������������������������������������������������������������������������������������92 Using FOR XML EXPLICIT �����������������������������������������������������������������������������������101 Summary�����������������������������������������������������������������������������������������������������������111 Chapter 4: Querying and Shredding XML �����������������������������������������113 Querying XML ����������������������������������������������������������������������������������������������������113 Using exist() �������������������������������������������������������������������������������������������������116 Using value( ) �����������������������������������������������������������������������������������������������118 Using query( ) ����������������������������������������������������������������������������������������������121 Using Relational Values in XQuery ���������������������������������������������������������������123 FLWOR ���������������������������������������������������������������������������������������������������������127 Modifying XML Data ������������������������������������������������������������������������������������133 Shredding XML ��������������������������������������������������������������������������������������������������141 Shredding XML with OPENXML( ) ����������������������������������������������������������������141 Shredding XML with Nodes �������������������������������������������������������������������������146 Using Schemas �������������������������������������������������������������������������������������������������152 Summary�����������������������������������������������������������������������������������������������������������155 Chapter 5: XML Indexes ��������������������������������������������������������������������157 Preparing the Environment �������������������������������������������������������������������������������157 Clustered Indexes ���������������������������������������������������������������������������������������������159 Tables Without a Clustered Index ����������������������������������������������������������������160 Tables with a Clustered Index ����������������������������������������������������������������������161 Clustering the Primary Key ��������������������������������������������������������������������������162 vi TTaabbllee ooff CCoonnTTeennTTss Performance Considerations for Clustered Indexes ������������������������������������164 Creating a Clustered Index ��������������������������������������������������������������������������165 Primary XML Indexes ����������������������������������������������������������������������������������������167 Creating Primary XML Indexes ��������������������������������������������������������������������168 Secondary XML Indexes ������������������������������������������������������������������������������������171 Creating Secondary XML Indexes ����������������������������������������������������������������172 Performance Considerations for XML Indexes ��������������������������������������������������175 Summary�����������������������������������������������������������������������������������������������������������179 Chapter 6: Understanding JSON �������������������������������������������������������181 Understanding the JSON Format ����������������������������������������������������������������������181 JSON vs� XML ����������������������������������������������������������������������������������������������������188 JSON Usage Scenarios��������������������������������������������������������������������������������������195 n-Tier Applications with Rest APIs ���������������������������������������������������������������195 De-Normalizing Data �����������������������������������������������������������������������������������195 Config As Code ��������������������������������������������������������������������������������������������197 Analyzing the Log Data ��������������������������������������������������������������������������������199 Summary�����������������������������������������������������������������������������������������������������������199 Chapter 7: Constructing JSON from T-SQL ����������������������������������������201 FOR JSON AUTO ������������������������������������������������������������������������������������������������201 Working with Root Nodes ����������������������������������������������������������������������������204 Working with NULL Values ���������������������������������������������������������������������������207 Using Column Aliases ����������������������������������������������������������������������������������212 Automatic Nesting ���������������������������������������������������������������������������������������215 FOR JSON PATH �������������������������������������������������������������������������������������������������224 Summary�����������������������������������������������������������������������������������������������������������228 vii TTaabbllee ooff CCoonnTTeennTTss Chapter 8: Shredding JSON Data ������������������������������������������������������229 OPENJSON() with Default Schema ��������������������������������������������������������������������229 Shredding a Column ������������������������������������������������������������������������������������232 Dynamic Shredding Based on Document Content ���������������������������������������235 OPENJSON( ) with Explicit Schema �������������������������������������������������������������������237 OPENJSON( ) with Path Expressions �����������������������������������������������������������������242 Shredding Data into Tables ��������������������������������������������������������������������������246 Summary�����������������������������������������������������������������������������������������������������������249 Chapter 9: Working with the JSON Data Type ����������������������������������251 Querying JSON Data� �����������������������������������������������������������������������������������������251 Using ISJSON( )��������������������������������������������������������������������������������������������251 Using JSON_VALUE( ) ����������������������������������������������������������������������������������254 Using JSON_QUERY( ) ����������������������������������������������������������������������������������261 Using JSON_MODIFY() ���������������������������������������������������������������������������������266 Indexing JSON Data ������������������������������������������������������������������������������������������271 Summary�����������������������������������������������������������������������������������������������������������276 Chapter 10: Understanding Spatial Data ������������������������������������������279 Understanding Spatial Data ������������������������������������������������������������������������������279 Spatial Data Standards �������������������������������������������������������������������������������������286 Well-Known Text ������������������������������������������������������������������������������������������287 Well-Known Binary ��������������������������������������������������������������������������������������289 Spatial Reference Systems �������������������������������������������������������������������������291 SSMS and Spatial Data �������������������������������������������������������������������������������������294 Summary�����������������������������������������������������������������������������������������������������������296 viii TTaabbllee ooff CCoonnTTeennTTss Chapter 11: Working with Spatial Data �������������������������������������������299 Constructing Spatial Data ���������������������������������������������������������������������������������300 Querying Spatial Data ���������������������������������������������������������������������������������������307 Indexing Spatial Data ����������������������������������������������������������������������������������������332 Understanding Spatial Indexes ��������������������������������������������������������������������332 Creating Spatial Indexes ������������������������������������������������������������������������������334 Summary�����������������������������������������������������������������������������������������������������������340 Chapter 12: Working with Hierarchical Data and HierarchyID���������341 Hierarchical Data Use Cases �����������������������������������������������������������������������������342 Modeling Traditional Hierarchies �����������������������������������������������������������������������344 Modeling Hierarchies with HierarchyID �������������������������������������������������������������350 HierarchyID Methods �����������������������������������������������������������������������������������������356 Working with HierarchyID Methods �������������������������������������������������������������358 Indexing HierarchyID Columns ��������������������������������������������������������������������������377 Summary�����������������������������������������������������������������������������������������������������������382 Index �������������������������������������������������������������������������������������������������385 ix About the Author Peter A. Carter is a SQL Server expert with more than 15 years’ experience in database development, administration, and platform engineering. He is currently a consultant, based in London. Peter has written a number of books across a variety of SQL Server topics, including security, high availability, and automation. xi
Description: