T-SQL – Curated SQL

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.

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.

The Downside of UNISTR()

Published 2024-11-01 by Kevin Feasel

Solomon Rutzky shares some thoughts:

Since the new UNISTR function doesn’t provide new functionality, only convenience (“syntactic sugar” as some would say; see comment below), I would argue that it should not only use a more standard syntax, but also not waste the opportunity and provide more substantive convenience by handling several commonly used escape sequences. I suspect that the number of times people would use “\n” is several orders of magnitude more than the number of times people would inject emojis or other non-keyboard characters. Even better would be to incorporate common escape sequences into standard string parsing.

Read on for Solomon’s comment explaining why he is not a fan of UNISTR().

Comments closed

Aggregate Window Functions in SQL Server

Published 2024-10-31 by Kevin Feasel

Steve Jones does a bit of aggregation:

I looked at row_number() in a previous post. Now I want to build on that and do some counting of rows with COUNT() and the OVER clause. I’ll show how this differs a bit from a normal aggregate.

Read on to see one example of how aggregation window functions (available in SQL Server since 2012) are so powerful.

Comments closed

T-SQL Notebooks in Microsoft Fabric

Published 2024-10-31 by Kevin Feasel

Dennes Torres tries out T-SQL notebooks:

T-SQL Notebooks is one of the new features announced during FabCon Europe.

The most distracted could miss the fact this is a new feature at all. Yes, it is. Notebooks were capable to support Spark SQL, but T-SQL is something new.

The main examples being announced are built with data warehouses, but let me confirm and highlight this:

T-SQL Notebooks support lakehouses as well.

There is at least one limitation: DML is not supported with lakehouses.

Saving my rant about lakehouses vs warehouses in Fabric, do read what Dennes has to say about T-SQL notebooks as they exist today.

Comments closed

Query Processor Ran out of Internal Resources

Published 2024-10-31 by Kevin Feasel

David Fowler explains an error:

Recently I received a cry for help over Teams. The issue was that an application was throwing up the following SQL error,

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I’ll be honest, that’s not one that I had seen before but it seemed pretty self explanatory. the query was just too complex for SQL to cope with. I asked what the query was, the answer was something similar to the snippet below,

Read on to learn what the problem was, as well as David’s answer. David had a simple rewrite retaining the IN clause, though you could also rewrite this with an INNER JOIN or even an EXISTS. One of those two alternative approaches might have a better performance profile, though there are no guarantees.

1 Comment

Backing up SQL Server via T-SQL

Published 2024-10-23 by Kevin Feasel

I have a new video:

In this video, I show how to perform a variety of database backup operations via T-SQL, as well as how (and why) to back up to NUL and how to back up a database to a network share.

This one is not quite as lengthy as the prior video in the series: just 20 minutes instead of 30. That said, I do cover quite a bit of content around taking backups, something that every infrastructure DBA should be familiar doing.

Comments closed

Troubleshooting Chains of Common Table Expressions

Published 2024-10-17 by Kevin Feasel

Jared Westover calls me out:

I started using Common Table Expressions (CTEs) in Oracle before Microsoft SQL Server and found them easier to read than a derived table. Something about reading from the top down just clicked in my brain. As the years have passed, I’m less of a fan for one reason: troubleshooting a chained CTE with several links is hard—especially when it’s someone else’s code. Even the ones I wrote years ago fill me with unease when something breaks. Is there an easier way to break down each link in the chain when things go wrong?

This is, I would argue, the biggest inconvenience around using common table expressions. The fact that SQL Server cannot materialize the contents of CTEs is likely a more prevalent problem, but challenges in how you can troubleshoot the individual parts of common table expressions are real.

Comments closed

Thoughts on Combining UPDATE Operations

Published 2024-10-16 by Kevin Feasel

Brent Ozar thinks about consolidation:

I’ve got a BEGIN TRAN in there before the updates just so I can test the same queries repeatedly, and roll them back each time. The execution plan for the updates is quite nice: SQL Server divebombs into the supporting indexes:

Relatively few rows match, so our query does less than 1,000 logical reads – way less than there are pages in the table. In this case, separate UPDATE statements make sense.

Brent then continues with, but what do we do when we need to perform multiple independent scans of the same table? Read on for Brent’s answer, but definitely check out Thomas Franz’s comments, which indicate a potential complicating factor.

Comments closed

Functions in JOIN and Performance Problems

Published 2024-10-15 by Kevin Feasel

Steve Stedman lays out a warning:

When writing SQL queries, it’s easy to focus on getting the right results without thinking too much about performance. One common mistake that can lead to significant slowdowns is using functions in the JOIN ON clause of a SELECT statement.

While SQL Server supports a wide range of built-in functions, using them incorrectly—especially in the join conditions—can severely impact performance. Let’s break down why this happens, how it can affect your query speed, and what you can do to avoid it.

Read on for the answer.

Comments closed

Category: T-SQL