Press "Enter" to skip to content

Curated SQL Posts

Executing Azure Data Factory Pipelines with Power App

Published 2021-06-30 by Kevin Feasel

Rayis Imayev has a plan:

One of my university professors liked to tell us a quote, “The Sleep of Reason Produces Monsters”, in a way to help us, his students, to stay active in our thinking process. I’m not sure if Francisco Goya, had a similar aspiration when he was creating his artwork with the same name.

So, let me explain my reasons to create a solution to trigger Azure Data Factory (ADF) pipelines from a Power App and why it shouldn’t be considered as a monster 🙂

If that’s not an introduction enticing enough to get you to read the whole thing, I don’t know what is.

Comments closed

Data Type Conversions and Query Folding

Published 2021-06-30 by Kevin Feasel

Chris Webb explains how data type conversions and query folding might not mix:

It’s surprisingly easy to stop query folding happening in Power Query by changing the data type of a column. This is mentioned in the docs here, and it’s something several people have blogged about already (for example here). However there is something new to note: an option that will allow you to convert text columns to number or date columns in a foldable way for SQL Server data sources.

Consider the following table in a SQL Server database that consists of a single nvarchar(50) column containing numeric values:

Click through for the example, and also check out the comments below for more info.

Comments closed

Comparing Property-Based and Partition-Based Testing

Published 2021-06-29 by Kevin Feasel

Mark Seemann compares and contrasts two types of testing which typically get conflated:

To be fair, the overlap may easily be larger than the figure implies, but you can certainly describes properties without having to partition a function’s domain.

In fact, the canonical example of property-based testing (that reversing a list twice yields the original list: reverse (reverse xs) == xs) does not rely on partitioning. It works for all finite lists.

You may think that this is only because the case is so simple, but that’s not the case. You can also avoid partitioning on the slightly more complex problem presented by the Diamond kata. In fact, the domain for that problem is so small that you don’t need a property-based framework.

This is an interesting look at two related but separate branches of testing.

Comments closed

Finding Eager Index Spools

Published 2021-06-29 by Kevin Feasel

Erik Darling hunts the most dangerous prey of all:

I’ve written a bunch about Eager Index Spools, and how to fix them, but I’ve always sort of left the “finding” part up to you, or pointed curious tuners to tools like sp_BlitzCache.

Recently though, I worked with a client who had Eager Index Spools so frequently that we needed to track them down specifically.

This is the plan cache query that I used to do it — they didn’t have Query Store enabled — and I wanted to share it.

Click through for the query.

Comments closed

Using the Cosmos DB Integrated Cache

Published 2021-06-29 by Kevin Feasel

Hasan Savran makes use of a cache:

We are ready to write some code now. Integrated Cache works only in Eventual Consistency for now. So, we need to send requests in Eventual consistency to test the Integrated Cache. To do that, we need to use requestOptions parameter in SDK. You can change your database consistency level to Eventual too for testing if you like. Don’t forget to change it back later!

Hopefully that limitation changes later, but in the meantime, click through to see how to use the integrated cache in Cosmos DB.

Comments closed

Creating Comparisons in Power BI

Published 2021-06-29 by Kevin Feasel

Matt Allington has a video:

The whole concept here is to allow a user to pick two things from a list and compare them with each other. For example, compare

– Any year with any other year
– Any product against any other product (maybe even a group of products, using multi select)
– Snapshots (eg different versions of a financial forecast) with any other version
– Etc

What is possible is endless, and the good news is that it is pretty simple to do this in Power BI. Check out my video below to see how I did it.

Click through for the video.

Comments closed

Levels of Recursion in CTEs

Published 2021-06-29 by Kevin Feasel

Steve Stedman probes the limits:

When presenting unleashing Common Table Expressions at SQL Saturday a while back, I was asked a couple of great questions that I didn’t know the answer to. So I did the research and tracked it down:

1. How many levels of recursion can you have in a CTE?

2. How many levels of nesting can you have in a CTE?

So I started doing the research and doing some testing to figure it out.

How many levels of recursion can you have in a CTE?

Click through for the two answers.

Comments closed

Using ggplot2 to Create a Faceted Histogram plus Curve

Published 2021-06-28 by Kevin Feasel

Sebastian Sauer builds a combo chart:

Overlaying a histogram (possibly facetted) is not something far fetched when analyzing data. Surprisingly, it appears (to the best of my knowledge) that there’s no comfortable out-of-the-box solution in ggplot2, although it can be of course achieved with some lines of code. Here’s my take.

Click through for Sebastian’s version, as well as information on the ggh4x library.

Comments closed

A GETDATE() Workaround when Rewriting Scalar UDFs

Published 2021-06-28 by Kevin Feasel

Erik Darling finds a workaround for one scalar UDF inlining limitation:

SQL Server 2019 has been a bit of a roller coaster ride. In particular, UDF inlining started as I think the most interesting addition to the product. Big brain stuff, for sure.

It has been nerfed quite a bit, with seemingly more and more restrictions added to every cumulative update. Hopefully some of these can be lifted at the feature matures, but I understand how difficult all this is.

People program absolute bloodbaths into functions.

Today, I want to look at one restriction that has a fairly simple workaround: Calling GETDATE().

Click through to see how you can replace calls to GETDATE() without too much hassle.

Comments closed