Press "Enter" to skip to content

Category: Syntax

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.

Leave a Comment

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).

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

More Types of Window Functions in SQL Server

Published 2025-09-24 by Kevin Feasel

I continue a series on window functions:

In this video, I continue a dive into each category of window function, quickly reviewing the four categories of window function (plus ordered set functions). Then, I cover offset window functions, statistical window functions, and ordered set functions.

This video includes some of the window functions I use most often (LAG(), LEAD()), some of the window functions I use least often, and even a few ordered set functions to boot. Combined, it’s about 45 minutes of content between this video and the prior.

Leave a Comment

Grouping Options in T-SQL

Published 2025-09-24 by Kevin Feasel

Chad Callihan rolls up the data:

When learning T-SQL, I’d wager that learning GROUP BY comes up early in the process. What may not be mentioned are the variations that can be added to a GROUP BY clause. Are you familiar with GROUP BY GROUPING SETS, GROUP BY ROLLUP, and GROUP BY CUBE? If you’ve never seen these used, or if you have and want a refresher, read on as we look at an example of each.

Of the three, CUBE is the one that I’ve used the least. I’ve found good instances where ROLLUP gives me exactly what I want for reporting purposes, and GROUPING SETS is powerful enough that I’ve made use of it a fair number of times. But CUBE just returns back too many combinations for what I’ve needed.

Leave a Comment

Concatenation and UNISTR in SQL Server

Published 2025-09-22 by Kevin Feasel

Koen Verbeeck takes a peek at a pair of new features in SQL Server 2025:

Often new T-SQL functionality is released first in the Azure cloud environment, before it makes its way to the on-premises SQL Server product. Recently, a new T-SQL function and operator went general available in Azure SQL DB (and Azure SQL Managed Instance): UNISTR and the ANSI SQL string concat operator (||). In this tip, we’ll introduce you to both concepts.

Koen looks at these in Azure SQL Database, though if you have the public preview of SQL Server 2025, you can try both out there as well.

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.

Comments closed

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.

Comments closed