A Fine Slice Of SQL Server
Kevin Wilkie only believes in two dimensions:
The function FLATTEN takes our JSON object and gives us the ability to put the data into an actual table. If we run the original query above, then we’ll get the data shown below:
Read on for an example using JSON, though as Kevin mentions, FLATTEN() will also work with arrays and XML data.
Hiram Fleitas updates the data:
I recently ran into a Kustomer that migrated from TSI to ADX (Azure Data Explorer). They were really excited about using Kusto Trender but one item they couldn’t wrap their head around was how to update their hierarchy table(s) in ADX. i.e.
- Contoso WindFarm Hierarchy (Levels: Plant > Unit > System > Name) -- Plant --- Unit ---- System ----- NameAs a big data platform ADX is an append-only data store, so we don’t have the options to do updates, right? Well, that’s not completely true. We absolutely don’t support updates, but we do have a couple options to simulate updates.
Read on to see what options are available to you.
Comments closedDeborah Melkin looks at a nice feature in SQL Server 2022:
A couple of months ago, I wrote about my initial thoughts to SQL Server 2022. I think a lot of what I wrote still holds true. It feels like it’s building on the functionality from previous versions, especially when it comes to Intelligent Query Processing and its feedback capabilities.
The feature I’m most curious to find out more about is the Degree of Parallelism Feedback. (You can read more about it here.) One of the main reasons for this is I know very little about the settings.
Read on for Deb’s thoughts and a situation where it would have helped.
Comments closedBy sheer coincidence, I had the privilege of being invited to a private SQL Server 2022 workshop taught by Bob Ward last week. And through my job, I also had the privilege of doing some testing work around QAT backups and S3 Data Virtualization during the private preview phase last summer. So while I had exposure and access to SQL Server 2022 for much longer than others, there were many things that Microsoft loaded into the 2022 release that I barely skimmed over or knew were even there.
Towards the end of the workshop, Bob presented a slide called Purvi’s List. Purvi Shah is an engineer on the SQL performance team and as Bob said, “spends her time finding ways to make SQL Server and Azure SQL faster.”
Read on to learn what had Andy so excited.
Comments closedThe
take_anyfunction is a random row generator. Based on the parameters passed it, it will select a random row from the dataset being piped into it. It also has a variant,take_anyif, we’ll see both in this post.Note that
take_anywas originally calledanyand was renamed. Whileanystill works, it has been deprecated and you should now usetake_any.
As always, Robert shares plenty of examples of how the operator works, so check it out.
Comments closedGilbert Quevauvilliers has us synchronize our watches:
In this blog post I am going to detail how to create a schedule, test and confirm that the backup for the Power BI Premium dataset is successfully backed up.
This follows on from my first blog post: Automating the backup of Power BI Premium datasets/databases – Part 1
Read on for a walkthrough of what you need to do in order to create a schedule runbook.
Comments closedMagda Bronowska rounds to the nearest minute:
From MS Learn:
DATETRUNC() function returns an input date truncated to a specified datepart.
On the surface the work similarly to
DATEPART(), however that function returns integer values, opposed to the dates returned byDATETRUNC()(we will see that better in the example below).
Read on for plenty of examples of this, as well as two more syntax updates in SQL Server 2022.
Comments closedPranab Paul divvies up the costs:
While working on various customer and partner facing roles, I felt the necessity of a simple and flexible solution to align Azure Cost to the customer’s organizational structure. “Project Bose” is a fully operational prototype derived from the same thought process. This is a side project I am working on during my leisure time. I found various customers derived similar solutions in-house, and there are ISV solutions as well. But there are a few fundamental differences between “Project Bose” and all the other solutions I found. “Project Bose” has a flexible backend and hence any changes in organizational structure can easily be implemented on it without disruption. It is also independent of using Resource Tags, which gives it the opportunity to remain non-vulnerable to erroneous values injected intentionally or non-intentionally by IT-Ops.
Pranab didn’t include a link to the GitHub repo, so here it is.
Comments closedThis month, Deepthi Goguri (@dbanuggets) asks us about our favourite new feature in SQL Server 2022 or Azure. And while there are always a few, I’m going to write about why I have a particular fondness of “IS [NOT] DISTINCT FROM“, despite the fact that it’s overly wordy and the functionality isn’t actually new at all.
People understand my point that it’s a little wordy. Typing “IS NOT DISTINCT FROM” instead of “=” doesn’t sound fun to anyone, and I think “==” or “IS” ought to be fine. The fact that the functionality isn’t new… well that statement seems to raise a few eyebrows.
Read on for Rob’s take on what IS DISTINCT FROM (and its negative cousin) actually do and what performance-killing alternative people used prior to that.
Kevin Chant looks at Azure Synapse Link for SQL Server 2022:
My first topic is about a new feature that covers both SQL Server 2022 and Azure. Which is Azure Synapse Link, or to be more precise Azure Synapse Link for SQL Server 2022.
I have been doing various tests with this feature recently. Which has led to some interesting blog posts about Azure Synapse Link for SQL Server 2022.
Read on for a few more thoughts, as well as deployment scripts via Azure DevOps and GitHub Actions.
Comments closed