Syntax – Page 2 – Curated SQL

The Basics of Framing in Window Functions

Published 2025-12-01 by Kevin Feasel

In this article, we’ll explore the concept of framing in window functions. We’ll compare the differences between the ROWS and RANGE clauses and discuss when to choose one over the other. We’ll also highlight common pitfalls of framing and whether it applies to all types of window functions. By the end, you’ll better understand how framing works with window functions, making it seem less complex.

Click through for a primer on frames in window functions. Admittedly, if I were writing this article, I’d toss out most of the “pitfalls” section, as pitfalls 2 and 3 aren’t particularly relevant or pitfall-y (because SQL Server always defines a frame on a window function if you don’t). Instead, I’d add that there are some annoying limitations on RANGE frames, where the ANSI SQL standard allows you to use intervals like date or time when defining frames, so you can get records ranging from three hours ago to right now, for example.

But that said, it’s a good overview if you’re fairly new to window functions.

Comments closed

Using the PRODUCT() Function in T-SQL

Published 2025-11-24 by Kevin Feasel

Rajendra Gupta uses a reducer function:

SQL Server 2025 includes new features and enhancements. In the previous SQL Server 2025 tips, we have explored many new features. Have you explored the new Product() function? If not, this will walk you through the Product() function with several examples.

Read on to see how PRODUCT() works and how thoughtful the development team was around supporting window functions as an aggregate function.

Comments closed

Invoking REST API Endpoints in SQL Server 2025

Published 2025-11-21 by Kevin Feasel

Hristo Hristov makes a call:

One highly anticipated new feature in SQL Server 2025 is the ability to call an external REST API endpoint from the database server itself. This new feature opens the door to new data integration scenarios and delivers on the promise to “bring AI closer to data.” What are the steps to follow if you want to use this new feature?

I expect to see two things from this. First, some percentage of developers will abuse it and cause performance problems in the database. Second, some percentage of database administrators will panic about this and try to prevent its use even when it makes sense.

But hey, at least this time, they didn’t use the term “unsafe” to describe something DBAs don’t understand and thus cause a widescale panic.

Comments closed

Multiple Filters with Regular Expressions

Published 2025-11-20 by Kevin Feasel

Louis Davidson shows off some more of the power of regular expressions:

One of the practical uses of RegEx is more powerful filtering. One of the projects I am working on, (very slowly) is sharing some SQL utilities on GitHub, Utilities like looking at the metadata of a table, searching for columns, database sizes, etc. I usually use LIKE to filter data, which lets me simply use an equality search, or I can also do a partial value search when I don’t know exactly what I am looking for.

LIKE is quite useful but, as Louis points out, it does have its limits. And in those limits is where regular expressions do so well.

Comments closed

The Joys of FORMATMESSAGE

Published 2025-11-13 by Kevin Feasel

Louis Davidson listened to some advice:

A few weeks ago, I wrote a post on using temporary stored procedures in SQL Server. Kevin Feasel of Curated SQL had this reply Using Temporary Stored Procedures to Output Common Messages. I had heard of FORMATMESSAGE before, but I had completely coupled this in my mind with formatting SQL Server error messages. (Which turns out to be a great use of this tech)

Click through to see how it works and some additional testing with RAISERROR().

Comments closed

Fuzzy Text Match in SQL Server

Published 2025-11-11 by Kevin Feasel

Rob Farley is excited:

However, SQL Server 2025 does bring some great options for doing fuzzy string matches, making custom Data Quality options even richer. I’ve spoken about this at some user groups recently (including tomorrow, remotely for TriPASS, and in a few weeks in Melbourne and Sydney for Difinity), and in that session I go much deeper into how I see data matching going. I’ll also write more about these methods in future posts, but it’ll take a few posts, covering quite a few sub-topics.

If you want to see that session, our user group (the Triangle Area SQL Server Users Group) is hosting it Wednesday morning Australia time, or this evening US Eastern Standard Time.

Comments closed

Using Temporary Stored Procedures to Output Common Messages

Published 2025-10-30 by Kevin Feasel

Louis Davidson shows a neat use for temporary stored procedures:

On another connection (on another computer for that matter), I am right no doing some pretty long loads of some test data. The script is comprised of 6 queries, and they each may take 10 minutes (not completely sure, this is my first run of the scripts). And of course, I want to get some feedback on these queries to know how long they are taking.

One common way to do this is to put a PRINT statement between the queries so you can see the progress. But PRINT statements are notorious for one thing. Caching the output until the output buffer reaches some level.

One addition I’d make to Louis’s post is to make use of the FORMATMESSAGE() functionality that SQL Server 2016 introduced. This use case is right in its wheelhouse.

    SET @message = FORMATMESSAGE(N'%s%s%s%s',
			@Message,
			CASE
				WHEN @AddTimeToMessageFlag = 1 THEN CONCAT(N' : Message Time - ', SYSDATETIME())
				ELSE N''
			END,
			CASE
				WHEN @AddSpidToMessageFlag = 1 THEN CONCAT(N' : ProcessId - ', @@spid)
				ELSE N''
			END,
			CASE
				WHEN @AddOriginalLoginToOutputFlag = 1 THEN CONCAT(N' : LoggedInUserId - ', original_login())
				ELSE N''
			END);

FORMATMESSAGE() provides a moderate benefit to readability versus a lengthy CONCAT(). And if you always wanted to emit all fields versus the optional setup that Louis has in place, FORMATMESSAGE() makes the result even clearer to understand.

SET @message = FORMATMESSAGE(N'%s : Message Time - %s : ProcessId - %i : LoggedInuserId - %s',
			@Message,
			CAST(SYSDATETIME() AS NVARCHAR(100)),
			@@spid,
			original_login());

3 Comments

Decimal Precision and Rounding in SQL Server

Published 2025-10-27 by Kevin Feasel

Jiri D. provides a warning:

Do you ever worry about how you declare NUMERIC or DECIMAL data types in SQL?
Do you sometimes “add a bit more” precision—just to be safe?
Have you considered how that small decision could actually change your arithmetic results?

I ran into this recently when comparing data between two environments that should have produced identical results. One calculated field was slightly off — and the culprit turned out to be a difference in numeric data type declarations.

Read on to see what happened. The differences weren’t massive, but if you were expecting an exact match, seeing a difference, even at the 7th or 8th spot after the decimal, could be jarring.

Comments closed

Adding Commas to Numeric Output in SQL Server

Published 2025-10-27 by Kevin Feasel

Andy Yun is speaking my language:

One thing that’s always driven me crazy is when I have large numbers in my resultsets and the lack of commas for readability. For a lot of different things I do, the more commas a number has, the more attention I want to give to a given value.

Andy shows examples of formatting to two and zero spots after the decimal, respectively. In a talk I give on analyzing business data with T-SQL, I also demonstrate how to show currency-based results:

FORMAT(SUM(o.Quantity * sih.LastCostPrice), N'$0,###.##') AS TotalCost

This starts each cost record with a dollar sign, ensures you have commas in the thousands spots, and have a two-digit decimal value. That would return back a result like $31,409,113.00, which is a lot easier to read than 31409113.

Comments closed

Splitting Strings with T-SQL

Published 2025-10-22 by Kevin Feasel

Courtney Woolum splits a string:

If you’ve escaped string parsing thus far, count thyself lucky. I’ve seen some numbing scripts from pre-SQL Server 2016 when STRING_SPLIT was introduced. I think the fact this month’s T-SQL Tuesday is entirely on string parsing says enough about how many ways there are to paint this particular cat.

In the post, Courtney mentions learning early on to avoid using the APPLY operator. I want to have harsh words with whoever taught her that. Purposefully avoiding the APPLY operator artificially hamstrings your ability to write effective T-SQL code.

Comments closed

Category: Syntax