Press "Enter" to skip to content

Curated SQL Posts

Implementing SCD Type 2 in Microsoft Fabric

Published 2026-09-08 by Kevin Feasel

Nikola Ilic reminds us that the Kimball model is alive and well:

Slowly Changing Dimensions (SCD) are one of the fundamental concepts in dimensional modeling. If you are not sure what dimensional modeling is, I suggest you first check the series of articles I wrote on data modeling some time ago.

And, when it comes to SCD in particular, SCD Type 2 is rightly considered “the queen” of the SCDs for analytical workloads. Without going into details (since the main goal of this article is to show you HOW to implement the SCD Type 2 in Microsoft Fabric), I’ll just briefly introduce the general concept behind the SCD Type 2.

Click through for a primer and examples in the warehouse and lakehouse.

Leave a Comment

Azure Databricks and Power BI Storage Modes

Published 2026-09-08 by Kevin Feasel

Chris Webb makes a choice:

In case you haven’t already seen the blog post on the Power BI blog or the discussion on LinkedIn, we at Microsoft published a new white paper last week to help you decide which storage mode to use when you’re using Power BI to create semantic models and reports on data stored in Azure Databricks. You can find the announcement and the link to the paper here.

Click through for Chris’s thoughts and check out the whitepaper.

Leave a Comment

Understanding the Receiver Operating Characteristic Curve

Published 2026-09-04 by Kevin Feasel

Ken Koon Wong digs into ROC and the area under the curve (AUC):

We see ROC-AUC so often with classification models, we know the higher the better, but there is always that, well it depends scenario. I’ve always wanted to know what the pitfall is, how to avoid it, and how to do better. Let’s go from the basics on how to code ROC-AUC from scratch to decision curve analysis!

Click through to learn how to calculate ROC and dig into the topic a bit. H/T R-Bloggers.

Leave a Comment

Tracking SQL Server Login Failures

Published 2026-09-04 by Kevin Feasel

Ed Pollack builds some infrastructure:

Failed logins are one of the clearest early-warning signs of trouble on a SQL Server – whether that’s a misconfigured connection string, an expired password, or an actual unauthorized access attempt. Yet, by default, SQL Server won’t proactively tell you when they happen; you have to go looking.

This guide walks through how to pull login failure data using sys.xp_readerrorlog, filter it by time and error type, parse it into readable columns, aggregate repeat offenders, and automatically email a summary report — turning a passive log file into an active security and troubleshooting tool.

Click through for the process and scripts.

Leave a Comment

Decomposition Options Available in T-SQL

Published 2026-09-04 by Kevin Feasel

Jerry Nixon shares some options:

Application developers already know what happens when one method does everything: it becomes difficult to read, test, reason over, and safely change. We use patterns like decomposition, encapsulation, and explicit dependencies because they solve those problems.

T-SQL does not give us classes, inheritance, interfaces, or polymorphism in the same way C# does, but that does not mean good software practices stop applying when logic moves into the database.

Decomposition is a good example. Breaking complex database logic into sensible, well-defined components can reduce complexity, improve readability and maintainability, and make individual pieces easier to test. These are established, respected, and proven techniques for building great software, whether the code runs in an application or inside the database.

My problem is, there are performance costs to T-SQL decomposition. Unfortunately, Jerry doesn’t cover that at all in his post, but attempts at decomposing in SQL Server often fail for exactly that reason.

Leave a Comment

False Enrichment from a SIEM

Published 2026-09-04 by Kevin Feasel

Andreas Wolter takes a look at an alert:

This week, early morning, a customer alerted me that my account was possibly involved in an incident.

The alert came from Rapid7 InsightIDR:

Andreas has an example in which the SIEM decided that his user account was the relevant one, despite there being no logged in user. It’s an interesting story around how it’s critical to understand your sources and what they’re actually telling you.

Leave a Comment

Cross-Table Date Math

Published 2026-09-04 by Kevin Feasel

Erik Darling does some date math covering multiple tables:

So, so we’re going to use this query, which, if I remember its provenance correctly, came from the Stack Data Explorer site. I can just never find it when I go look there again. But it’s, it’s, it’s, it’s the intent of the query is to find posts that had a lot of very early upvotes, and this query was always very slow, and to me, the interesting part of the query was that the where clause was looking for a date diff in columns on two tables. Now, under normal circumstances, if you had both of these columns in the same table, right, you could, you could, if you were denormalized a bit, but this would be a terrible denormalization.

Click through for a clever use of an indexed view and a non-clustered columnstore index. Which, incidentally, marks one of the few times in which I’ve seen actual value in non-clustered columnstore indexes.

Leave a Comment

Compiling Power BI Calculation Groups

Published 2026-09-03 by Kevin Feasel

Phil Seamark looks at an optimization:

Power BI now avoids compiling calculation items that a query has already filtered out. The time saved is before the first storage engine event. It does not make the scans themselves faster.

Will I benefit? If your query filters a calculation group down to a subset of its items, it may compile faster. A single filtered group can benefit, although often only by a little. The largest gains are in models where calculation groups reference one another, because the engine used to expand combinations the query never needed. If your query has no calculation groups, or uses every item in them, there is nothing to prune.

Click through to see what the hubbub is all about and if it might affect you.

Leave a Comment

From SQL to PySpark and Spark SQL

Published 2026-09-03 by Kevin Feasel

Andy Brownsword gives Spark a try:

I’ve spent years shaping data with SQL Server, however after pulling at the threads of Fabric I’m opening notebooks and finding PySpark.

At first glance the difference is stark, but it’s not quite the dramatic shift it appears. If you’re not familiar, let’s look at what’s very similar, and where the true differences are.

There’s plenty of nuance in the syntax differences and behavioral differences between the platforms, but Spark SQL is just as ANSI compliant at this state as pretty much any other platform, and PySpark feels a lot like a chained quasi-functional approach to SQL because of Spark’s Scala heritage.

Leave a Comment

Full-Text Index Management Permissions

Published 2026-09-03 by Kevin Feasel

Emad Al-Mousa tests some permissions:

I was exploring SQL Server Text Indexes, and while exploring it I stumbled upon the function sys.dm_fts_index_keywords.

According to the “current” version of the documentation (up to 1 September 2026): https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-fts-index-keywords-transact-sql?view=sql-server-ver17

sysadmin role is required to run this function, I found out that this is not true !

Click through for the test. Granted, this does require CONTROL on the database, so not something that J. Rando db_datawriter can do.

Leave a Comment