Press "Enter" to skip to content

Curated SQL Posts

How Random are SQL Server Random Functions?

Published 2025-04-24 by Kevin Feasel

Rick Dobson publishes a book of guaranteed random numbers, like 41 and 8:

Microsoft provides T-SQL developers with three functions (rand, newid, and crypt_gen_random) for generating random numbers. Each of these functions is effective at returning random values, but feature sets associated with each function make them best suited to different use cases. This tip’s Solution section presents an overview of common use cases for random numbers in SQL Server along with references to learn more about random numbers in SQL Server. The first substantive provides a short comparative overview of the three functions. The remaining tip sections present and describe T-SQL examples for evaluating the randomness of values returned by SQL Server pseudorandom number generators.

Knowing that SQL Server doesn’t have some nice entropy engine (which does exist, typically as an embedded device that plugs in to a USB port) to pull from, it does a pretty good job of things.

Leave a Comment

A Primer on Indexing in SQL Server

Published 2025-04-24 by Kevin Feasel

Kevin Hill goes back to basics:

“What Is an Index?”
I get this question a lot, especially from developers and sysadmins who’ve been handed a SQL Server and told, “Keep it running fast.” No pressure.

Given that the percentage of people in this industry who have actually seen a phone book continually decreases, it’s for the best that Kevin didn’t use that worn-out example.

If you’re new to indexing in SQL Server, this is a good way to jump-start your knowledge. Then, you spend a while learning the exceptions to the rules and the foibles in existing systems.

Leave a Comment

Securing SQL Server Backups to Azure via Managed Identity

Published 2025-04-24 by Kevin Feasel

Arun Sirpal doesn’t want to generate a SAS token:

I do believe most people know about the ability to backup your SQL server databases to URL (from Azure VMs).  If you recall you would use the storage key ( ideally not) or a SAS token, from there you would create a SQL credential that is bound to the SAS token (as a secret). Lots of moving parts especially when it comes to rotation time. So now, if you are on the newer versions of SQL server (SQL Server 2022 Cumulative Update 17 ) start thinking about backups via managed identities.

Click through to learn how and why.

Leave a Comment

Kafka Consumer Offset Changes with KIP-1094

Published 2025-04-23 by Kevin Feasel

Alieh Saeedi looks at a change in Apache Kafka 4.0.0:

Consumer offsets are at the heart of Apache Kafka®’s robust data handling capabilities, as they determine how data is consumed, reprocessed, or skipped across topics and partitions. In this comprehensive guide, we delve into the intricacies of Kafka offsets, covering everything from the necessity of manual offset control to the nuanced challenges posed by offset management in distributed environments. We further explore the solutions and enhancements introduced by KIP-1094 (available in Kafka 4.0.0), offering a closer look at how it addresses these challenges by enabling more accurate and reliable offset and leader epoch information retrieval.

Click through for an overview of how consumer behavior works, as well as what KIP-1094 does.

Leave a Comment

Comprehensions in Python

Published 2025-04-23 by Kevin Feasel

I have a new video:

In this video, I show how to use comprehensions in Python to generate lists, dictionaries, and sets. I also run a quick performance test, comparing a list comprehension to an equivalent for loop.

It can take a little bit of time to get used to the syntax, but once you do, comprehensions are quite powerful.

Leave a Comment

When to Use a Python Notebook vs Spark Notebook in Microsoft Fabric

Published 2025-04-23 by Kevin Feasel

Gilbert Quevauvilliers lays out the plan:

This is the first blog post in a series of blog posts where I dive into how to use Python notebooks instead of Spark notebooks. For example, I will show you how to run a SQL query from a Lakehouse table and get it into a data frame. Read and write to a Lakehouse table and more.

NOTE: This is still in preview, but I personally think that this is worth investing time in learning.

The reason I am using the term Python is because the notebook can ONLY use Python and not any of the other languages available in a Spark

Also, in fairness, I’ve heard people working on Microsoft Fabric within the company reference these as ‘Python notebooks,’ so Gilbert is in good company.

Leave a Comment

Session-Scoped Temp Tables in Microsoft Fabric now GA

Published 2025-04-23 by Kevin Feasel

Twinkle Cyril gets something GA:

Introducing distributed session-scoped temporary (#temp) tables in Fabric Data Warehouse and Fabric Lakehouse SQL Endpoints.

#temp tables have been a feature of Microsoft SQL Server (and other database systems) for many years. In the current implementation of Fabric data warehouse, #temp tables are session scoped or local temp tables. Global temp tables are not included in this release.

Session-scoped #temp tables exist only within the session in which they are created and last only for the duration of that session. They are not visible to other users or sessions and are automatically dropped from the system once the session ends or the user decides to drop the temp table. These tables are accessible to all users without requiring specific artifact-level permission.

Click through for examples of how it works and how you can specify a session-level temp table over a local temp table.

Leave a Comment

Expression Reordering in PostgreSQL

Published 2025-04-23 by Kevin Feasel

Andrei Lepikhov speeds up a query:

Occasionally, you may come across queries featuring complex filters similar to the following:

SELECT * FROM table
WHERE
  date > min_date AND
  date < now() - interval '1 day' AND
  value IN Subplan AND
  id = 42';

And in practice, it happens that a simple rearrangement of the order of conditions in such an expression allows for speeding up (sometimes quite notably) the query execution time. Why?

Read on for the answer. In a perfect world, SQL is a 4th generation language and the order of operations should make zero difference for query performance. In practice, as Andrei shows, this is a challenge for the developers of the relational databases we use.

Leave a Comment