SQL Server 2016 added support for working with JSON. Although there isn’t a JSON datatype, there is still the ability to output query results to JSON, and to break down JSON into rows and columns.
This post will run through some basic examples of working with JSON. I’m using SQL Server 2017 although 2016 can be used as well.

Next is returning relational data as JSON. Much like the FOR XML clause returns XML, the FOR JSON clause returns the selected data in a JSON string. AUTO will return a default structure, where using PATH will allow more control on the output, like naming the root node and returning NULL values instead of omitting them.

There are also a few JSON Functions available.
ISJSON will determine if a string is valid JSON or not.
JSON_VALUE will extract a scalar value.
JSON_QUERY will return a JSON fragment or an array of values.
By default, the JSON functions are in Lax mode, which means that an error won’t be raised with an invalid operation, a NULL values will be returned instead. Strict mode can be specified, in which case an error will be raised with an invalid operation.

The ‘sp_execute_external_script’ stored procedure is used to execute R code. In the @language parameter we specify ‘R’ since we want to run R code. In the @input_data_1 parameter, we specify the data set that we want to pass in, in this case we select all of the records from our 1 column table. The @script parameter is where we place the R code. Any data returned by the code in the @input_data_1 parameter is visible in our R code as ‘InputDataSet’ (We can use a different name by specifying in a different parameter, but we’ll go with the defaults to keep it simple).
Even though our input data set has only one attribute (Measure), it should be specified, InputDataSet$Measure. We use the R function mean to find the mean value of all of the Measure values. So even though it is a scalar value being returned, it needs to be converted to a data frame, which is assigned to the default output variable OutputDataSet.
For the output, we use RESULT SETS to specify the name, data type and nullability of each attribute in the data set. In this case, we get the value 5 as ‘Average’.

Installing R Packages:
One of the powerful things about R is all of the external libraries of functionality that are freely available to use. If we want to explore machine learning or advanced statistical functions, we’ll need to find and install these packages.
RStudio Support has a list of the most popular R packages.
Once we find a package that we’d like to use, we’ll need to download and install it. We’ll install stringr, which has regular expression and other string manipulation functionality.
Running install.packages in the sp_execute_external_script proc didn’t work for me, I got a message that the package wasn’t available.SQL Server Central – Installing R packages in SQL Server R Services gives some other options to install packages.
I ended up using the R command line option to install stringr. Since the package was dependent on two other packages, they were retrieved and installed as well.

A new feature in SQL Server 2016 is the ability to run R scripts within SSMS. For those not familiar with the R language, it is an open source language used to performed statistical computations, to make use of machine learning algorithms, and for data visualization.
In this post, I’ll go through a quick explanation on installing and setting up the services.

Background:
The R language comes with a great deal of built-in functionality, but its real power comes from the hundreds of packages available, created by the R community to extend the functionality of R. R is an interpreted language, using vectors and data frames (similar to tables) as the primary data structures.
R can connect to SQL Server to retrieve data, but there are a few drawbacks. For one, R works with data in memory, so your dataset is limited to the amount of memory available in your workspace. Also, R is single-threaded, so we can’t take advantage of distributing the workload. Working with R within SQL Server will allow us to use a larger dataset as well as take care of

Installation:
The R components for SQL Server are installed from SQL Server setup. If the services weren’t installed with the initial installation of the database engine, you can go back and add the components.
One option is to install a stand alone R Server, that would be used as a separate instance to use in analyzing data using R. There is also an option to install in-database R services. This optional also installs an additional service, the SQL Server Launchpad, that allows integration with R. In my testing, I elected to work with the in-database component with my existing SQL Server instance.

Setup:
Once the components have been installed, the ‘External Scripts Enabled’ setting will need to be set to true.

EXEC sp_configure 'external scripts enabled', 1;
GO
RECONFIGURE;
GO

We’ll also need to restart the database instance to have this setting take effect. We also need to make sure that the SQL Server Launchpad service is running.

Test:
We’ll make a call to the sp_execute_external_script stored procedure to make sure that R is properly set up and that we can make a call.
I found this sample code on MSDN that will allow us to test the R set-up without writing R code or retrieving data from tables right now.

In the Service Pack 1 for SQL Server 2016 (Download here), a new T-SQL enhancement has been added:
CREATE OR ALTER

This command can be used with stored procedures, functions, triggers and views. So it doesn’t matter if an object already exists or not, we can issue the same command for either case. No more checking for the existence of an object or dropping and recreating an object.
For example:

Right-clicking on a table in SSMS 2016 includes an option for the ‘Memory Optimization Advisor’. This starts a wizard to assist in migrating a table to an In-Memory table structure. If the base table has any issues that would prevent it from being migrated to an in-memory structure, the wizard will identify and help to resolve those issues. Here’s a link to some issues that would prevent the migration.
A big issue will be tables with foreign keys defined. The keys will have to be dropped and then re-created, they won’t be migrated.
If all checks pass, then the table can be migrated. There is an option to migrate the data as well.

Just a quick introduction to the Query Store feature. Query Store is a feature new to SQL Server 2016 that will capture data on query performance. Once it is enabled, we’ll have access to dashboards to track query plans as well as statistics on query execution.
I’m using SQL Server 2016 RTM Developer Edition.

Set Up:
Query Store is enabled at the database level. Going to the database properties you’ll see a ‘Query Store’ page. Setting ‘Operation Mode (requested)’ from ‘Off’ to ‘Read Write’ will enable this functionality. You’re also able to set the max space taken up, among other settings.
Once enabled, you can go back to SSMS and you’ll see a ‘Query Store’ tree under the database.

Dashboards:
Under the ‘Query Store’ tree in SSMS, we’ll see four entries. These are for available dashboards to return data on our query performance and query plans.
– Regressed Queries: Returns queries that have become worse performing over time. We can select from certain criteria and view query plans to determine issues.
– Overall Resource Consumption: A summary of query stats for Duration, Execution Count, CPU Time and Logical Read over a set period of time.
– Top Resource Consuming Queries: We can select our criteria, such as duration, CPU Time, Physical Reads, etc. and see the worst performing queries by that criteria.
– Tracked Queries: Takes a query ID as a parameter – Returns query plans, the time the query was executed along with the average duration.