Press "Enter" to skip to content

Category: T-SQL

Reducing the Cost of Delete Operations in SQL Server

Published 2024-03-04 by Kevin Feasel

Ben Johnston eats the elephant:

One of the first things you learn when working with SQL Server, and other transactional based SQL systems, is that set based operations perform best. If you are querying data, a cursor pulling individual rows doesn’t perform as well as a single query. It doesn’t matter if that cursor is on the client side or the server side. A set-based operation is more efficient, runs faster, locks less, and is generally better than submitting multiple queries.

This is also generally true with delete statements. This post covers the exceptions to that rule. Large delete statements impacting many rows and large amounts of data (millions of rows and many gigs of data) can actually have decreased performance. With transactional systems, such as SQL Server, each transaction follows the ACID standard. Part of that standard ensures that transactional statements either complete or roll back fully – partial transactions are not allowed. For a delete statement, that means that all of the rows specified by the delete are removed from the table, or none are removed and the data rolls back to the original state. The delete and rollback behavior must be predictable and consistent or the data could be left in a contaminated, unreliable state. Performing very large deletes can present some challenges and needs to be treated differently in production systems.

Read on for the reasoning behind this, as well as several techniques you can use and how they compare.

Comments closed

EXISTS and NOT EXISTS in T-SQL

Published 2024-03-01 by Kevin Feasel

Erik Darling plays Descartes in his spare time:

Let’s talk about two of the most overlooked and undervalued facilities in the SQL language: EXISTS and NOT EXISTS. Perhaps they’d get more traction is they were called THERE and NOT THERE, but but it would be perilously easy to start confusing your WHERE and THERE clause.

Often besmirched by someone who read a blog post about MySQL in 1998 as “subqueries, which are slower than joins”, they’re two of the most useful things you can grasp and use regularly.

This post does a great job of explaining when you’d want to use EXISTS and NOT EXISTS.

Comments closed

Find and Replace Text Strings in SQL

Published 2024-02-29 by Kevin Feasel

Ed Pollack deals with the lack of regular expressions in T-SQL:

This article is focused on how to write, simplify, and get the most out of string data using the least amount of code to do so. It’s a reminder that code complexity is equivalent to technical debt and that in these scenarios, less is always more.

Text-manipulating functions will be introduced and reviewed throughout this article, so feel free to perform searches against it to find what you are looking for.

Read on for mechanisms to split strings into multiple rows, build strings from multiple rows, and search+replace within strings.

Comments closed

Common Mistakes in T-SQL Code

Published 2024-02-23 by Kevin Feasel

Rich Benner builds a list:

One potential issue here is the lack of time to delve really deeply into each programming language at one’s disposal. The thing about SQL Server is that it is a language that performs very differently than, say, C#. Those languages are row based, which means it’s common to iterate through a data set and process each row individually. This works quite well in those languages, but quite terribly in SQL Server. Being a set-based language, SQL Server prefers to deal with the whole data set at once.

Because of these nuances, it’s easy for developers to make mistakes when creating SQL server code. Let’s go through some common errors that we see.

Read on for several common mistakes that we often find in our code.

Comments closed

Weirdness with Aggregation

Published 2024-02-15 by Kevin Feasel

Erik Darling digs into a problem. Part 1 sets up the scenario:

Here’s the query plan, which yes, you’re reading correctly, runs for ~23 seconds, fully joining both tables prior to doing the final aggregation.

I’m showing you a little extra here, because there are missing index requests that the optimizer asks for, but we’ll talk about those in tomorrow’s post.

The wait stats for this query, since it’s running in Batch Mode, are predictably HT-related.

Part 2 covers those missing indexes:

I’ve taken a small bit of artistic license with them.

The crappy thing is… They really do not help and in some cases things get substantially worse.

Maybe it’s because it’s early and I’m trying to compile things in my head rather than actually trying it out, but it seems like a combo of CTE + CROSS APPLY or a pair of CROSS APPLY statements could work better (especially with a good index), assuming that join doesn’t need to be in place. Given the query as it is, with two MAX() aggregations and no GROUP BY clause, that could be an avenue for improvement, though one I have not actually tested. Nonetheless, read both of Erik’s posts.

Comments closed

Batching Data Copy Operations in SQL Server

Published 2024-02-07 by Kevin Feasel

Jana Sattainathan performs a data migration:

The purpose of this post is to give you a skeleton of TSQL code on how to perform a copy of a large table (say from one database to another within the same SQL Server Instance) by breaking up the data into manageable chunks. I have blogged about how to break-up any Oracle or SQL Server table data into chunks in this blog post.

Read on to learn why you don’t want to perform the operation as a single query, and one alternative that’s available. The query is a good bit more complex, but that complexity is for a good cause.

Comments closed

Counting Path Elements in T-SQL

Published 2024-02-05 by Kevin Feasel

Steven Sanderson switches over to T-SQL for a bit:

Yesterday I was working on a project that required me to create a SQL query to generate a table of accounts receivables pathways. I thought it would be interesting to share the SQL code I wrote for this task. The code is as follows:

Click through for the code. I was playing a bit of code golf in my mind, seeing how I might modify things. One big example would be to replace the STUFF() + FOR XML PATH combo with STRING_AGG(), assuming the SQL Server instance is 2017 or later, or if the database is in Azure SQL DB or SQL MI. The count of a specific character is an interesting approach, and this is the first time I’ve had to wonder why there isn’t a helper function in T-SQL to do that. But considering that this is the first time I’ve had to ask that question, maybe that’s an answer in itself.

Comments closed

Replacing DISTINCT with EXISTS

Published 2024-01-30 by Kevin Feasel

Andy Brownsword makes a switch:

The DISTINCT clause in a query can help us quickly remove duplicates from our results. Sometimes it can be beneficial to stop and ask why. Why do we need to use the clause, why are we receiving duplicates from our data?

I see this typically due to a JOIN being used where we don’t really want all of those results. This could be a ‘does something exist’ check such as if a customer has ever ordered before. The issue comes when there are multiple rows returned like a frequent customer in this example.

As an alternative to this, Andy shows how you can use the EXISTS clause to find records matching some criterion.

Comments closed