Press "Enter" to skip to content

Curated SQL Posts

Microsoft Fabric Quotas

Published 2025-02-26 by Kevin Feasel

Mihir Wagle puts the kibosh on things:

On February 24, 2025, we launched Microsoft Fabric Quotas, a new feature designed to control resource governance for the acquisition of your Microsoft Fabric capacities. Fabric quotas aimed at helping customers ensure that Fabric resources are used efficiently and help manage the overall performance and reliability of the Azure platform while preventing misuse.

Note that these are not quotas you set on your users, but rather quotas that Microsoft sets on you.

Comments closed

Maxing Out on Stored Procedure Parameters

Published 2025-02-26 by Kevin Feasel

Louis Davidson tries it out:

So I replied: “Challenge Accepted”, well, actually I replied with a gif of Neo saying it, but the effect was the same. So, I decided to just see, what would that look like. Coincidentally I am testing the new template for the Simple Talk site, and a function with 2100 parameters seemed like some code that screams out: “BIG!” Testing is should always be about pressing the limits of your code, so why not.

It turns out that you cannot have 2101 parameters in a single stored procedure definition. But this is definitely an example of Swart’s 10% Rule. It also nets Louis my most coveted category: Wacky Ideas.

1 Comment

Building a RegEx Emulator in SQL Server

Published 2025-02-26 by Kevin Feasel

Sebastiao Pereira offers a fourth-best solution:

Regular expressions (REGEX) let you adaptively investigate, employ, and authenticate text data. This makes it easy to search for simple or complex string patters. There is no direct way to do this in SQL Server, but in this article we look at some SQL functions you can create to emulate regex like functionality.

Regular expressions are coming to SQL Server 2025 and are in Azure SQL Database, so that’s the best option when it becomes available. The second-best option is to use CLR and offload your regular expressions work to .NET, especially if you’re using a library like SQLSharp to do so. The third-best option would be to do this in Python or R with ML Services, though that’s going to be a bit of setup effort and will probably be somewhat limiting. And if all else fails, this is an admirable fallback.

Comments closed

Working with JSON_OBJECTAGG() and JSON_ARRAYAGG() in Azure SQL

Published 2025-02-25 by Kevin Feasel

Koen Verbeek tries out a couple of fairly new functions:

I need to construct JSON from data in our database, but I find the existing FOR JSON PATH limited when the data is not located in one single row but rather scattered over multiple rows. Is there another method on how to handle JSON data in SQL Server? Learn how to use the new SQL Server JSON Functions JSON_OBJECTAGG and JSON_ARRAYAGG in this article.

These aren’t available on-premises yet, though given that there’s a new version of SQL Server coming out in 2025, there’s a good chance we’ll have it by then.

Comments closed

Comparing Oracle and PostgreSQL Transaction Systems

Published 2025-02-25 by Kevin Feasel

Laurenz Albe performs a comparison:

The transaction system is a core component of a relational database. It provides services that facilitate developing applications that guarantee data integrity. The SQL standard regulates some of the features of database transactions, but leaves many details unspecified. As a consequence, the transaction systems of relational databases can differ substantially. These days, many people are trying to get away from Oracle database and move to PostgreSQL. To migrate an application from Oracle to PostgreSQL, it is crucial to understand the differences between the respective transaction systems. Otherwise, you can end up with nasty surprises that may jeopardize performance and data integrity. So I thought it would be useful to compile a comparison of the features of transactions in Oracle and PostgreSQL.

Read on for that dive into how the two products compare.

Comments closed

Aging Accounts Receivable Data in Power BI

Published 2025-02-25 by Kevin Feasel

Marco Russo and Alberto Ferrari inform us that we owe them money:

The Accounts Receivable (AR) Aging report helps companies track overdue receivables and better manage their cash flow. It is a common requirement from the finance department that often represents a challenge for Power BI reports, especially when you want to show the trend over time. Here is a typical visualization for the Open Amount at the end of each month, colored by the age range of the Accounts Receivable.

Read on for a dive into the report concept and some of the most common issues you may run into.

Comments closed

Power BI Semantic Model Monthly Refresh via Fabric Data Pipelines

Published 2025-02-25 by Kevin Feasel

Chris Webb has another way for scheduling refreshes:

I’m sure you already know how to configure scheduled refresh for your semantic models in Power BI. While the options you have for controlling when refresh takes place are generally good enough – you can configure daily or weekly refreshes and set up to eight times a day for refreshes to take place – there are some scenarios it doesn’t work for, such as monthly refreshes. Up to now the workaround has been to use Power Automate to trigger refreshes (see here for an example) or to call the refresh API from another application. Now, with Fabric, you have a much better option for scheduling refreshes: Data Pipelines.

Click through for the demonstration.

Comments closed

Defining “Laziness” in R

Published 2025-02-24 by Kevin Feasel

Maëlle Salmon, Athanasia Mo Mowinckel, and Hannah Frick are quite studious:

In the programming world, laziness can often be a good thing: it is both a human quality that can motivate automation efforts, and a programming concept that avoids wasting resources such as memory. Now, when reading code or documentation, seeing the word “lazy” can be confusing, because of its polisemy: it carries several meanings. In this post, we will enumerate the different possible definitions of “lazy” in R code.

Read on for a variety of contexts around “lazy,” including lazy evaluation, lazy database queries, lazy loading, and more.

Comments closed