A Fine Slice Of SQL Server
John McCormack tries out some stuff:
I don’t often go down code based rabbit holes, its just not what I do day to day but a while back, someone asked on twitter’s #sqlhelp hashtag if there was a character length limit to the STUFF function. The documentation didn’t have an answer. For that reason only, I decided to take a look.
Click through to see what John learned along the way.
Comments closedBack in my uni days I remember a Prolog assignment to solve “each letter represents a number” puzzles, and my solution being slow. Years later I tried it again and it worked out just fine, but by then the due date was in the past and they weren’t prepared to change my grade.
While these kinds of things can be fun (more so when there aren’t uni grades dependent on the solution), there are also times that it can be fun to rewrite some code in a way that is more intuitive, or that feels clever in a profoundly simple way.
Rob shares links to a few examples along those lines.
Comments closedErik Darling is always optimal:
Using the OPTIMIZE FOR UNKNOWN hint, or declaring variables inside of a code block to be used in a where clause have the same issue, though: they make SQL Server’s query optimizer make bad guesses, which often lead to bad execution plans.
You can read great detail about that here.
Read on for a bit of a deserved rant and an example to show why OPTIMIZE FOR UNKNOWN often doesn’t solve the problem.
Erik Darling does an odd search:
Most of the time, it’s some query that looks like this:
SELECT
c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.OwnerUserId LIKE ‘2265%’
The cruddy thing here is that… SQL Server doesn’t handle this well.
At first, my thought was, “Why not just use an inequality operator like p.OwnerUserID >= 22650000 AND p.OwnerUserID < 22660000 but then I remembered that we’re probably dealing with multiple orders of magnitude here and the whole thing made my head hurt a little.
Chad Callihan gets things sorted:
Have you ever needed a Top 10 list? It may sound like a simple task but not all lists are the same. Consider records where there are ties. Does that mean you need more than 10 records? If two records tie for first does that mean the third record is considered to be ranked second? No matter what type of list you need, SQL Server can help you find your way.
Click through for an overview of three of the four ranking functions in SQL Server. Poor NTILE().
Aaron Bertrand is a fan of this new function:
I have come across a lot of use cases for manufacturing rows on the fly, aside from the common goal of populating a large data set such as a numbers or calendar table. A few favorites include building sample data, pivoting an unknown number of columns, data extrapolation, and filling gaps in date or time ranges.
If you are on SQL Server 2022 or Azure SQL Database, or have been reading up on new features, you’ve likely heard about one of the better T-SQL enhancements: a new built-in function called
GENERATE_SERIES. The syntax is straightforward – it accepts arguments forstartandstop, and an optional argument to indicatestep(in case you want to iterate by more than1, or backwards):
Click through to see how performance for this compares to two methods we’ve used in the past to generate similar results.
Comments closedTariq Rasheed Al-Qaralleh recounts a customer problem:
In some cases, like when customers use Object-relational Mapping tools (ORM) Like Entity framework or LINQ, part of the code at the end will be converted to a TSQL executable statement.
For example, The LINQ with .Where () method will be a TSQL Query with a Where clause :
Query Syntax and Method Syntax in LINQ (C#) | Microsoft Learn
Read on for some of the practical consequences of doing this, including performance issues and possibly even runtime errors.
Tariq gives a couple examples of how to fix the issue, and a third possible fix is to pass in the IN clause as a table-valued parameter and join to that TVP.
Comments closedEitan Blumin just wants to switch the database type:
Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in “Single-User” mode in one of their environments.
To resolve it, I first tried running the following command:
ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;In response, I got deadlocked with the dreaded error 1205:
There were a few different attempts with no success until Eitan came up with the final script. Eitan’s analogy was to curling, though the first thing I thought of was Odysseus fighting off his wife’s suitors as he came back to claim his home.
1 CommentChris Johnson finds a language quirk:
Recently I found a quirk of T-SQL, where a group by statement was treating strings as the same if the only difference was one or more trailing spaces. So, ‘aa’ would be grouped with ‘aa ‘. I did some digging, and this is what I found.
Yeah, this isn’t just Microsoft’s T-SQL variant—it’s a standard part of SQL, as Chris notes later in the post.
My “just-so” story is that this might have been implemented to deal with CHAR(x) comparisons, such as CHAR(2) to CHAR(3). There’s no way to make that comparison unless you treat trailing spaces as irrelevant. Because we almost always use VARCHAR(x) or NVARCHAR(x), it isn’t something top of mind to most database practitioners, but there is a method to the madness.
Comments closedI think I have probably spent 500 hours of my life rewriting T-SQL Scalar UDFs to avoid all the performance problems associated with them.
The obvious choice is the Inline Table Valued Function, which has fewer performance issues baked in. For the kids out there: they don’t spill trauma.
But getting the rewrite right can be tricky, especially when it’s possible for the function to return NULL values.
Click through for the example.
Comments closed