Press "Enter" to skip to content

Curated SQL Posts

Elastic Data Maps with Azure Purview

Published 2021-08-19 by Kevin Feasel

Wolfgang Strasser has some good news for us:

It’s been a long and intensive discussion – the (initial) pricing structure of Azure Purview. As I already talked about it in my Purview pricing blog post, the basic cost calculation involves

– the cost for the data map (= the infrastructure to store metadata and provide the Purview UI + cataloging functionality)

– plus the costs involved for scanning sources.

And that has added up to a significant amount of money, especially in dev/test scenarios. But read on for the glad tidings Wolfgang has to share.

Comments closed

Ways to Filter Data in PostgreSQL

Published 2021-08-19 by Kevin Feasel

Gauri Mahajan shows off several techniques for filtering data in PostgreSQL:

Data is hosted in a variety of data repositories, one of which is relational databases. Out of tens of commercial and open-source relational databases, one of the most popular open-source relational databases is PostgreSQL. This database is offered on the Azure cloud platform through a service named Azure Database for PostgreSQL. One of the most fundamental operations performed on the database is reading and writing data to consume and host data. It goes without saying that when the data is consumed, it must be scoped based on the requirements or criteria specified by the consumer. This translates to filtering the data while querying it. Like every other relational database, Postgres offers different operators and options to filter data while querying. Let’s go ahead and learn some of the most fundamental ways to filter data hosted in PostgreSQL.

Most of them are the same as what you have in T-SQL, but not everything.

Comments closed

Eliminating False Positives when Searching for NOLOCK

Published 2021-08-19 by Kevin Feasel

Aaron Bertrand carries a lantern around Athens, searching for an honest NOLOCK:

In Part 1 of this series, I showed how to identify “NOLOCK in DML” patterns within a single statement using a Visitor pattern. In Part 2, I showed how to find those patterns in procedure and trigger bodies across multiple databases and instances. I still need to show how to eliminate false positives without tedious visual inspection.

Read on to see how we can narrow down the likelihood of a false positive when searching for the worst sorts of NOLOCK queries.

Comments closed

Power BI Push Tools

Published 2021-08-19 by Kevin Feasel

Marco Russo announces a new set of tools:

Power BI Push Tools is a set of open-source tools to work with Power BI push datasets.

Read Implementing real-time updates in Power BI using push datasets instead of DirectQuery to learn the architecture of solutions based on push datasets and how to create and populate a push dataset starting from a regular Tabular model.

In addition to reading that linked post, click through to see the three tools which are currently available.

Comments closed

Renaming Multiple Columns at Once in Power BI

Published 2021-08-19 by Kevin Feasel

Matt Allington wants to change a bunch of column names at once with Power BI:

This is not the first time I have shared this concept.  In my previous article I showed how it is possible to add a prefix to every column in a table. This article today is slightly different. Today I am removing text from multiple columns all at once using some M code. The trick you need to learn to solve this problem is “how to create a list of lists”.

Click through for a video to see it in action.

Comments closed

The Basics of Event-Driven Architecture

Published 2021-08-18 by Kevin Feasel

The Aiven team has a nice primer on event-driven architecture:

What happens when one link in the chain goes down? Requests that are waiting for a response don’t receive one at all. They continue to wait, or they time out. The entire application is blocked. What’s more, as the number of services increases, the number of synchronous interactions between them increases as well. In such a situation, a single system’s downtime affects the availability of other systems as well.

An alternative approach is building a microservices application on an event-driven architecture (EDA). Event-driven architecture is made up of decoupled components — producers and consumers — which process events asynchronously, often working through an intermediary, called a broker. That might feel like a mouthful. Don’t worry — we’re going to walk through these concepts one step at a time. In this article, we’re going to look at the components that make up event-driven architecture, why you would use this paradigm, and how to implement it.

Read on to see what makes it so interesting.

Comments closed

Testing If sp_crecompile Updates Views

Published 2021-08-18 by Kevin Feasel

Kenneth Fisher puts on a lab coat and safety goggles:

So, now the question comes in. If I run sp_recompile against a table, will that also cause the associated views to be updated?. So quick experement.

Click through for the experiment. And I completely agree with Kenneth’s sentiment at the end—be willing to try things out. One of the nicest things about working in databases is that we have extremely low-friction ways to experiment—a fully-featured IDE allows us to connect directly to the database, we can create and drop tables or databases at will, and we can even use containers for some of the riskier stuff (assuming that your test also works on Linux).

Comments closed

Joining to STRING_SPLIT

Published 2021-08-18 by Kevin Feasel

Kevin Wilkie explains that the STRING_SPLIT() function isn’t something one simply joins to:

My friends! Last time together, we discussed using the STRING_SPLIT function and how it’s used in combination with the CROSS APPLY.

First off, most of us are used to working with an INNER JOIN instead of CROSS APPLY. Well, you’re not going to be able to use an INNER JOIN when you’re using the STRING_SPLIT function.

Read on for a demonstration.

Comments closed