Press "Enter" to skip to content

Category: T-SQL

Concatenating String Values in SQL Server 2025

Published 2025-10-16 by Kevin Feasel

Louis Davidson plays around with the new string concatenation operator:

I was looking around for stuff to post about the other day, so I read through the latest What’s new in SQL Server 2025 and I found this:

|| (String concatenation) Concatenate expressions with expression || expression.

My interest was piqued to say the least. Using + to concatenate string data has always had its issues, and as much as I love the CONCAT function, it is kind of clunky to use versus a proper operator.

This is part of the ANSI SQL standard, and that standard does specify most of its behavioral patterns. Read on as Louis tries it out in a variety of circumstances.

Comments closed

Tuning Window Functions in SQL Server

Published 2025-10-15 by Kevin Feasel

I have a new video:

In this video, I show you various techniques you can use to make window functions faster, including proper indexing and usage of batch mode. I also demonstrate the performance difference between RANGE and ROWS.

This wraps up my series on window functions, and although I pack a lot of content into the video, I highly recommend checking out the links for deeper dives into performance.

Comments closed

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