Peter A. Carter - SQL Server Advanced Data Types: JSON, XML, and Beyond

SQL Server Advanced Data Types JSON, XML, and Beyond Taking full advantage of SQL Servers ever-growing support for modern data Peter A. Carter SQL Server AdvancedData TypesJSON, XML, and BeyondPeter A. CarterSQL 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 orders-ny@springer-sbm.com, 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 rights@apress.com, 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 books 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 couldonly be dedicated to my son, Reuben.Table of Contents v Table of Con T T able of Con enT en s vi Table of Con T T able of Con enTs vii Table of Con T T able of Con enT en s viii Table of Con T T able of Con enTs ix About the AuthorPeter 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 About the Technical ReviewerIan Stirk is a freelance SQL Server consultant based in London. In addition to his day job, he is an author, creator of software utilities, and technical reviewer who regularly writes book reviews for www.i-programmer.info. He covers every aspect of SQL Server and has a specialist interest in performance and scalability. If you require help with your SQL Server systems, feel free to contact him at ian_stirk@yahoo.com or www.linkedin.com/ in/ian-stirk-bb9a31. Ian would like to thank Peter Carter, Jonathan Gennick, and Jill Balzano for making this book experience easier for him.

None of us stands alone, and with this in mind, Ian would like to thank these special people: Pat Richards, Bhargava Ganti, Jon McCabe, Nick Fairway, Aida Samuel, Paul Fuller, Vikki Singini, Rob Lee, Gerald Hemming, and Jordy Mumba. Ians fee for his work on this book has been donated to the Deworm the World Initiative (www.givingwhatwecan.org/report/deworm/). xiii IntroductionSQL 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 bookto 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 github.com/Microsoft/sql-server-samples/tree/master/samples/ databases/wide-world-importers and the .bak file can be downloaded from github.com/Microsoft/sql-server-samples/releases/download/ wide-world-importers-v1.0/WideWorldImporters-Standard.bak. xv CHAPTER 1SQL ServerData Types SQL Server 2017 provides a wide range of basic data types that can store data, such as character strings, numeric data, binary data, and dates.

In this chapter, I will review each of the basic data types available in SQL Server, looking at the differences between similar data types. I will also discuss the importance of using the correct data type for each use case. Tip The following sections discuss the acceptable ranges for each data type. It is worth noting, however, that all data types can also be set to NULL, indicating that the value is unknown. Numeric Data Types