Contents
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
Understanding XML .............................................................................................29
Well-Formed XML ...............................................................................................38
Understanding XSD Schemas ...............................................................................43
XML Usage Scenarios in SQL Server .....................................................................46
Summary ...........................................................................................................47
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
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
Preparing the Environment ................................................................................157
Clustered Indexes .............................................................................................159
Tables Without a Clustered Index .......................................................................160
Tables with a Clustered Index ............................................................................161
Clustering the Primary Key ................................................................................162
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
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
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
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
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
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
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.
SQL Server Data 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.
Numeric Data Types
Some numeric data types in SQL Server are exact, while others are approximate. If a data type is exact, it stores a number to a fixed precision.
If a data type is approximate, in many cases, it will store a very close approximation of a number, instead of the number itself.
