Category: Syntax

Microsoft releases the classic SQL Server every couple of years, with some functionality added through regular updates. On the other hand, the SQL Server offering in Azure (Azure SQL Database and Managed Instance) receives the latest features earlier.

This post highlights some of the T-SQL functions currently available in Azure SQL but not yet in classic SQL Server. However, with the recent announcement of SQL Server 2025, this might change next year. Keep in mind that some of these functions are in preview, so their behavior might evolve as they reach general availability.

Click through for those examples.

Alternatives to Long IN() Lists in SQL Server

Published 2024-12-05 by Kevin Feasel

David Fowler still has a list:

This post comes off the back of my last, where I looked at issues caused by explicitly declaring a large number of values in an IN clause. The query processor ran out of internal resources and could not produce a query plan – When You’ve Managed to Confuse SQL With a Crazy IN Clause.

My suggestion was to put the values into a temp table and carry out a semi-join.

That got me thinking, which of the two methods would perform better.

I’m going to look at the two methods for different amounts of values and have a look at how they get on in terms of reads and total time.

Read on for the answer. If I’m surprised by anything in it, it’s that the threshold where temp tables out-perform the IN() clause is so high.

Against ODBC Functions

Published 2024-12-05 by Kevin Feasel

Andy Brownsword stakes a claim:

It’s a function, but not quite as we know it. They’re surrounded by { curly braces } and prefixed with fn. The kind of thing I’d expect to see in PowerShell.

It turns out they were introduced in ODBC versions 1-3, with the latter being based on the SQL-92 specification. These were introduced in 1995 and 1992 respectively, which likely explains why I don’t see them used.

I’m thinking hard and don’t believe I’ve ever seen anyone use these in code I’ve maintained. Thus, I agree with Andy: you probably don’t need them.

Avoid WHERE COUNT(*) = 0

Published 2024-12-02 by Kevin Feasel

Hubert Lubaczewski makes a comparison:

Every now and then I see something like this:
SELECT u.* FROM users u
WHERE 0 = (SELECT COUNT(*) FROM addresses a WHERE a.user_id = u.id);
and it kinda pains me. So figured, I’ll write about what is the problem with it, and how to avoid such constructs.

Read on to understand what the problem is and how you can fix it.

6 Comments

GROUP BY Clause Order and Performance in PostgreSQL

Published 2024-11-26 by Kevin Feasel

Andrei Lepikhov runs some tests:

PostgreSQL users often employ analytical queries that sort and group data by different rules. Optimising these operators can significantly reduce the time and cost of query execution. In this post, I will discuss one such optimisation: choosing the order of columns in the GROUP BY expression.

Postgres can already reshuffle the list of grouped expressions according to the ORDER BY condition to eliminate additional sorting and save computing resources. We went further and implemented an additional strategy of group-by-clause list permutation in a series of patches (the first attempt and the second one) for discussion with the Postgres community, expecting it to be included in the next version of PostgreSQL core. You can also try it in action in the commercial Postgres Pro Enterprise fork.

From a “this is a 4th generation language” perspective, there should never be a performance difference in the ordering of grouped columns in a GROUP BY clause. The database optimizer should always determine the optimal ordering. Read Andrei’s article to see how much of a challenge that can be in practice.

Comments closed

New Syntax in SQL Server 2022

Published 2024-11-25 by Kevin Feasel

Steve Jones talks syntax:

At the recent 2024 PASS Data Community Summit, I saw a lightning talk from Mikey Bronowski on the New T-SQL Functions in SQL Server 2022. Before the talk, I made a joke with him that none of these were new because it’s 2024. They’d been out for 2 years.

Mikey did a nice job, given that he only had 10 minutes, but it was enough to give attendees an idea of some new things they might investigate to use in their own code. If you missed the talk, or you don’t have access to the recordings, we have a series on SQL Server Central that covers these (Part 1, Part 2, Part 3) and helps you understand the new options. The list of language changes is also in the MS Docs under What’s New in the Language.

Steve asks about how much utilization there is of these. I think the problem is that so few organizations have adopted SQL Server 2022 that knowledge that these things even exist is quite low.

By the way, I do have a script covering many of these new bits of syntax as part of a talk on getting up to speed with core T-SQL changes since SQL Server 2012.

Comments closed

Creating Totals and Subtotals in Postgres with ROLLUP and CUBE

Published 2024-11-19 by Kevin Feasel

Elizabeth Christensen uses a pair of analytical operators:

Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, ROLLUP and CUBE. Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting.

I’ve used ROLLUP on occasion, but never found a great case when CUBE made sense in a report. I am, however, quite partial to GROUPING SETS, the third of these analytical operators and the one that gives you the most control.

Comments closed

The IS Operator in T-SQL

Published 2024-11-15 by Kevin Feasel

Eric Blinn wants to know of those NULLs:

Many data professionals search for help writing T-SQL queries containing columns with NULL values in a Microsoft SQL Server table. Some of the most common NULL functions include IS NULL, IS NOT NULL, NOT NULL, NULL, what is NULL, NULL vs NOT NULL, etc. In this SQL tutorial, the goal is to help you better understand how to work with NULL values in a SQL database.

Even the best of us have accidentally used = NULL in a script when we meant IS NULL. Which is totally different from ISNULL(), of course. And NULLIF(), naturally.

Comments closed

Materialized Views in PostgreSQL

Published 2024-11-14 by Kevin Feasel

Brent Ozar builds a view but a special one:

That query gets the top 100 users who have the most accepted answers. On my server, that takes about a minute to run – unacceptably long for a public-facing web site, for example.

Does that data need to be up to the second? Of course not. The leaderboard of the top 100 answerers isn’t going to change all that quickly. People who have successfully answered hundreds of questions aren’t going to suddenly disappear, nor is someone else suddenly going to rocket to the top.

For report queries like this, Postgres offers materialized views: a view that’s written to disk, and then updated only when you want to update it.

Read on to see how you can create one in PostgreSQL. Brent does touch on one of the differences between indexed views in SQL Server and materialized views in PostgreSQL while covering the process of creating, querying, and updating materialized views. In discussing how to update them, Brent covers en passant a second difference between indexed views in SQL Server and materialized views in PostgreSQL. Whether the “keep it up to date at all times” approach beats the “update it when you want but let data go stale in the meantime” approach is better, that’s something worth debating.

Comments closed

Moving Averages in T-SQL

Published 2024-11-13 by Kevin Feasel

Jared Westover does the math:

Even though I enjoy using SQL Server, there are some things other tools do better. For example, calculating moving averages or rolling totals is often simpler in tools like Power BI or Excel. That’s because Microsoft built those programs with that functionality in mind. Recently, we had to optimize a complex moving average query written for SQL Server 2008R2. Surprise! There’s no built-in function for moving averages in SQL Server. But don’t worry; I’ll show you how to make it work.

Read on for the solution, as well as the mess we had to work with prior to SQL Server 2012.

Comments closed