Press "Enter" to skip to content

Category: Syntax

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

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

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

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

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

CONTINUE and BREAK in T-SQL WHILE Loops

Published 2025-09-30 by Kevin Feasel

Erik Darling tries to count and gets kind of far along. The key part of the video is the use of CONTINUE and BREAK as control logic in WHILE loops. CONTINUE is something I’ve very rarely used, though it’s pretty useful. BREAK, however, is something I’m quite familiar with, especially in conjunction with record counts. This way, you can perform small updates (e.g., update 1000 records) with a filter that tells you which records still need updated, and when @@ROWCOUNT is below 1000, you know you’re done with the updates.

Comments closed

RETURNING Clause in PostgreSQL Update Operations

Published 2025-09-25 by Kevin Feasel

Hans-Jürgen Schönig wants the output:

PostgreSQL 18 offers a couple of new features such as asynchronous I/O (aio), improvements around indexing, and a lot more. However, there is one feature that has really caught my attention. While it might go unnoticed by many users, it is really powerful and, to me, somehow represents everything I like about PostgreSQL.

The improvement I want to focus on today is related to the way the “RETURNING-clause” works in PostgreSQL 18.

This behavior is very much like the OUTPUT clause in T-SQL. Though it appears that OLD and NEW are not themselves pseudotables like SQL Server’s INSERTED and DELETED, as there is an example that includes old and new columns together in the same row.

Comments closed