A Fine Slice Of SQL Server
The
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 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.
Pranab 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 closedDeborah Melkin handles missing servers with aplomb:
OPENROWSET is a functionality that allows you to access data sources outside your current server. This could be reading from an Excel file or calling another SQL Server instance. You’re able to treat that other data source as record set, or derived table, and work with the rows returned as you would a local table. One reason you may want to do this is that you need to use a stored procedures to query data from other servers and bring the data together, effectively creating an ELT (Extract – Load – Transform) process without having to use SSIS or Azure Data Factory (ADF).
Read on to see how OPENROWSET() works, what happens if you try to access a remote server which doesn’t exist (or times out), and how you can capture that error message in a CATCH block—something that is not possible to do by default.
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 closedShalu Ganotra Chadha, et al, explain how to keep your Delta Lake tidy:
The useful features of Delta Lake come at the cost of requiring regular maintenance. Delta Lake requires periodic cleanup as it accumulates files over time with each upsert and retains previous snapshots of the data. They can quickly convert a small dataset (in MBs) to several GBs of storage. This is because deleted data is not really removed but retained as an older snapshot of the Delta Lake dataset.
Click through for two operations you can perform on a Delta Lake, as well as some recommendations on when to do what via the Genie Delta Lake Auto Maintenance scripts they provide.
Comments closedKoen Verbeeck lays out a recommendation:
In this blog post I’ll talk about another of those rules/mantras/patterns/maxims:
build once, add metadata
I’m not sure if I’m using the right words, I heard something similar in a session by Spark enthusiast Simon Whiteley. He said you should only write code once, but make it flexible and parameterized, so you can add functionality just by adding metadata somewhere. A good example of this pattern can be found in Azure Data Factory; by using parameterized datasets, you can build one flexible pipeline that can copy for example any flat file, doesn’t matter which columns it has. I have blogged about this:
Click through to learn more about the concept, as well as some tips on how you’d do that in various data movement products (e.g., SSIS, ADF, Logic Apps).
Comments closedReza Rad brings order to things:
The question I often get in my Power BI architecture consulting sessions is; “How should we organize our workspaces? should we have one workspace with all the reports in it? Or multiple? Should we split it based on each report? Business unit? or something else?” In this article and video, I’ll explain a guide on how to organize and set up workspaces in your organization.
Click through for a video and a helpful article.
Comments closed