Creating NOSQL like environment in SQL Server 2016

    With Json support and In-Memory tables features, It is pretty easy to create a  NOSQL environment in SQL server 2016. I am going to try to create one in this post.
 
    NOSQL environments first advantage is the speed. The data always stays in the memory and It's very fast to find what you are looking for. We have this option in SQL Server too, we should be able to create an in-memory table and put the data in it right?  Let's start with that. What do we need to create an in-memory table? First our database needs to have Filegroup that can hold in-memory table files. You will get an error when you try to create an in-memory table if your database does not have one.
   
    Right click on your database and go to options. Click on FileGroups page in the left, click on the Add FileGroup button and type the name of your new File Group

    Now we have a file group, we still need to add file to the new file group we created. Click on the Files page in the left. Type the name of the file under the Logical Name column and pick the FILESTREAM Data for it and click OK

    We are ready to create tables in memory now. SQL Server is going to use this new FileGroup for the in-memory tables. Let's create an in-memory table now.

CREATE TABLE Summaries (
  Id int PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 200000),
  Summary nvarchar(max)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

    Our table structure is just like NOSQL databases. We have a key named Id, and a value field named Summary. I am going to store json objects in the Summary column so i made its data type nvarchar(max). Now we need some data, I am going to use WideWorldImporters data since it has Json values in it.

USE WideWorldImporters
INSERT INTO Summaries
SELECT InvoiceId, ReturnedDeliveryData 
FROM Sales.Invoices

    Let's list what we have accomplished so far.

• We have a key,value table in memory staying in a SQL Server machine. 
• Web applications can retrieve JSON object really fast by an Id just like NOSQL
• To create this environment, I didn't have to install a NOSQL Server.
• I didn't need to add any new references to my web application so it can connect to a NOSQL server. 

     Really, I didn't have to do that much.I am going to query this table just like other SQL tables. If you have SQL Server 2016, you should be able to do all of these. But wait, it gets better. SQL Server 2017 has new features that will help me here.

    SQL Server 2017 supports JSON functions. That is great news for me because I have JSON objects in my table and only way to retrieve them right now is by Id column. With SQL Server 2017, I can query my table by Json functions!

     First let's make our table more stable. I want to add a CHECK constraint to my table, I want to be sure that all of the values in the Summary column are valid JSON objects.

ALTER TABLE Summaries
ADD CONSTRAINT [You better be JSON if you want to stay]
CHECK (ISJSON(Summary)=1)

     Now, Summary column accepts only valid JSON objects. Now Let's try to query our table by a JSON function. I want to find all events that handled by a specific driver.

SELECT * FROM Summaries
WHERE JSON_VALUE(Summary, '$.Events[1].DriverID') = 2

    You need SQL Server 2017 to use the JSON functions. I hope Microsoft will let us create Clustered Columnstore in the in-memory tables in future, that way this table will be compressed up to 10 times.