Press "Enter" to skip to content

Curated SQL Posts

Subscribing to Power BI Reports

Published 2022-04-22 by Kevin Feasel

Reza Rad looks at e-mail subscriptions of Power BI reports:

Have you ever wondered is it possible to have updates of the Power BI report to be emailed to you (or some other colleagues) on a daily basis? Power BI, fortunately, has this feature, it is called Subscription. Subscriptions are helpful ways to send an up-to-date version of the report and dashboard to the users’ email addresses on a scheduled basis. In this article and video, I’ll explain what a subscription is and how it works in Power BI.

Click through for the video and complete blog post.

Comments closed

Splitting Strings with Quoted Names

Published 2022-04-22 by Kevin Feasel

Daniel Hutmacher mixes separators with regular characters:

Suppose you have a delimited string input that you want to split into its parts. That’s what STRING_SPLIT() does:

DECLARE @source nvarchar(max)='Canada, Cape Verde, '+    'Central African Republic, Chad, Chile, China, Colombia, Comoros';

SELECT TRIM([value]) AS[Country]
FROM STRING_SPLIT(@source, ',');

Simple enough. But delimited lists are tricky, because the delimiter could exist in the name itself. Look for yourself what happens when we add the two Congos to the list:

Daniel has a clever solution to the problem.

Comments closed

Optimizing Index Spools

Published 2022-04-22 by Kevin Feasel

Francisco looks at index spools:

When we are analyzing execution plans, we may come across different types of Spool operators – Table Spools, Row Count Spools, Window Spools or Index Spools – that the Query Optimizer chooses for specific purposes. In this post we are going to briefly look into the Index Spool, how it can sometimes lead to suboptimal query performance, and what can be done to easily fix it.

My favorite description of this is Erik Darling’s: spools are SQL Server’s passive-aggressive way of telling you “I’m not saying you need an index but you need an index.”

Comments closed

Logic Apps: Source Control and Deployment

Published 2022-04-22 by Kevin Feasel

Koen Verbeeck has a two-parter. First up is storing Logic App code in source control:

At a data warehouse project I’m using a couple of Logic Apps to do some lightweight data movements. For example: reading a SharePoint list and dumping the contents into a SQL Server table. Or reading CSV files from a OneDrive directory and putting them in Blob storage. Some of those things can be done in Azure Data Factory as well, but it’s easier and cheaper to do them with Logic apps.

Logic Apps are essentially JSON code behind the scenes, so they should be included into the source control system of your choice (for the remainder of the blog post we’re going to assume this is git).

The second post covers deployment:

It’s easy to duplicate an Azure Logic App in a resource group, but unfortunately you cannot duplicate a Logic App between environments (you might try to copy paste the JSON though). So unless you want to hand craft every Logic App yourself on each of your environments, you need a way to automatically deploy your Logic Apps. It’s easier, faster and less error-prone than any manual method.

Check out both posts.

Comments closed

Calculating Running Totals with Window Functions

Published 2022-04-22 by Kevin Feasel

Steve Jones shows off a good use case for window functions:

Recently I was looking at some data and wanted to analyze it by month. I have a goal that is set for each day and then an actual value. I wanted to know how I was tracking against the goal, as a running total. If my goal is 10 a day, then I ought to actually get to 10 the first day, 20 for the second day (10 + 10), etc.

Read on to see how Steve solved the problem.

Comments closed

Search in KQL

Published 2022-04-21 by Kevin Feasel

Robert Cain looks at the search operator in KQL:

In this post we will examine the KQL (Kusto Query Language) search operator. Search allows us to look across all columns in one or more tables for a specific text string.

The samples in this post will be run inside the LogAnalytics demo site found at https://aka.ms/LADemo. This demo site has been provided by Microsoft and can be used to learn the Kusto Query Language at no cost to you.

Click through to learn more about this very useful operator.

Comments closed

Entity Framework and Include Operations

Published 2022-04-21 by Kevin Feasel

Josh Darnell has a warning:

I can imagine someone reading that and not seeing the gravity of the situation. “Hey, 500 rows isn’t that many – we have modern hardware!”

I thought it was worth writing about a real world situation where this can get seriously out of hand.

Read on for a scenario in which 64 rows turns into 100,000 rows pretty quickly.

Comments closed

Quotation Marks in Azure SQL DB vs Managed Instance

Published 2022-04-21 by Kevin Feasel

Michael Bourgon notices a difference between Azure SQL DB and Managed Instance/box product:

I was trying to get some Xquery parsing working, and ran into a weird error:

"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

Weird.  Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.  

Read on for the solution.

Comments closed

Using Buffer Pool Extension in SQL Server

Published 2022-04-21 by Kevin Feasel

Chad Callihan looks at buffer pool extension:

Perhaps you started out with X amount of memory when your SQL server was brought online and over time, with additional load and activity on that SQL server, users are not quite getting the type of performance they’re used to getting. Sure, you can buy more memory. What if that’s not an option?

If you’re running low on memory and need a little boost, enabling buffer pool extension can take advantage of an SSD as an “extension” for the buffer pool.

This is one of those interesting features that probably help a small number of customers but shouldn’t be generally useful. That’s because even with SSD performance improvements, memory is still a couple orders of magnitude faster, so as long as you have the ability to increase RAM, that brings much better performance.

Comments closed