Press "Enter" to skip to content

Curated SQL Posts

Improving Shiny and RMarkdown Inputs

Published 2026-07-29 by Kevin Feasel

Thomas Williams is back with more:

In my last post on improving inputs in R Markdown/Shiny, I covered four improvements to quality of life in interactive reports and dashboards. In this blog post I have three more, all approaches I’ve used to add professionalism to self-service R Markdown files.

In yet another plug for R Markdown: most of these techniques can be used in a single *.Rmd file, or can be included in many by putting them in a common CSS or javascript file and linking to it.

Click through to see what you can do.

Leave a Comment

Database Refactoring with VS Code

Published 2026-07-29 by Kevin Feasel

Drew Skwiers-Koballa shows off some functionality:

SQL database projects are a fundamental tool for keeping your database in source control, tracking changes and collaborating on quickly evolving database needs. Not to be forgotten, their superpower is being able to dynamically generate deployment scripts that match the declared state and the exact environment you’re deploying to. SQL project development becomes more challenging when a database needs serious changes, where the requirements of the system have evolved to the point of having to rename tables and move objects between schemas.

This should be pretty familiar to people who have worked with various iterations of database projects in Visual Studio over the years.

Leave a Comment

Huge Table Partitioning

Published 2026-07-29 by Kevin Feasel

Michael J. Swart partitions a rather large table:

Aaron Bertrand wants you to consider using partitioned tables and the sliding window pattern to help archive old data.

That’s a great idea. In fact, I’d like to do that at my own job. I have a truly humungous log table (Terabytes) and its clustered index is already on CreatedDate so it’s a good candidate for this pattern.

Click through to see how, but also one pain point you’re likely to run into when adding partitioning to existing large tables.

Leave a Comment

A Primer on Indexing in SQL Server

Published 2026-07-29 by Kevin Feasel

Ed Pollack has a guide:

Indexes are supposed to make SQL Server faster – so why do so many databases end up slower, bloated, and harder to maintain when they have more of them? It usually comes down to misapplied indexes rather than missing ones. There may be too many that are too wide, tuned with settings that don’t fit the workload, or built on assumptions that stopped being true years ago.

This guide walks through the most common SQL Server index tuning mistakes seen in production environments, such as over-indexing, oversized INCLUDE lists, unnecessary fill factor settings, misuse of SORT_IN_TEMPDB, over-aggressive index maintenance – and the myth that heaps are a shortcut to speed. Features real examples.

I think this serves as a reasonable overview of the topic. You can certainly get into more nuance on a number of the topics, but this is a good starting point.

Leave a Comment

The Performance Impact of Visual Calculations

Published 2026-07-29 by Kevin Feasel

Marco Russo and Alberto Ferrari dig into the nuance:

The goal of visual calculations is to simplify some reports and calculations, rather than to optimize performance. However, it is common sense that – in some scenarios – visual calculations can bring some benefit from the performance point of view.

The main idea is that a report may precompute some values and then, to further elaborate on them, it may use the content of the virtual table rather than recompute the values multiple times.

Read on to learn how you can adjudge what that visual calculation is doing for you in terms of performance relative to alternatives. But hey, if you’re stuck with Power BI Report Server, you don’t have to worry about visual calculations potentially slowing anything down. Mostly because you don’t get visual calculations.

Leave a Comment

Incremental Liquid Clustering in Apache Spark

Published 2026-07-28 by Kevin Feasel

Miles Cole lays out a recommendation:

Liquid Clustering was already a better abstraction than static partitioning due to its flexible nature. Fabric Spark Runtime 2.0 fixes the part that had me actively cautioning customers to reconsider blindly adopting it: the cost of maintaining the layout.

In Runtime 1.3 (Delta 3.2) a small append followed by OPTIMIZE would rewrite every file in a partial Z-Cube. In Runtime 2.0, the incremental strategy only touches files that are unclustered, small, or carrying a high density of deletion vectors. That changes Liquid Clustering from an occasional, potentially expensive maintenance operation into something that works beautifully with workloads of any shape and with adjacent layout optimizations. Batch or streaming writes. Auto Compaction and/or Fast Optimize. With the new incremental strategy, Liquid Clustering is highly compatible and highly efficient, and should now take its rightful place as the de facto new data layout strategy.

Read on to learn more about what Liquid Clustering is, how it works, and why you should think about it if you’re using Fabric Spark Runtime 2.0.

Leave a Comment

The Power of PBIP

Published 2026-07-28 by Kevin Feasel

Ruben Van de Voorde lays out an argument:

The PBIP format is an alternative to PBIX for storing the metadata that defines your semantic model and report. It allows more robust workflows and is far more agent-friendly, while the PBIX binary format packs everything into one file.

In this article, we argue that you should use PBIP format by default, deferring to the PBIX format only when PBIP isn’t an option.

Like when you’re using Power BI Report Server because then you’re not allowed to have nice things.

Leave a Comment

Partition Switching and Archive Tables

Published 2026-07-28 by Kevin Feasel

Aaron Bertrand shares some thoughts:

Something I’ve been thinking about lately is how much work I force onto SQL Server simply because data gets old. An order processing system may need to keep data for three years for warranty reasons or seven years for compliance reasons, but that doesn’t mean that data has to live all those years in your hottest, most active table and database. It also doesn’t mean you should spend a lot of effort moving it, row by row, to an archive table, never mind a completely different database. Only to delete it later. If you’re constantly fighting archiving processes that interfere with everyday workload, this conversation might be for you.

Click through for the article. One additional thing I could recommend looking into is using something like PolyBase for that archival. I have a demonstration of the process and it essentially uses the same technique SQL Server professionals used prior to partitioning in 2005. That particular scenario would work best when the archival data is stuff you very rarely query, but can allow you to move data off of relatively expensive fast storage onto relatively less expensive and slower storage.

Leave a Comment

Retrieving Plans from DMVs and Query Store

Published 2026-07-28 by Kevin Feasel

Deborah Melkin concludes a video series on how to get execution plans in SQL Server:

I really wanted to put this together because I feel like understanding these differences is important to understanding how we can troubleshoot performance problems and where we need to be looking for these pieces of information to get that full picture of where to spend our time. It makes us better performance tuners. I hope you found this helpful and you leave with a better appreciation for these nuances.

Click through for the video, as well as a transcription on the blog post.

Leave a Comment