Press "Enter" to skip to content

Category: T-SQL

Understanding String Concatenation with FOR XML PATH

Published 2023-07-12 by Kevin Feasel

Brent Ozar did some noodling:

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

But eventually, I took a deep breath and read the code more closely to understand what it was doing.

I do like to joke that this is cryptic code that gets handed down from generation to generation, with each generation saying “Don’t touch the code, for you do not understand it.” But as of SQL Server 2017, you don’t need to do this anymore and can use STRING_AGG().

Comments closed

The Tally Table Splitter

Published 2023-07-12 by Kevin Feasel

Steve Jones talks tally tables:

That being said, years ago I got an article from Jeff Moden on the tally table. I hadn’t used this, and was fascinated. I know Itzik had written about numbers tables early on, but it hadn’t caught my attention. However, in a follow-up, Jeff wrote about a splitter function, which would use the tally table to split strings efficiently. This is the function (credit to Jeff in his article):

Click through for that function. The CLR-based tally table function is still faster, but if you can’t have CLR in your environment, and you split strings so frequently that you need a splitter, and STRING_SPLIT() just doesn’t do it for you (or you’re on an older version of SQL Server), this is a good solution. It’s also the foundation for a principle I have with T-SQL query tuning: sometimes you need to think in terms of pivoted or unpivoted data and operate on it versus the raw data. Here, we’re unpivoting a string into one row per character to get what we need out of it much more efficiently than if we tried to work the string by itself.

Comments closed

T-SQL Query Transformations and Performance

Published 2023-07-12 by Kevin Feasel

Erik Darling isn’t satisfied with “good enough”:

Query tuning is hard work, especially when queries run for a long time and you have to test various changes trying to get it down to a reasonable runtime.

There are, of course, things you pick up intuitively over time, and figuring out where problems in actual execution plans has gotten easier with operator runtimes.

Beyond basic query tuning intuition comes the really creative stuff. The stuff you’re amazed anyone ever thought of.

Click through for two really interesting examples.

Comments closed

Using Subqueries in a SELECT Statement

Published 2023-06-27 by Kevin Feasel

Greg Larsen builds a subquery:

Did you know you can include a SELECT statement within another SELECT statement? When a SELECT statement is embedded within another statement it is known as a subquery. There are two types of subqueries: basic subquery and correlated subquery.

In this article I will be discussing both types of subqueries and will be providing examples of how to use a subquery in different places within in a SELECT statement.

Greg has a good write-up on the topic of subqueries and does well to separate correlated from non-correlated subqueries.. And if you want to learn more about those, as well as common table expressions, I put out a video on the topic just last week.

Comments closed

Finding the Actual Error Line in sp_executesql

Published 2023-06-23 by Kevin Feasel

Thom Andrews solves a problem:

Notice that the error line states line 2 not line 8, which is the line the sys.sp_executesql was called on. Knowing the line the error occured on within the dynamic batch is certainly important, but if you’re working with multiple dynamic batches you have no way of knowing which dynamic batch might have produced the error; was it the one executed on line 100? Line 200? Line 350?

Solving this problem wasn’t wasn’t exactly simple, and it came with a couple of caveats.

Click through for the approach. As Thom mentions, it isn’t perfect, but it is reasonable and interesting.

Comments closed

The Value of QUOTENAME

Published 2023-05-31 by Kevin Feasel

Quoth Chad Callihan, “Occasionally more”:

QUOTENAME can be used to make sure database objects are valid in your query. Most of the time, objects like table names only contain valid characters, so there’s nothing to worry about. But nobody’s perfect. Let’s look at an example of what can happen when somebody creates a table with a forward slash in the name and see how QUOTENAME can be used to query against it.

QUOTENAME is also a good way of preventing SQL injection, though I still prefer appropriate use of exec sp_executesql in any case in which it’s possible to use.

Comments closed

Filling Gaps in T-SQL

Published 2023-05-30 by Kevin Feasel

Itzik Ben-Gan has a new challenge:

The task involves developing a stored procedure called GetBalances that accepts a parameter called @accountid representing an account ID. The stored procedure should return a result set with all existing dates and balances for the input account, but also gap-filled with the dates of the missing workdays between the existing minimum and maximum dates for the account, along with the last known balance up to that point. The result should be ordered by the date.

My first thought was last observation carried forward, which is now available in SQL Server 2022 (Itzik’s solution 2). I kind of thought of solution 3, though did not think through the mechanics of how it’d work and so I get no credit there.

Comments closed

Trying NTILE

Published 2023-05-17 by Kevin Feasel

Chad Callihan looks at the fourth ranking window function:

Have you ever used the NTILE function? Or have you even heard of the NTILE function? It seems to be one of the lesser known, lesser used window functions in SQL Server. I’ve never come across it in the wild but maybe there are those that use it all the time. Either way, let’s have a look at what it does and how it can be used.

Click through for a demo. I definitely use it a lot less than ROW_NUMBER(), RANK(), and DENSE_RANK(), but I have used it to some good effect in the past, mostly in cases where I’ve wanted to focus on the top X% of data for an analysis.

Comments closed