Press "Enter" to skip to content

Curated SQL Posts

Choosing COUNTROWS() over DISTINCTCOUNT() in DAX

Published 2026-08-10 by Kevin Feasel

Phil Seamark provides some guidance:

Counting transactions is one of the most common things a retail model is asked to do. How many transactions did we do in March. How many in New Zealand. How many involved a T-shirt.

The obvious measure is the one everybody writes:

Transactions = DISTINCTCOUNT ( FactTransaction[TransactionID] )


It is correct, and on a large fact table it is one of the most expensive things you can ask the storage engine to do.

Read on for a better alternative, and more importantly, when it’s a better alternative.

Leave a Comment

Reviewing the SQL Server 2022 STIG

Published 2026-08-10 by Kevin Feasel

Marlon Ribunal reads some guidance:

Security on your SQL Server is important. That doesn’t need any explaining. But where do you start when evaluating the security of your SQL Server? If you are like me, and probably for many DBAs, that’s the hardest part. You know security matters, but without a structured baseline, it’s easy to overlook configuration issues that could expose your environment to unnecessary risk. Starting with a proven checklist gives you a clear way to identify gaps before they become problems.

And how do you even implement the principle of least privilege on the instance and database level?

Read on to see what the US Department of Defense recommends. Also, I like pointing out Tracy Boggiano’s dbachecks updates that include CIS security auditing, though that is a few years old at this point so I’m not 100% sure how well it works.

Leave a Comment

Tips on When to Use Microsoft Fabric Shortcuts

Published 2026-08-10 by Kevin Feasel

James Serra offers up some guidance:

Imagine separate Sales, Finance, Shared Data, and Executive Analytics workspaces. Sales owns sales transactions, Finance owns budgets, and Shared Data owns common tables such as Customer, Product, and Date. Executive Analytics needs selected data from all three, but it does not want to copy everything into another lakehouse and maintain another set of pipelines. Instead, it creates shortcuts to the authoritative tables and presents them together in its own lakehouse.

This is where the beauty of Fabric OneLake shortcuts becomes obvious. To a report developer or analyst, the Executive Analytics lakehouse can look like one complete collection of tables. That person might not even know which tables are physically stored there and which are shortcuts—and usually should not need to know. Fabric resolves those paths behind the scenes, which is one reason I call Fabric “the great data unifier”: it can present one logical data estate without forcing all the data into one physical location.

Click through to learn more about what shortcuts are, how they work, and when you should (or should not) use them.

Leave a Comment

A Performance Monitoring Update

Published 2026-08-10 by Kevin Feasel

Erik Darling has a new video:

Erik Darling here with Darling Data, the one, the only, the monitoring tool mogul of SQL Server. Today I wanted to sort of update people on the state of the performance monitor project because there are some things that are useful to the general population that I feel like I should bring up.

So the current version of the performance monitor is 3.1. If it’s been a while since you’ve tried this thing out, I would suggest giving it another shot because there have been some really, really big improvements, not only in the collected data and sort of visualization and printification of things, but also in the UI, UX, the sort of experience that you get out of it.

Click through for the video and make sure you don’t get sucked into any organ harvesting rings. They may sound alluring at first, but they never work out the way you want them to.

Leave a Comment

Drilling into Separate Fact Tables via Detail Rows in Power BI

Published 2026-08-10 by Kevin Feasel

Chris Webb crosses fact tables:

If you have a DirectQuery fact table in Power BI you can use user-defined aggregations to improve query performance; querying a smaller, summarised copy of your data in an Import mode aggregation table is always going to be faster than querying a large fact table containing all your detail data that is in DirectQuery mode. What’s more a composite model like this can have a much smaller footprint in memory than a model where all your tables are in Import or Direct Lake mode, which means you can use a smaller Fabric capacity SKU. However, in some cases you can take the same tables that you would use to create a composite model like this and solve the same problem slightly differently without using aggregations.

Click through for an example of this.

Leave a Comment

Handling Backpressure in Fabric Real-Time Intelligence

Published 2026-08-10 by Kevin Feasel

Greg Low talks about backpressure:

In any real-time data system, there’s a point where the incoming event rate can exceed what the system can process. This condition is known as backpressure.

Backpressure can occur for a few reasons — a sudden spike in data volume, slow or overloaded consumers, or limited throughput in one part of the pipeline. If it’s not handled properly, it can cascade through the system, eventually causing delays or even a complete stall in event processing.

There are several strategies to handle backpressure effectively.

Click through for those mechanisms. The pedant in me who hates how “real-time” has replaced “online” in terms of systems terminology—not Greg’s fault in the least—would point out that if you truly have a real-time system, you can’t afford to have backpressure because any sort of delay would be inimical to it being real-time.

Leave a Comment

Spatial Data in MySQL vs PostgreSQL

Published 2026-08-07 by Kevin Feasel

Aisha Bukar contrasts two data platforms:

MySQL and PostgreSQL both store spatial and geometric data, but they take fundamentally different approaches. While MySQL bakes spatial support directly into its engine, PostgreSQL separates two ecosystems: native geometric types (built-in, flat-plane, non-geographic), and PostGIS (a full GIS extension with coordinate systems, projections, and hundreds of spatial functions).

For most real-world location-aware applications — GPS tracking, geofencing, distance queries — PostGIS is the more powerful option, though MySQL 8.0 closed the gap considerably for common use cases. This guide compares their architectures, data types, indexing strategies, spatial functions, and what to watch out for if you’re migrating between them.

Click through for the comparison.

Leave a Comment

Issues with INSERT-EXEC

Published 2026-08-07 by Kevin Feasel

Erik Darling has two problems:

All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…

why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.

Click through to learn more about both problems, including windows of time when SQL Server gets blackout drunk and can’t account for its time.

Leave a Comment

Implementing the Gauss Kronod Quadrature Formula in SQL Server

Published 2026-08-07 by Kevin Feasel

Sebastiao Pereira implements a formula:

Gauss-Kronrod quadrature is a numerical integration method that extends the Gaussian quadrature providing high accuracy and a built-in error estimation. It is based on the work of Carl Friedrich Gauss and Alexander Kronrod. The Kronrod method reuses all Gauss nodes adding extra points obtaining one integral estimate for Gauss and another for Kronrod and the differences gives the estimated error.

Click through to see how you can do this in T-SQL.

Leave a Comment

Datatypes and Constraints in Microsoft Fabric Data Warehouses

Published 2026-08-07 by Kevin Feasel

Louis Davidson starts digging in:

There are three major “engines” in Fabric that I have seen that use T-SQL. There is the SQL Azure Fabric engine, the Lakehouse, and the Data Warehouse. Having this capability to manipulate data is wonderful, but there are some things you need to understand before you start writing code (unless you want to learn them the hard way like I have. I also will not profess to have found all of these differences. Changing my mindset when using these engines was the subject of this editorial

Read on for some inconsistency.

Leave a Comment