A Fine Slice Of SQL Server
Jack Vamvas shows how you can import data in JSON format and get tabular data in SQL Server:
It is possible to read a json file using T-SQL.There are a number of different methods. By using the OPENROWSET functionality , ISJSON and OPENJSON function you can quickly read the file , check if the JSON is valid and then unpack the JSON into a SQL table.
Read on for an example. This also performs reasonably well in practice, at least in my experience.
Comments closedBill Fellows takes us through a library which (seemingly by law) must be in every .NET project:
#sqlhelp #biml I would have the metadata in a Json structure. How would you parse the json in the C# BIML Script? I was thinking use Newtonsoft.Json but I don’t know how to add the reference to it
Adding external assemblies is a snap but here I’ll show how to use the NewtonSoft Json library to parse a Json based metadata structure and then use that in our Biml.
Click through to learn how.
Comments closedJeffery Hicks shows how you can modify your Windows Terminal profile using Powershell:
I recently updated my Windows 10 systems to the 1903 release. One of the reasons is that I wanted to try out the new Windows Terminal preview. You can find it in the Windows Store. This is bleeding edge stuff and far from complete but promises to a great addition. Now you will be able to have all your command terminals, in one tabbed application and easily be able to switch between them. As I said, this is far from being a finished and polished product. Right now, if you want to add a new profile, that is another terminal, you have to manually edit a json file. If you have VS Code installed, the file will open in that. Otherwise, I’m assuming you’ll get whatever application is associated with the .json extension.
Read on for a Powershell one-liner which lets you create a terminal profile.
Comments closedDave Mason continues a journey into parsing JSON with T-SQL:
Starting with SQL Server 2016, Microsoft provided a STRING_SPLIT function. It is a table-valued function that splits a string into rows of substrings, based on a specified separator character. It’s been a welcome addition that we waited a long time for. It has one shortcoming, though: the order of the output rows is not guaranteed to match the order of the substrings in the input string.
Microsoft also provided support for parsing JSON data starting with SQL Server 2016. I discovered the OPENJSON function can be used to split strings, and it can also return the ordinal position of each substring from the original input string.
There are some limitations which you’d expect, namely around requirements for valid JSON.
Comments closedDave Mason has a primer on JSON parsing using T-SQL:
Microsoft added support for JSON data beginning with SQL Server 2016. JSON is an open-standard file format consisting of attribute–value pairs and array data types. It is commonly used to transmit data objects for asynchronous browser–server communication. But it is also used for storing unstructured data in files or NoSQL databases such as Microsoft Azure Cosmos DB. For most of us, SQL Server’s support for JSON probably means two things: we can convert relational data to JSON and vice versa. In this post, I’ll focus on converting JSON to relational data and share what I’ve learned from a recent experience.
I’ve been pleasantly surprised with the way JSON support works in SQL Server. It’s supported every complicated scenario I’ve had to deal with so far, including nesting, deciding with or without arrays for the outer element, quotes or no quotes around numbers, etc.
Comments closedPhil Factor takes a look at using JSON to perform memoization:
For the SQL Server developer, matrices are probably most valuable for solving more complex string-searching problems, using Dynamic Programming. Once you get into the mindset of this sort of technique, a number of seemingly-intractable problems become easier. Here are fifty common data structure problems that can be solved using Dynamic programming. Until SQL Server 2017, these were hard to do in SQL because of the lack of support for this style of programming. Memoization, one of the principles behind the technique is easy to do in SQL but it is very tricky to convert existing procedural algorithms to use table variables. It is usually easier and quicker to use strings as pseudo-variables as I did with Edit Distance and the Levenshtein algorithm, the longest common subsequence, and the Longest Common Substring. The problem with doing this is that the code to fetch the array values can be very difficult to decypher or debug. JSON can do it very easily with path array references.
The results aren’t fantastic but the code is easier at least.
Comments closedBert Wagner gives us a way that you can quickly search through complicated JSON:
Computed column indexes make querying JSON data fast and efficient, especially when the schema of the JSON data is the same throughout a table.
It’s also possible to break out a well-known complex JSON structure into multiple SQL Server tables.
However, what happens if you have different JSON structures being stored in each row of your database and you want to write efficient search queries against all of the rows of your complex JSON strings?
Bert’s solution is an example of a phenomenon I’ve noticed in relational databases: sometimes, the best solution is not the most straightforward. The most straightforward solution is to take the JSON as-is, but that hits a wall as Bert shows. Reshaping the data leads to much better performance…as long as you’re able to afford the time needed to reshape and don’t have JSON changing that frequently.
Comments closedSometime you would need to know how many Managed Instance you have created in Azure cloud. Although you can find all information about the Azure SQL Managed Instances in Azure portal or API (ARM, PowerShell, Azure CLI), sometime it is hard to list all instances and search them using some criteria. In this post you will see how easily you can load list of your Managed Instances and build inventory of your resources.
Problem
Imagine that you have a large number of Managed Instances and you need to know how many instances you have, in what regions, subnets, and virtual networks they are placed, how much compute and storage is allocated to each of them, etc. Analyzing inventory of Managed Instances might be hard if you just use PowerShell.
Click through for the solution.
Comments closedEduardo Pivaral shows off the FOR JSON functionality in SQL Server 2016 and later:
For most of real-world applications, the JSON AUTO will not give you the control you could need over your file format, for having more control over it, you must use the JSON PATH option, along with the ROOT option as follows:
SELECT TOP 10
id,
dataVarchar,
dataNumeric,
dataInt,
dataDate
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')
Eduardo has several examples along these lines.
Comments closedGreg Low walks us through a mechanism to check whether data has changed:
In a previous post, I wrote about how to determine if a set of incoming values for a row are different to all the existing values in the row, using T-SQL in SQL Server.
I later remembered that I’d seen a message by Adam Machanic a while back, talking about how FOR JSON PATH might be useful for this, so I did a little more playing around with it.
If you are using SQL Server 2016 or later, I suspect this is a really good option.
Click through to see an example using the WideWorldImporters database.
Comments closed