One common question is are there some performance degradation if we format JSON in database layer compared to standard approach (returning all data in application layer and formatting results as JSON there). In this post I will show one experiment with FOR JSON compared to regular queries.
In this post we will see how to index JSON text using full text search. Full text search indexes can be applied on any text data, so we can use it to index elements in JSON arrays. In this example I will create index on array of sales reasons.
I this post will be shown how to generate sequence of numbers using OPENJSON table value function.
DROP IF EXISTS is the new thing in SQL Server 2016 that can simplify your scripts.
In this post I will talk about different way to select rows from the table by list of ids. I will compare performance of some common approaches (IN, LIKE, and OPENJSON). I will show that OPENJSON is good approach for selecting rows by id.
In this post I will talk about one annoying thing – how to return a result set containing one to many relationships between tables? In that case you will have multiple primary rows because one row is generated per for each child row. in this post we will see how you can resolve this problem using JSON.
Currently you can find many JSON documents stored in files. Sensors generate information that are stored in files, applications log information in JSON files, etc. One important thing that you would need to do is to read JSON stored in files, load them in SQL Server, and analyze them. In this post we will see how you can import JSON files in SQL Server.
Sql Server 2016 and DocumentDb enable you to query JSON documents. DocumentDb has nice syntax for querying JSON documents – you can find some good examples on DocumentDb site . Sql Server provides built-in functions for accessing JSON fields (JSON_VALUE), fragments (JSON_QUERY) and opening JSON documents (OPENJSON). In this post, I will show you some queries that can be executed in DocumentDb and equivalent Sql Server queries.
OPENJSON function in Sql Server enables you to open complex JSON structures. In this post we will see how you can open GeoJSON format.
In Sql Server can be used standard indexes to speed-up queries that use values in JSON documents. In this post we will see how to use standard non-clustered index to improve performance of you queries on JSON data.
OPENJSON function that will be added in SQL Server 2016 is the easiest way to import JSON text into regular table. In this post we will see how you can transform JSON to table and then insert/update existing table in SQL Server.
JSON can be used to improve performance and reduce complexity in data load process if you serialize some entities as JSON collections. In this post we will see how you can use JSON columns in data load process.
SQL Server do not supports complex types such as arrays or lists. If you need to send some parametrized query or execute stored procedure, you will need to use primitive scalar types. Currently, it is hard to send some structured information to SQL server modules. New OPENJSON function can be used to parse array elements. If you need to send an array of element to SQL server it is better that dynamic SQL, and also simpler than table value functions.In this blog post you can see how to use this function. In this post you will see how you can use JSON arrays to send complex data to SQL Server.
SQL Server 2016 will include JSON support. First functionality (FOR JSON clause) is available in CTP2. Here you can see an overview of FOR JSON clause that is available in SQL Server 2016 CTP2.