Avoiding JSON can produce wide, sparse address tables. International addresses vary in line count, postal-code format, and even whether a postal code exists. JSON provides a lightweight semistructured alternative that omits irrelevant fields. section 3.5
Choose JSON according to how the data will be used:
- Prefer JSON when data is written often and queried infrequently. section 3.5
- Consider relational storage when the data is queried far more often than it is written, because T-SQL is efficient for querying structured values. section 3.5
- Do not automatically shred JSON into relational tables; match the schema to application requirements. section 3.6
Employee addresses
An Employees table can store an address in an NVARCHAR(MAX) column. SQL Server can generate a nested document with FOR JSON PATH, extract the employee identifier with JSON_VALUE, and shred address fields into relational columns with OPENJSON. section 3.5
Listing 3.22 Generating a JSON document
SELECT
1 AS 'EmployeeID'
, '5331 Rexford Court' AS 'Address.Line1'
, 'Montgomery' AS 'Address.City'
, 'AL' AS 'Address.State'
, '36116' AS 'Address.ZipCode'
FOR JSON PATH, ROOT ('EmployeeAddress') ;
Inspect the T-SQL pattern that generates a nested employee-address document. section 3.5
Listing 3.24 Shredding the JSON data into relational values
DECLARE @EmployeeAddress NVARCHAR(MAX) ;
SET @EmployeeAddress = (
SELECT
1 AS 'EmployeeID'
, '5331 Rexford Court' AS 'Address.Line1'
, 'Montgomery' AS 'Address.City'
, 'AL' AS 'Address.State'
, '36116' AS 'Address.ZipCode'
FOR JSON PATH, ROOT ('EmployeeAddress')
) ;
SELECT *
FROM OPENJSON(@EmployeeAddress, '$.EmployeeAddress[0]')
WITH (
EmployeeID SMALLINT,
Line1 NVARCHAR(64) '$.Address.Line1',
City NVARCHAR(64) '$.Address.City',
[State] NCHAR(2) '$.Address.State',
ZipCode NVARCHAR(10) '$.Address.ZipCode'
) ;
Inspect the T-SQL pattern that converts nested address data into relational values. section 3.5
