Press "Enter" to skip to content

Curated SQL Posts

Ingesting IoT Data into SQL Server via Python

Published 2025-09-18 by Kevin Feasel

Hristo Hristov builds an app:

MQTT is a lightweight Industrial IoT communications protocol allowing efficient communication to and from edge devices such as machines, sensors, and actuators. How can we get data from an MQTT on-premises or cloud broker and persist them in an SQL Server database? How can we leverage the newest features in SQL Server 2025 to make efficient query compilations and build a scalable solution for a data pipeline for permanently storing IoT data?

Read on for the code, most of which is in Python.

Leave a Comment

Finding Row Counts in Tables

Published 2025-09-18 by Kevin Feasel

Andy Brownsword wants a quick answer:

A question I ask myself often when exploring unfamiliar data sets. So here’s a quickie:

Click through for the script. This is a lot faster than SELECT COUNT(*), something that can really burn you when there are a few trillion rows in a table and your index scan interferes with ongoing operations. I’ve noticed that reading these counts from statistics is usually pretty solid, but generally, we’re interested in orders of magnitude, in which case 39,308,149 and 39,308,206 are close enough for purposes of understanding which tables are heftier.

Leave a Comment

The Readability Benefit of Splatting in Powershell

Published 2025-09-18 by Kevin Feasel

David Seis simplifies the code a bit:

Have you ever written a PowerShell command so long that it stretched across the screen? Or had to update a script and hunt through a long parameter list to change a single value? Splatting solves that problem by letting you pass multiple parameters to a command using a single variable.

Read on for some examples, including a good example of how to make similar cmdlet calls easier to read.

Leave a Comment

Responding to “The Server is Slow”

Published 2025-09-18 by Kevin Feasel

Kevin Hill shares some consulting advice:

Stop. Don’t Open SSMS Yet.

You’ve heard it before: “The server is slow.”

What does that actually mean?

If you jump straight into SQL Server looking for blocking, bad queries, or a missing index, you’re working with bad input. And bad input = wasted time.

The real work starts before you ever connect to SQL Server.

Click through for some guidance on how to frame the conversation. Even if you aren’t a consultant, I think it’s a good idea to scope and triage the problem in a similar fashion before trying to dive in and see what you can find.

Leave a Comment

Regular Expression Counts and Positions in SQL Server 2025

Published 2025-09-18 by Kevin Feasel

Louis Davidson wraps up a series on regular expressions:

I am only combining them into a short version because they are, in how they work, very similar to all the other functions. I certainly will demonstrate all the functionality for each function, but not to the extra level I have in previous blogs.

This time, I will cover:

  • REGEXP_INSTR Returns the starting or ending position of the matched substring, depending on the option supplied.
  • REGEXP_COUNT Returns a count of the number of times that regex pattern occurs in a string.

Read on to see how these work in SQL Server 2025.

Leave a Comment

Types of Window Functions in SQL Server

Published 2025-09-17 by Kevin Feasel

I have the first of a two-part video up:

In this video, I walk through four categories of window function, plus a somewhat-related type of function in SQL Server. From there, I demonstrate how aggregate window functions and ranking window functions work.

The second part will come out next week and will cover the other types of window function. Otherwise, this was shaping up to be a 40-minute video and that’s a bit too long.

Leave a Comment

Troubleshooting Bulk Insertion in SQL Server

Published 2025-09-17 by Kevin Feasel

Rick Dobson lays out some common issues:

Most SQL bulk insert and SQL Server openrowset tutorials skip file access issues that can stop imports cold. Both the bulk insert statement and openrowset function rely on the SQL Server service account to read a source file. The SQL Server service account must have read permission on the file or its folder. It is also convenient to have read & execute as well as list folder content permissions. Also, non-standard source file locations (e.g., C:\Users\Public\Downloads) may not grant default read access to the SQL Server service account – always verify before use.

Click through for several recommendations, links to additional resources, and a few scripts along the way.

Leave a Comment

Using Sankey Diagrams in Power BI

Published 2025-09-17 by Kevin Feasel

Ben Richardson creates a visual:

Ever wished you could see exactly how customers move through your sales funnel, or how costs flow across your business?

A Sankey Diagram makes those flows visible, showing not just totals but how values split and connect between categories.

In Power BI, the Sankey Diagram is available as a custom visual from AppSource, designed to reveal relationships and flow patterns.

There are specific times and places for Sankey charts. It requires having a natural flow in your data—that is, you need different states of data, those states should typically only “move” in one direction, you have paths to get from one state to another, and there is enough variety in pathing that not all of the data is going to the same location. The more of these rules you violate, the less useful a Sankey diagram is.

Leave a Comment

Calendar-Based Time Intelligence in DAX

Published 2025-09-17 by Kevin Feasel

Marco Russo and Alberto Ferrari grab a calendar:

Since its first release in 2010, DAX has had a set of time intelligence functions to simplify calculations like year-to-date, year-over-year, and so on. However, the calculations only supported the Gregorian calendar, without addressing similar requirements for other calendars, such as the 4-4-5, ISO, and many other non-Gregorian calendars. With the classic time intelligence, the columns of the Date table were unknown to the time intelligence functions, with the only exception of the date column in the Date table, typically Date[Date].

Click through to see what Marco and Alberto have come up with.

Leave a Comment