Press "Enter" to skip to content

Author: Kevin Feasel

Building an UPDATE … LIMIT in PostgreSQL

Published 2025-10-03 by Kevin Feasel

Laurenz Albe doesn’t have MySQL envy:

If you are reading this hoping that PostgreSQL finally got UPDATE ... LIMIT like MySQL, I have to disappoint you. The LIMIT clause is not yet supported for DML statements in PostgreSQL. If you want to UPDATE only a limited number of rows, you have to use workarounds. This article will describe how to do this and how to avoid the pitfalls and race condition you may encounter. Note that most of the following also applies to DELETE ... LIMIT!

Click through for what you can do in PostgreSQL instead. In T-SQL, we can use UPDATE TOP(n).

Comments closed

Performance of User-Defined Functions in Fabric Warehouses

Published 2025-10-03 by Kevin Feasel

Jared Westover shares some findings:

In Part One, we saw that simple scalar user-defined functions (UDFs) perform as well as inline code in a Fabric warehouse. But with a more complex UDF, does performance change? If it drops, is the code-reuse convenience worth the price?

I’m surprised that the performance profile was so good. I had assumed it would perform like T-SQL user-defined functions—namely, worse in general.

Comments closed

Creating Database Snapshots with sp_snapshot

Published 2025-10-03 by Kevin Feasel

David Fowler announces a tool update:

Presenting you with an updated version of our sp_snapshot procedure, allowing you to easily create database snapshots.

This new version fixes a bug that we’ve found in version 2 where snapshots will fail for databases with multiple data files.

We’ve also added the @STMTOnly parameter, allowing you to generate the scripts for creating the required snapshots without actually doing so.

Click through for more information, as well as where you can go to download the script.

Comments closed

RCSI Scenarios

Published 2025-10-03 by Kevin Feasel

Haripriya Naidu digs into a few scenarios:

When RCSI is enabled, I’d like to discuss three scenarios:

  1. UPDATE is in progress, and SELECT starts to run.
    Where does SELECT read from?
  2. SELECT runs, and there are no concurrent operations or uncommitted transactions. Where does SELECT read from?
  3. SELECT is running, and now an UPDATE starts to run concurrently. What happens to SELECT that is in progress? What about the UPDATE that started? Does it wait for SELECT to finish?

Click through to see what happens during each of these scenarios.

Comments closed

A Primer on Principal Component Analysis

Published 2025-10-02 by Kevin Feasel

Harris Amjad explains the basics of principal component analysis:

In this series of tips, we will delve into the unsupervised learning branch of Machine Learning. Principal Component Analysis (PCA) is a powerful technique for dimensionality reduction, but its mathematical foundation involving eigenvalues and eigenvectors can be intimidating. This tip aims to demystify PCA, explaining its purpose, how it works, and its use in visualizing high-dimensional data.

Click through to learn how it works. This is a solid primer.

Comments closed

Choosing between Data Scalers in a Data Science Project

Published 2025-10-02 by Kevin Feasel

Bala Pirya C performs a comparison:

In this article, you will learn how MinMaxScaler, StandardScaler, and RobustScaler transform skewed, outlier-heavy data, and how to pick the right one for your modeling pipeline.

Topics we will cover include:

  • How each scaler works and where it breaks on skewed or outlier-rich data
  • A realistic synthetic dataset to stress-test the scalers
  • A practical, code-ready heuristic for choosing a scaler

Read on to learn more about each of these three scaler types, the use cases that best fit each of them, and even a flow chart at the end.

Comments closed

VARCHAR or NVARCHAR

Published 2025-10-02 by Kevin Feasel

Brent Ozar asks a question:

You’re building a new table or adding a column, and you wanna know which datatype to use: VARCHAR or NVARCHAR?

If you need to store Unicode data, the choice is made for you: NVARCHAR says it’s gonna be me.

But if you’re not sure, maybe you think, “I should use VARCHAR because it takes half the storage space.” I know I certainly felt that way, but a ton of commenters called me out on it when I posted an Office Hours answer about how I default to VARCHAR. One developer after another told me I was wrong, and that in 2025, it’s time to default to NVARCHAR instead. Let’s run an experiment!

This is going back a long way (June of 2020) but one of my earliest YouTube videos is entitled NVARCHAR Everywhere. I’ve gotten a lot better at presentation skill since then (and have a much nicer camera), but I still stand by the arguments.

Comments closed

Tracking Memory Consumption in Fabric SQL Database

Published 2025-10-02 by Kevin Feasel

Lance Wright tracks memory utilization:

SQL Database in Fabric continues its commitment to providing you with robust tools for database management, performance monitoring, and optimization. Earlier this year, we released a performance dashboard to help you monitor and improve the performance of your SQL Database in Fabric. We’ve improved upon those performance monitoring capabilities with the ability to track memory consumption. This new capability delivers real-time, actionable data regarding the memory utilization of all database queries to help you make more informed decisions and manage SQL Database resources more efficiently.

Read on to see what you can do with this.

Comments closed

Narrowing down Slowdown Causes in SQL Server

Published 2025-10-02 by Kevin Feasel

Kevin Hill continues a series on solving the age-old “The server is slow!” problem:

At this point you’ve:

  • Defined what “slow” means and built a timeline (Part 1).
  • Checked things outside SQL Server like network, storage, and VM noise (Part 2).

Now it’s time to open the hood on SQL Server itself.

I think Kevin’s checklist is a pretty solid one for the type of client he often deals with: one without an in-house DBA or the expertise to stay on top of server problems.

Comments closed

Row Expansion in T-SQL

Published 2025-10-02 by Kevin Feasel

Louis Davidson solves a problem:

On LinkedIn a few days ago, there was a question that I found interesting about what was purported to be an interview question. The gist was “say you have a set that looks like this:

OrderId Item Quantity
------- ---- ---------
O1      A1   5
O2      A2   1
O3      A3   3

and you need to expand it to be one row based on the value in Quantity

Admittedly, this kind of problem is fairly uncommon in the business world, though this is exactly the sort of thing that a tally table can solve, and that’s what Louis uses to solve the problem. Louis also gets brownie points for praising CROSS APPLY along with tally tables.

Comments closed