Press "Enter" to skip to content

Category: T-SQL

Generating Gaussian Numbers in SQL Server

Published 2025-10-15 by Kevin Feasel

Sebastiao Pereira generates random numbers:

Generating random numbers from a normal distribution is essential for accuracy and realistic modeling, simulation, inference, and algorithm design. This is useful for scientific, engineering, statistical, and AI domains. Let’s see how we can create Box Muller transform functionality in SQL Server without the use of external tools.

I’ve used this technique before in SQL Server and it works really well. Performance is fine and the results approximate a normal distribution like you’d want.

Comments closed

Table Switching in SQL Server

Published 2025-10-14 by Kevin Feasel

Rebecca Lewis swaps in a bag of sand while snatching the golden idol:

Use ALTER TABLE … SWITCH to move very large tables instantly.  Yes.  I said instantly.  ALTER TABLE .. SWITCH doesn’t copy the data or physically move it.  It just reassigns the page ownership.  This means that only the metadata with the data pointer changes, and that’s why it completes in milliseconds and barely touches the transaction log.

There are some strict conditions around when you can use this technique, but it’s as powerful as Rebecca mentions.

Comments closed

Static and Dynamic Bulk Insert into SQL Server

Published 2025-10-09 by Kevin Feasel

Rick Dobson inserts some data:

There are numerous use cases for multi-file imports of CSV files into a SQL Server table:

  • Dynamic SQL Server bulk insert loads are especially appropriate for tasks that extract content from multiple files to a SQL Server table where the source file names change between successive import jobs.
  • Static bulk insert loads target scenarios where the source file names do not change between successive import jobs.

Read on for examples of how to implement each. Admittedly, bulk insert has rarely worked all that well in my experience, whether due to permissions mishaps, poor data integrity, or sudden changes in data types between file runs. But it does tend to work a lot better if you have a specified data interchange format and a standardized process to prepare the data and make it available on disk for insertion.

Comments closed

The Intricacies of COUNT()

Published 2025-10-09 by Kevin Feasel

Louis Davidson can easily get to 20:

I was reading LinkedIn posts the other day when I saw this blog about what was apparently an interview question about some forms of a COUNT aggregate function

This was apparently asked in an interview. What will each of these constructs do in a SQL statement:

COUNT(*) = ?
COUNT(1) = ?
COUNT(-1) = ?
COUNT(column) = ?
COUNT(NULL) = ?
COUNT() = ?

There’s one tricky bit in this set. Louis then takes it a bit further with CASE expressions and variables, so check out the post for the answers as well as those additional examples in T-SQL.

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

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

Use Cases for Window Functions

Published 2025-10-01 by Kevin Feasel

I have a new video:

In this video, I take you through a variety of use cases for window functions, showing how you can solve common (and sometimes uncommon) business problems efficiently and effectively.

This video builds off of the prior two videos. Those prior two videos showed what the different window functions are and how they work. This one focuses primarily on solving business problems in sometimes-clever ways.

Comments closed