Press "Enter" to skip to content

Author: Kevin Feasel

Performance Comparison: Tally Table vs GENERATE_SERIES()

Published 2025-03-06 by Kevin Feasel

Steve Jones performs a pair of tests:

I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.

I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.

Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.

Comments closed

Random Functions in PostgreSQL 17

Published 2025-03-06 by Kevin Feasel

Leo Hsu and Regina Obe look at updates to the random() function in PostgreSQL:

Have you ever wanted to get a random integer between 1 and 10 and been a little annoyed the slightly cryptic code you had to write in PostgreSQL? PostgreSQL 17 random functions make that simpler. Sometimes it’s the small changes that bring the most joy.

Click through to see what it took to get a random integer or floating point number prior to PostgreSQL 17 and how it’s a fair bit simpler today.

Comments closed

An Overview of Real-Time Intelligence in Microsoft Fabric

Published 2025-03-06 by Kevin Feasel

Christopher Schmidt lays out a use case:

Operational reporting and historical reporting serve distinct purposes in organizations. Historically, data teams have heavily leaned on providing historical reporting, as being able to report on the operational business processes has proved elusive.  

As a result, organizations have created reports directly against the operational database for operational needs or spend significant effort trying to get analytical tools to refresh faster using ‘micro-batching’ and/or keeping a tool like Power BI in directQuery mode. These efforts come with the goal of ‘moving data through the system as fast as possible’. 

Click through for an architecture diagram and an example scenario.

Comments closed

Object-Level Security in Power BI Desktop

Published 2025-03-06 by Kevin Feasel

Nikola Ilic locks things down:

You’ve heard about the Oblect-level security feature in Power BI, but you’ve also heard that you CAN’T configure it directly from the Power BI Desktop? And, that you must use an external tool, such as Tabular Editor, to configure and manage OLS.

Well, this is not the case anymore! With the new TMDL view in Power BI Desktop, you can define Object-level security directly in the Power BI Desktop.

Read on for a tutorial on what it takes to implement object-level security.

Comments closed

Visualizing PostgreSQL Query plans

Published 2025-03-06 by Kevin Feasel

MIchael Christofides views a plan:

When you’ve got a slow Postgres query, EXPLAIN and its parameters are incredibly useful for working out why.

However, the information returned can be difficult and time-consuming to interpret, especially for more complex queries. Over the years, people have built quite a few tools for visualizing Postgres query plans. As one of those people, I’m a little incredibly biased, but as a fan of many of the others, I hope to do them justice.

Read on for the options. I think the SQL Server world is a bit spoiled with Solarwinds Plan Explorer (even if the product hasn’t really changed since Solarwinds bought Sentry One), but some of these options look very solid.

Comments closed

Avidity KPIs in T-SQL

Published 2025-03-05 by Kevin Feasel

I have a new video:

In this video, we will take a look at two KPIs for measuring avidity. We will also show off how to use ranking window functions to order groups of customers.

Click through for the video. There are far too many measures of avidity for me to do a good job explaining them all, and so many of them are closely tied to the specific nature of the business, but hopefully this at least gives you ideas of how the business side may look at user avidity or stickiness.

Comments closed

Making a Query SARGable

Published 2025-03-05 by Kevin Feasel

Haripriya Naidu explains SARGability:

Having the right index is helpful, but are you using the predicate (WHERE clause) correctly to make efficient use of that index?

This is where the term SARGable comes into play. SARGable stands for Search ARGumentable. If SQL Server is able to limit the search space while evaluating the predicates and can seek right at the page(s) to get the values, then it is SARGable.

Read on for an explanation of why this is important, as well as several examples of what is SARGable versus what isn’t. The most important thing about SARGability is that you pronounce it like “Sarge” and not “sarg.”

Comments closed

Multi-Measure Calculations in Relational Databases

Published 2025-03-05 by Kevin Feasel

Greg Low describes a common business problem:

But while food wholesale systems will need to deal with quantities like I described in that post, they often have another layer of complexity. Items are often sold by:

  • Quantity
  • Weight
  • Quantity and Weight

This is an interesting look at how the domain can drive what a proper solution looks like. It also seems like a good use case for 6th normal form, with unit quantity and unit weight tables to prevent NULL from cropping up.

Comments closed