SQL Server Statistics Questions

https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/

Try as I might, I find it hard to over-emphasize the importance of statistics to SQL Server. Bad or missing statistics leads to poor choices by the optimizer: The result is horrific performance. Recently, for example, I watched a query go from taking hours to taking seconds just because we got a good set of statistics on the data. The topic of statistics and their maintenance is not straightforward. Lots of questions occur to people when I’m doing a presentation about statistics, and some get asked in front of the rest of the audience. Then there are other questions that get asked later on, in conversation. Here are some of those other questions….

What’s the difference between statistics on a table and statistics on an index?

There is no essential difference between the statistics on an index and the statistics on a table. They’re created at different points and, unless you’re creating the statistics manually yourself, they’re created slightly differently. The statistics on an index are created with the index. So, for an index created on pre-existing data, you’ll get a full scan against that data as part of the task of creating the index which is also then used to create the statistics for the index. The automatically-created statistics on columns are also usually created against existing data when the column is referenced in one of the filtering statements (such as WHERE or JOIN .. ON). But these are created using sampled data, not a full scan. Other than the source and type of creation, these two types of statistics are largely the same.

How can the Query Optimizer work out how many rows will be returned from looking at the statistics?

This is the purpose of the histogram within the statistics. Here’s an example histogram from the Person.Addresstable in AdventureWorks2012:

If I were doing a search for the address ‘1313 Mockingbird Lane’ then the query optimizer is going to look at the histogram to determine two things:

• Is it possible that this set of statistics contains this value
• If it does contain the value, how many likely rows will be returned

The RANGE_HI_KEY column shows the top of a set of data within the histogram, so a search for ‘1313’ would place it within the step represented by row ten which has a RANGE_HI_KEY value of ‘137 Lancelot Dr.’ So the first question is answered. The optimizer will then look at the AVG_RANGE_ROWS to determine the average number of rows that match any given value within the step. So in this case, the optimizer will assume that there are 1.623188 rows returned for the value. But, these are all estimates. In reality, the database doesn’t contain the value ‘1313 Mockingbird Lane

When data changes, SQL Server will automatically maintain the statistics on indexes that I explicitly create, if that setting is enabled. Does it also maintain the statistics automatically created on columns?

As data changes in your tables, the statistics - all the statistics - will be updated based on the following formula:

• When a table with no rows gets a row
• When 500 rows are changed to a table that is less than 500 rows
• When 20% + 500 are changed in a table greater than 500 rows

By ‘change’ we mean if a row is inserted, updated or deleted. So, yes, even the automatically-created statistics get updated and maintained as the data changes.

Where are the statistics actually stored? Do they take up much space? Can I save space by only having the essential ones?

The statistics themselves are stored within your database in a series of internal tables that include sysindexes. You can view some of the information about them using the system views sys.stats and sys.indexes, but the most detail is gleaned using a function, DBCC SHOW_STATISTICS. The statistics themselves take very little space. The header is a single row of information. The density is a set of rows with only three columns, equal in the number of rows to the number of columns defining the key columns of the statistic. Then you have the histogram. The histogram is up to 200 rows and never exceeds that amount. This means statistics do not require much room at all. While you can save a little bit of space by removing unneeded statistics, the space savings are too small to ever be worthwhile .

How do I know when stats were last updated?

You can look at the header using DBCC SHOW_STATISTICS. This contains a bunch of general information about the statistics in question. This example is from the Person.Address table:

As you can see, the last time the statistics were updated was January 4, 2013 at 7:01AM.

How reliable is the automated update of statistics in SQL Server?

You’d have to define what you mean by reliable. They are very reliable. They’re also sampled and automated to update on the criteria that we outlined in the first question. If the data in your system is fairly well distributed, as is usually the case, then the sampled statistics will work well for you. By ‘well distributed’ I mean that you’ll get a consistent view of all the available data by pulling just a sample of the data. Most systems, most of the time, will have reasonably well distributed data. But, almost every system I’ve worked with has exceptions. There always seems to be that one rogue table or that one index that’s got a very weird distribution of data, or gets updated very frequently, but not frequently enough to trigger an automatic update of the statistics. In this situation, the statistics can get stale or be inaccurate. But the problem isn’t the automated process. It’s that this data is skewed. These are the situations where you’ll need to manually take control of your statistics. This should be an exceptional event in most systems.

Are there any scripts or tools that will help me maintain statistics?

SQL Server provides two basic commands to help you to maintain your statistics, sp_updatestats and UPDATE STATISTICS. Sp_updatestats will look at all the statistics within a database to see if any rows have been modified in the table that the statistics support. If one or more rows have been modified, then you’ll get a sampled update of the statistics. UPDATE STATISTICS will update the statistics in the way that you specify, against the object that you specify; whether it is a table, an index, or a specific set of statistics.

Do we update statistics before or after we rebuild/reorganize indexes?

Just remember that, when you create an index, part of the task of creating that index is to create the statistics using a full scan of the data. Since a rebuild of an index is effectively a ‘drop and recreate’ of the index, the statistics are also recreated. I’ve frequently seen maintenance scripts that rebuild indexes and then update statistics usingsp_updatestats. This basically replaces a good set of statistics based on a full scan with a less-accurate set of statistics based on random sampling. So if you are rebuilding indexes, I would not recommend updating the statistics since this is extra work for a less effective statistic.

Now when you reorganize an index, no modifications of any kind are made to the statistics. So if you’ve had enough modifications to the data that you feel you should also update the statistics, go ahead and do it for indexes that you’ve reorganized because you won’t be hurting any other work.

If you absolutely must update the statistics and rebuild the indexes and you’re not going to try to tightly control exactly which indexes and tables you do this on, then the best practice would be to update the statistics first and then rebuild the indexes second.

Is using UPDATE STATISTICS WITH FULL SCAN the same as the statistics update that happens during an index rebuild?

Yes. The keyword and tricky phrase that you have to look at is WITH FULL SCAN. That indicates that the full data set is used to create/update the statistics. Since rebuilding an index is, to a degree, almost the same as recreating the index, you’re getting a full scan of the data set to put the index back together. This full scan also updates the statistics. If you run UPDATE STATISTICS WITH FULL SCAN, once again, it’s looking at all the data.

How do you determine when you need to manually update statistics?

This is one of the hardest questions about statistics to answer, because there is no hard-and-fast formula. The short answer is that you need to determine whether the statistics accurately enough represent the distribution of the data, and update them if it doesn’t. You need to update your statistics manually when the automatic processes are either not occurring frequently enough to provide you with a good set of statistics or because the sampled nature of the automatic updates is causing your statistics to be inaccurate. The only way to know whether you need to do these things is to track the behavior of queries within your system in order to spot when the optimizer starts making bad choices. This is probably because the statistics are not reflecting the data in a way that leads to good execution plans. It’s very easy to say, but a lot of work to do.

How often should I run a manual update of my statistics?

The answer to that depends on many circumstances. I’ve worked on systems that never needed any manual intervention on the statistics at all. At other times I’ve experienced a problem where we were running a manual update of the statistics every two minutes (that was a horrifically broken database and indexing design, not a pattern to be emulated). More usually, you’ll be dealing with a table that gets lots of inserts, maybe with an index on a datetime column, or an identity column, so that the data is outside the range of the statistics, but, there’s not enough activity to cause the automatic update to fire. Then, you’ll need to manually update statistics on a scheduled basis.

But how often? Often enough. You’ll need to determine that period based on your system and your circumstances. There’s not a formula I can provide that will precisely tell you when to run a manual update on your statistics. Further, I can’t tell you how to sample your statistics either. You may be experiencing random sampling that is perfect , or you may need some more specified degree of sampling right up to a full scan. You’ll need to experiment to understand what the right answer is in your circumstances.

Is there a way to change the sample rate for particular tables in SQL Server?

You can define how statistics sample the table when you create them or update them. You can specify either the number of rows to be sampled or the percentage of the table to be sampled. You can even specify that 0 percent or 0 rows be sampled. This will update your statistics, but with no actual statistics data. It’s probably a dangerous choice. If you use sp_update stats or UPDATE STATISTICS you can specify, by using the RESAMPLE command, the sample rate that you specified when you created the statistics should then be reused. If you controlled the sample rate directly, the next time you use RESAMPLE, that sample rate will be used again. This includes statistics created on columns and on indexes.

One point worth noting is that the automatic update of statistics will be a sampled update. If you have a very specific need for a particular sample rate on statistics and there’s a good chance that the automatic maintenance could tax your system too much, you might consider turning off the automatic update for that table or set of statistics. You can do this by specifying NORECOMPUTE option when you either UPDATE or CREATE STATISTICS. You have to use theSTATISTICS_NORECOMPUTE option when you create an index. You can specify NORECOMPUTE with a manual update of the statistics. But, if you do this, and the data is changing, you need to plan for manual updates to those statistics.

Can you create a set of statistics in SQL Server like you do in Oracle?

Oracle allows you to create custom statistics all the way down to creating your own histogram. SQL Server doesn’t give you that much control. However, you can create something in SQL Server that doesn’t exist in Oracle; filtered statistics. These are extremely useful when dealing with partitioned data or data that is wildly skewed due to wide ranging data or lots of nulls. Using AdventureWorks2012 as an example, I could create a set of statistics on multiple columns such as TaxAmt and CurrencyRateID in order to have a denser, more unique, value for the statistics than would be created by the optimizer on each of the columns separately. The code to do that looks like this:

CREATE STATISTICS TaxAmtFiltered

ON Sales.SalesOrderHeader (TaxAmt,CurrencyRateID)

WHERE TaxAmt > 1000 AND CurrencyRateID IS NOT NULL

WITH FULLSCAN;

This may help the optimizer to make better choices when creating the execution plan, but you’ll need to test it in any given setting.

Can you have statistics on a View?

No, and yes. No, your basic view is nothing but a query. That query doesn’t have statistics. It’s the same as a SELECT query inside a stored procedure or a batch statement. But, you can create a construct called an indexed view, or materialized view, which is a clustered index based on the query that defines the view. That’s an index, so it gets statistics just like any other index. Further, if you run queries that reference the columns in a filtering clause in the new clustered index, statistics can be created on those columns. While this is, strictly speaking, not the same as statistics on a view, it’s as close as you can get within SQL Server.

Are statistics created on temporary tables?

Yes. The major difference between a table variable and a temporary table is that a temporary table has statistics. The rules for the creation and maintenance of these statistics are exactly the same as for a regular table within SQL Server. So if you reference a column in a temporary table in a filtering command in T-SQL such as WHERE orJOIN, then a set of statistics will get created. Unfortunately, the creation of the statistics causes a statement recompile. This is a potential disadvantage of temporary tables: For small statements this is a cheap operation. For larger queries this can be very expensive. That’s a reason why you have to be careful about how you work with your temporary tables.

How does partitioning affect the statistics created by SQL Server?

Partitioning within SQL Server is defined through the creation of a clustered index. This clustered index has a full set of statistics based on all the data in the partition. If we’re talking about very large partitions and very large amounts of data, then there’s a good chance that the set of statistics may not be terribly accurate for the queries within your system. Oh, it will help you determine which partition to use very accurately, but within the partition you may still be seeing scans where a seek is possible. In order to help the optimizer, it’s a very good idea to create a set of manual statistics on each partition. This ensures that the data distribution of the partition is available to the optimizer to help it with making a good choice of query plan when executing your queries. For some additional details, read this overview from the SQL Server Customer Advisory Team (SQLCAT).

What kind of statistics are provided for SQL Server through a linked server?

None. The data on a linked server will have whatever statistics are provided by the database you’re connecting to through the linked server, on that server, but it won’t pass any statistics back to your system. If you need statistics on the data in the linked server then you’ll need to load that data into a table or temporary table (not a table variable) and create indexes and/or statistics on that table.

Can statistics be imported/exported?

Yes. If you look at the “Generate and Publish Scripts” wizard for a database it is possible to set up a situation where you not only script out the database, but the statistics that define that database as well. Within SQL Server Management Studio (SSMS), right click on the database in question and select “Tasks” from the context menu and then “Generate Scripts” from the sub-menu. This will launch the wizard. You can choose the objects you’re interested in and then click Next until you get to the “Set Scripting Options” step. Here, you want to click the ‘Advanced’ button. Scroll down and you’ll find the option “Script Statistics.” You can select to script out just the base statistics or include the histogram as you can see selected below.

You can then output the scripts. All the objects you selected are then generated out to a script which you can use to create a new database. If you take a look at the script, you can see one of two commands, either an UPDATE STATISTICS command for objects like indexes where a set of statistics are automatically created, or CREATE STATISTICS for sets of statistics that you created manually or were created automatically for you by SQL Server. Each of these has an additional option defined WITH STATS_STREAM and then a binary value:

While SQL Server can generate this binary information, you can’t. If you look for the documentation forSTATS_STREAM you won’t find it. Obviously, you can use this feature since it’s supplied by Microsoft. You can even generate your own STATS_STREAM value by using DBCC SHOW_STATISTICS WITH STATS_STREAM. But the documentation there reminds us: Not supported. Future compatibility is not guaranteed. So exercise caution when using this.

Conclusion

Dealing with statistics is definitely one of the more frustrating tasks of maintaining your SQL Server instances: However, as you can see, you have quite a large number of options which will enable you to get your statistics optimized on your server. Just remember that just as your data is not always generic, there is no generic solution for the maintenance of statistics. You’ll need to conform the solution you use to the data and the queries that are running on your system.

Rebuild All indexes and update statistics

http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/

USE MyDatabase
GOEXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC sp_updatestatsGO

https://www.simple-talk.com/sql/learn-sql-server/json-support-in-sql-server-2016/

JSON support in SQL Server 2016

15 December 2015

by Robert Sheldon

At last, SQL Server has caught up with other RDBMSs by providing a useful measure of JSON-support. It is a useful start, even though it is nothing like as comprehensive as the existing XML support. For many applications, what is provided will be sufficient. Robert Sheldon describes what is there and what isn't.

SQL Server 2016 is finally adding support for JSON, a lightweight format for exchanging data between different source types, similar to how XML is used. JSON, short for JavaScript Object Notation, is based on a subset of the JavaScript programming language and is noted for being human readable and easy for computers to parse and generate.

According to Microsoft, it is one of the most highly ranked requests on the Microsoft connect site and so for many, its inclusion in SQL Server is welcome news. That is, unless you were expecting the same sort of robust support we've seen with XML. SQL Server 2016 does not approach JSON with such vehemence, nor does it match what you'll find in products such as PostgreSQL.

SQL Server 2016 includes no JSON-specific data type and consequently none of the kinds of methods available to the XML data type. SQL Server 2016 continues to use the NVARCHAR type to store JSON data. However, it does provide several important T-SQL language elements that make working with JSON much easier than it has been in the past, so Microsoft is at least moving in the right direction, even if it still has some catching up to do.

Getting to know JSON

Although JSON is a bit more complex than what we'll cover here, it can help to have a basic understanding of what makes up a JSON code snippet before starting in on the SQL Server support. At its most basic, a JSON snippet can contain objects, arrays, or both. An object is an unordered collection of one or more name/value pairs (properties), enclosed in curly braces, as shown in the following example:

 {"FirstName":"Terri", "Current":true, "Age":42, "Phone":null}

For each property, the name component (FirstName, Current, Age, and Phone) is enclosed in double quotes and followed by a colon. The name component, sometimes referred to as the key, is always a string. The property's value follows slightly different rules. If the value is a string, you should enclose it in double quotes. If it is a numeric value, Boolean value (true or false), or null value, do not enclose it in quotes.

An array is simply an ordered collection of values, enclosed in square brackets, as in the following example:

 ["Terri", true, 42, null]

An array supports the same types of values as an object: string, number, true, false, or null. In addition, both objects and arrays can contain other objects and arrays as their values, providing a way to nest structures, as shown in the following example:

{
   "Employees":[
      {
         "Name":{
            "First":"Terri",
            "Middle":"Lee",
            "Last":"Duffy"
         },
         "PII":{
            "DOB":"1971-08-01",
            "NatID":"245797967"
         },
         "LoginID":"adventure-works\\terri0"
      },
      {
         "Name":{
            "First":"Roberto",
            "Middle":null,
            "Last":"Tamburello"
         },
         "PII":{
            "DOB":"1974-11-12",
            "NatID":"509647174"
         },
         "LoginID":"adventure-works\\roberto0"
      }
   ]
}

At the top level, we have a JSON object that includes a single property. The property's name is Employees, and the value is an array, which contains two values. Each array value is a JSON object that includes the Name, PII, andLoginID properties. The Name and PII values are also JSON objects, which contain their own name/value pairs.

As we work through the examples in this article, you'll get a better sense of how these various components work.

Formatting query results as JSON

One of the JSON-related features supported in SQL Server 2016 is the ability to return data in the JSON format, which we do by adding the FOR JSON clause to a SELECT statement. We'll explore the basics of how to use a FOR JSON clause to return data in the JSON format, using either the AUTO argument or the PATH argument.

First, however, we need some data on which to work. The following SELECT statement retrieves two rows from thevEmployee view in the AdventureWorks2016CTP3 database:

USE AdventureWorks2016CTP3;
go

SELECT FirstName, MiddleName, LastName, 
  EmailAddress, PhoneNumber
FROM HumanResources.vEmployee
WHERE BusinessEntityID in (2, 3);

It returns the following results, although you might see some differences with the final product, since the data and examples are based on the CTP 3 release of SQL Server 2016:

FirstName	MiddleName	LastName	EmailAddress	PhoneNumber
Terri	Lee	Duffy	terri0@adventure-works.com	819-555-0175
Roberto	NULL	Tamburello	roberto0@adventure-works.com	212-555-0187

AUTO mode

To return these results as JSON, to support a specific application, we simply add the FOR JSON clause to the statement, as shown in the following example.

SELECT FirstName, MiddleName, LastName, 
  EmailAddress, PhoneNumber
FROM HumanResources.vEmployee
WHERE BusinessEntityID in (2, 3)
FOR JSON AUTO;

Notice that the clause includes the AUTO argument, which indicates that the results should be returned in AUTOmode. When you specify this mode, the database engine automatically determines the JSON format, based on the order of the columns in the SELECT list and the tables in the FROM clause. In this case, the FOR JSON AUTO clause causes the SELECT statement to return the following results.

 [{"FirstName":"Terri","MiddleName":"Lee","LastName":"Duffy","EmailAddress":"terri0@adventure-works.com","PhoneNumber":"819-555-0175"},{"FirstName":"Roberto","LastName":"Tamburello","EmailAddress":"roberto0@adventure-works.com","PhoneNumber":"212-555-0187"}]

From these results, you might be able to see that the JSON output includes an array that contains two values, with each value a JSON object. Not surprisingly, as the results become more involved, it becomes more difficult to read them. In such cases, you can use a local or online JSON formatter/validator to turn the JSON snippet into something more readable. For example, I fed the previous results into the formatter athttps://jsonformatter.curiousconcept.com/ and came up with the following JSON:

[
   {
      "FirstName":"Terri",
      "MiddleName":"Lee",
      "LastName":"Duffy",
      "EmailAddress":"terri0@adventure-works.com",
      "PhoneNumber":"819-555-0175"
   },
   {
      "FirstName":"Roberto",
      "LastName":"Tamburello",
      "EmailAddress":"roberto0@adventure-works.com",
      "PhoneNumber":"212-555-0187"
   }
]

As you can see, it is now much easier to see our top-level array and the two object values it contains. Each object corresponds to a row returned by the SELECT statement. Going forward, I'll show only the formatter-fed results so they're more readable, but know that SQL Server returns the data as a single-line value, without all the whitespace and line breaks, as you saw above.

Now that you've gotten a taste of the FOR JSON AUTO clause, let's look at what happens when we join tables:

SELECT e.BirthDate, e.NationalIDNumber, e.LoginID,
  p.FirstName, p.MiddleName, p.LastName
FROM HumanResources.Employee e INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID in (2, 3)
FOR JSON AUTO;

As our SELECT statement becomes more complex, so too does the JSON output, as shown in the following results:

[ 
   { 
      "BirthDate":"1971-08-01",
      "NationalIDNumber":"245797967",
      "LoginID":"adventure-works\\terri0",
      "p":[
         {
            "FirstName":"Terri",
            "MiddleName":"Lee",
            "LastName":"Duffy"
         }
      ]
   },
   {
      "BirthDate":"1974-11-12",
      "NationalIDNumber":"509647174",
      "LoginID":"adventure-works\\roberto0",
      "p":[
         {
            "FirstName":"Roberto",
            "LastName":"Tamburello"
         }
      ]
   }
]

The information from the Person table is now part of the p array, which itself is one of the values in the parent object. As you'll recall, AUTO mode formats the results based on the order of the columns in the SELECT list and the tables in the FROM clause, so let's mix up that column order:

SELECT p.FirstName, p.MiddleName, p.LastName,
  e.BirthDate, e.NationalIDNumber, e.LoginID
FROM HumanResources.Employee e INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID in (2, 3)
FOR JSON AUTO;

Now the SELECT statement will return the JSON with the data from the Employee table treated as the nested object:

[
   {
      "FirstName":"Terri",
      "MiddleName":"Lee",
      "LastName":"Duffy",
      "e":[
         {
            "BirthDate":"1971-08-01",
            "NationalIDNumber":"245797967",
            "LoginID":"adventure-works\\terri0"
         }
      ]
   },
   {
      "FirstName":"Roberto",
      "LastName":"Tamburello",
      "e":[
         {
            "BirthDate":"1974-11-12",
            "NationalIDNumber":"509647174",
            "LoginID":"adventure-works\\roberto0"
         }
      ]
   }
]

As you can see, we have two e arrays, embedded in the outer objects. We can continue to play around with ourSELECT statement to try to get closer to the JSON results we want, or we can instead use the PATH mode, which gives us full control over the format of the JSON output. For all but the most basic SELECT statements, you'll likely want to use the PATH mode.

PATH mode

To use the PATH mode, we start be specifying PATH in the FOR JSON clause, rather than AUTO, as shown in the following example:

SELECT p.FirstName, p.MiddleName, p.LastName,
  e.BirthDate, e.NationalIDNumber, e.LoginID
FROM HumanResources.Employee e INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID in (2, 3)
FOR JSON PATH;

When we switch to the PATH mode, the database engine flattens out our results and returns the data as two object values within a single array:

[
   {
      "FirstName":"Terri",
      "MiddleName":"Lee",
      "LastName":"Duffy",
      "BirthDate":"1971-08-01",
      "NationalIDNumber":"245797967",
      "LoginID":"adventure-works\\terri0"
   },
   {
      "FirstName":"Roberto",
      "LastName":"Tamburello",
      "BirthDate":"1974-11-12",
      "NationalIDNumber":"509647174",
      "LoginID":"adventure-works\\roberto0"
   }
]

Using the PATH mode in this way is fairly straightforward; however, this is PATH at its most basic. The mode lets us be far more specific. For example, we can control how the the database engine nests the JSON output by specifying column aliases that define the structure, as shown in the following SELECT clause:

SELECT
  p.FirstName AS [Name.First],
  p.MiddleName AS [Name.Middle],
  p.LastName AS [Name.Last],
  e.BirthDate AS [PII.DOB], 
  e.NationalIDNumber AS [PII.NatID], 
  e.LoginID
FROM HumanResources.Employee e INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID in (2, 3)
FOR JSON PATH;

In this case, we are defining the Name object, which contains the First, Middle, and Last values; the PII object, which contains the DOB and NatID values; and the LoginID name/value pair, as shown in the following results:

[
   {
      "Name":{
         "First":"Terri",
         "Middle":"Lee",
         "Last":"Duffy"
      },
      "PII":{
         "DOB":"1971-08-01",
         "NatID":"245797967"
      },
      "LoginID":"adventure-works\\terri0"
   },
   {
      "Name":{
         "First":"Roberto",
         "Last":"Tamburello"
      },
      "PII":{
         "DOB":"1974-11-12",
         "NatID":"509647174"
      },
      "LoginID":"adventure-works\\roberto0"
   }
]

In some cases, you will want to add a single, top-level element to your JSON output to serve as a root. To do so, you must specify it as part of the FOR JSON clause, as shown in the following example:

SELECT
  p.FirstName AS [Name.First],
  p.MiddleName AS [Name.Middle],
  p.LastName AS [Name.Last],
  e.BirthDate AS [PII.DOB], 
  e.NationalIDNumber AS [PII.NatID], 
  e.LoginID
FROM HumanResources.Employee e INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID in (2, 3)
FOR JSON PATH, ROOT('Employees');

To specify the root, we add the ROOT option to the FOR JSON clause and, in this case, name the root Employees, which gives us the following results:

{
   "Employees":[
      {
         "Name":{
            "First":"Terri",
            "Middle":"Lee",
            "Last":"Duffy"
         },
         "PII":{
            "DOB":"1971-08-01",
            "NatID":"245797967"
         },
         "LoginID":"adventure-works\\terri0"
      },
      {
         "Name":{
            "First":"Roberto",
            "Last":"Tamburello"
         },
         "PII":{
            "DOB":"1974-11-12",
            "NatID":"509647174"
         },
         "LoginID":"adventure-works\\roberto0"
      }
   ]
}

If you compare these results to those from the previous example, you will see that the outer element has been changed from an array to an object that contains only the Employees property. The Employees value is now the array that was the outer element in the previous example.

You might have also noticed that the second employee, Roberto, includes no middle name. That is because theMiddleName column in the source table is null. By default, the database engine does not include a JSON element whose value is null. However, you can override this behavior by adding the INCLUDE_NULL_VALUES option to theFOR JSON clause, as shown in the following SELECT statement:

SELECT

SELECT
  p.FirstName AS [Name.First],
  p.MiddleName AS [Name.Middle],
  p.LastName AS [Name.Last],
  e.BirthDate AS [PII.DOB], 
  e.NationalIDNumber AS [PII.NatID], 
  e.LoginID
FROM HumanResources.Employee e INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID in (2, 3)
FOR JSON PATH, ROOT('Employees'), INCLUDE_NULL_VALUES;

Now the results will show that Roberto's middle name is null by assigning the null value to the Middle property:

{
   "Employees":[
      {
         "Name":{
            "First":"Terri",
            "Middle":"Lee",
            "Last":"Duffy"
         },
         "PII":{
            "DOB":"1971-08-01",
            "NatID":"245797967"
         },
         "LoginID":"adventure-works\\terri0"
      },
      {
         "Name":{
            "First":"Roberto",
            "Middle":null,
            "Last":"Tamburello"
         },
         "PII":{
            "DOB":"1974-11-12",
            "NatID":"509647174"
         },
         "LoginID":"adventure-works\\roberto0"
      }
   ]
}

There are, of course, other considerations to take into account when using this clause, so be sure to refer to SQL Server 2016 documentation. In the meantime, let's look at how to convert a JSON snippet to traditional rowset data.

Converting JSON to rowset data using the OPENJSON function

To return a JSON snippet as rowset data, we use the OPENJSON rowset function to convert the data to a relational format. The function returns three values:

• key: Property name within the object or index of the element within the array.
• value: Property value within the object or value of the array element specified by the index.
• type: Value's data type, represented numerically, as described in the following table:

Numeric value	Data type
0	null
1	string
2	int
3	true or false
4	array
5	object

To test how the the OPENJSON function works, let's assign a JSON snippet to a variable and then use the function to call the variable, as shown in the following example:

DECLARE @json NVARCHAR(MAX) = N'
{
  "FirstName":null,
  "LastName":"Duffy",
  "NatID":245797967,
  "Current":false,
  "Skills":["Dev","QA","PM"],
  "Region":{"Country":"Canada","Territory":"North America"}
}';

SELECT * FROM OPENJSON(@json);

The JSON snippet contains a single object that includes a property for each data type. The SELECT statement uses the OPENJSON rowset function within the FROM clause to retrieve the JSON data as a rowset, as shown in the following results:

key	value	type
FirstName	NULL	0
LastName	Duffy	1
NatID	245797967	2
Current	false	3
Skills	["Dev","QA","PM"]	4
Region	{"Country":"Canada","Territory":"North America"}	5

Notice that the type column in the results identifies the data type for each value. As expected, the column shows the Skills value an array, with all of the array's elements included in the results for that row. The same goes for the Region value, which is an object. The row includes all the properties within that object.

In some cases, you will want to return only the key and value columns, so you will need to specify those columns in your SELECT list:

SELECT [key], value
FROM OPENJSON(@json);

Notice that you must delimit the key column because Microsoft chose to return a column name that is also a T-SQL reserved keyword. As the following table shows, the results include only those two columns:

key	value
FirstName	NULL
LastName	Duffy
NatID	245797967
Current	false
Skills	["Dev","QA","PM"]
Region	{"Country":"Canada","Territory":"North America"}

Now let's move on to a more complex JSON snippet, which we'll use for the remaining examples in this article:

{
   "Employees":[
      {
         "Name":{
            "First":"Terri",
            "Middle":"Lee",
            "Last":"Duffy"
         },
         "PII":{
            "DOB":"1971-08-01",
            "NatID":"245797967"
         },
         "LoginID":"adventure-works\\terri0"
      },
      {
         "Name":{
            "First":"Roberto",
            "Middle":null,
            "Last":"Tamburello"
         },
         "PII":{
            "DOB":"1974-11-12",
            "NatID":"509647174"
         },
         "LoginID":"adventure-works\\roberto0"
      }
   ]
}

The JSON shown here comes from the output generated from the last example in the preceding section. As you'll recall, the database engine actually outputs the JSON in a format much less readable than what is shown here, but it can be easier to work with when assigning the JSON to a variable. So that's the approach we'll take for the remaining examples:

DECLARE @json NVARCHAR(MAX) = N'{"Employees":[{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"},{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}]}';

If you plan to try out the next batch of examples, you can use this variable definition for each one, which avoids all the whitespace you get when you run the results through a parser. Now let's use the OPENJSON function to convert the JSON in the variable:

SELECT [key], value
FROM OPENJSON(@json);

The example uses OPENJSON at its most basic, with no other parameters defined. As a result, the SELECTstatement returns only a single row for the Employees array, as shown in the following table:

key	value
Employees	[{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"},{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}]

To better control our results, we need to pass a second argument into the OPENJSON function. The argument is a JSON path that instructs the database engine on how to parse the data. For example, the following path instructs the database engine to return data based on the Employees property:

SELECT [key], value
FROM OPENJSON(@json, '$.Employees');

When you specify a JSON path, you start with a dollar sign ($) to represent the item as it exists in its current context. You then specify one or more elements as they appear hierarchically in the JSON snippet, using periods to separate the elements. In this case, the path specifies only the root element, Employees, giving us the results shown in the following table:

key	value
0	{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"}
1	{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}

This time, we get a row for each element in the Employees array. If we want to break the results down even further, we must work down the hierarchy. For example, to reference an element within the Employees array, we must specify the element's index, as it exists within the array. An array's index is zero-based, which means the index count starts with 0, so if we want to retrieve the first element in the Employees array, we must specify 0 after the root name, within square brackets, as shown in the following statement:

SELECT [key], value
FROM OPENJSON(@json, '$.Employees[0]');

The first element in the Employees array is a JSON object that contains three properties, so that is what theSELECT statement returns, as shown in the following results:

key	value
Name	{"First":"Terri","Middle":"Lee","Last":"Duffy"}
PII	{"DOB":"1971-08-01","NatID":"245797967"}
LoginID	adventure-works\terri0

Because the first two values are objects, the entire contents of those objects are returned. However, we can instead return only one of those objects by specify the object name:

SELECT [key], value
FROM OPENJSON(@json, '$.Employees[0].Name');

Now the SELECT statement returns only the three properties within the Name object:

key	value
First	Terri
Middle	Lee
Last	Duffy

The OPENJSON examples we've looked at so far have used the default schema when returning the data as a rowset, but there are limits to how well we can control the results. Fortunately, the OPENJSON function also lets us add a WITH clause to our SELECT statement in order to define an explicit schema. In the following example, the schema flattens out our data so we can easily see the details for each employee:

SELECT *
FROM OPENJSON(@json, '$.Employees')
WITH([Name.First] NVARCHAR(25), [Name.Middle] NVARCHAR(25), 
  [Name.Last] NVARCHAR(25), [PII.DOB] DATE, [PII.NatID] INT);

The WITH clause specifies each column, using names that link to the original JSON. For example, the Name.Firstcolumn returns the employee's first name. The column name is based on the First property within the Nameobject. For each column, we also provide a T-SQL data type. The SELECT statement now returns the results shown in the following table:

Name.First	Name.Middle	Name.Last	PII.DOB	PII.NatID
Terri	Lee	Duffy	1971-08-01	245797967
Roberto	NULL	Tamburello	1974-11-12	509647174

If we want to define more readable column names, we can instead create column definitions that each includes the new name, followed the data type, and then a path reference, as shown in the following example:

SELECT *
FROM OPENJSON(@json, '$.Employees')
WITH(FirstName NVARCHAR(25) '$.Name.First', 
  MiddleName NVARCHAR(25) '$.Name.Middle', 
  LastName NVARCHAR(25) '$.Name.Last', 
  BirthDate DATE '$.PII.DOB', 
  NationalID INT '$.PII.NatID');

Notice that, for the path, we do not need to reference the Employees array itself. That's taken care of in theOPENJSON function. But we still need to specify the dollar sign to show the current context. We then follow with theName or PII object name and then the property name. The SELECT statement now returns the results shown in the following table:

FirstName	MiddleName	LastName	BirthDate	NationalID
Terri	Lee	Duffy	1971-08-01	245797967
Roberto	NULL	Tamburello	1974-11-12	509647174

The preceding examples should give you at least a basic idea of how to turn a JSON snippet into rowset data. Again, refer to SQL Server 2016 documentation to get more specifics about how to use the OPENJSON function.

More JSON functions in SQL Server 2016

In addition to OPENJSON, SQL Server 2016 includes several other functions for working with JSON data. We'll review how to use the ISJSON, JSON_value functions, and JSON_ QUERY functions.

ISJSON

The ISJSON function lets you test whether a text string is correctly formatted JSON. This is a particularly important function, considering that SQL Server 2016 doesn't support a JSON data type. At least this way, you have some way to validate your data.

The ISJSON function returns 1 if a string is valid JSON, otherwise returns 0. The only exception to this is if the string is null, in which case the function returns null. The following SELECT statement tests our ubiquitous @json variable to verify whether it is valid:

SELECT CASE 
  WHEN ISJSON(@json) > 0 
    THEN 'The variable value is JSON.' 
    ELSE 'The variable value is not JSON.' 
  END;

As we hoped, the SELECT statement returns the following results:

The variable value is JSON.

Now let's pass in text that is not valid JSON by tagging on the Age element without a value:

DECLARE @json2 NVARCHAR(MAX) = N'
{"First":"Terri","Middle":"Lee","Last":"Duffy","Age"}';

SELECT CASE 
  WHEN ISJSON(@json2) > 0 
    THEN 'The variable value is JSON.' 
    ELSE 'The variable value is not JSON.' 
  END;

As expected, we receive the second message:

The variable value is not JSON.

JSON_VALUE

Another handy JSON-related function in SQL Server 2016 is JSON_VALUE, which lets us extract a scalar value from a JSON snippet, as shown in the following example:

SELECT JSON_VALUE(@json, '$.Employees[0].Name.First');

The JSON_VALUE function takes two arguments. The first is the JSON itself, and the second is a path that defines which element's value we want to retrieve. In this case, the path specifies the First property in the Name object, which is part of the first element in the Employees array. As we would expect, the SELECT statement returns the value Terri.

We can just as easily return the NatID value for the second employee:

SELECT JSON_VALUE(@json, '$.Employees[1].PII.NatID');

Now the SELECT statement returns 509647174. Suppose, however, that we try to retrieve something other than a scalar value. For example, the following path specifies only the PII object for the second employee:

SELECT JSON_VALUE(@json, '$.Employees[1].PII');

This time, the SELECT statement returns a null value. By default, the database engine returns a null value if the path does not exist or is not applicable to the current situation. In this example, we've specified an element that cannot return a scalar value, so the database engine returns the null value.

When specifying a path in a JSON-related expression, you can control the results by preceding the path with the laxor strict option. The lax option is the default and is implied if not specified, which means that the database engine returns a null value if a problem arises. For example, the following path explicitly includes the lax option:

SELECT JSON_VALUE(@json, 'lax $.Employees[1].PII');

Once again, out statement returns a null value because we're specifying an element that cannot return a scalar value. We can instead specify the strict option, in which case, the database engine will raise an error if a problem occurs:

SELECT JSON_VALUE(@json, 'strict $.Employees[1].PII');

This time we receive very different results:

Property cannot be found in specified path.

JSON_QUERY

Another useful JSON-related tool is the JSON_QUERY function, which can extract an object or array from a JSON snippet. For example, the following SELECT statement retrieves the PII object for the second employee:

SELECT JSON_QUERY(@json, 'strict $.Employees[1].PII');

Like the JSON_value function, the JSON_QUERY function takes two arguments: the JSON source and a path indicating what data to extract. The SELECT statement returns the following results:

{"DOB":"1974-11-12","NatID":"509647174"}

If we want to return the Employees array, we simply specify $.Employees as our path:

SELECT JSON_QUERY(@json, 'strict $.Employees');

Now the SELECT statement returns just about everything in our JSON snippet:

[{"Name":{"First":"Terri","Middle":"Lee","Last":"Duffy"},"PII":{"DOB":"1971-08-01","NatID":"245797967"},"LoginID":"adventure-works\\terri0"},{"Name":{"First":"Roberto","Middle":null,"Last":"Tamburello"},"PII":{"DOB":"1974-11-12","NatID":"509647174"},"LoginID":"adventure-works\\roberto0"}]

Summary: JSON and SQL Server 2016

This article should give you what you need to start working with JSON data in SQL Server. As you can see, however, JSON support is nowhere nearly as robust as XML support. And if you're working with other database management systems, you'll quickly discover that the JSON features in SQL Server 2016 have some catching up to do before they can match what's been implemented in other products.

Even so, what SQL Server 2016 provides is better than nothing, and the JSON support is solid and could prove more than adequate much of the time. In fact, for some organizations, the JSON features already implemented in SQL Server 2016 will be enough to meet their needs. Best of all, the JSON-related functionality is straightforward and easy-to-use, so you should be able to incorporate it into your workflow with relatively little pain.