Category: T-SQL

Deborah Melkin finds the optimizer in a “Dunno, here’s a guess” scenario:

A little while back, my coworker was having trouble with a query and asked me about a strange thing they were seeing in the execution plan: The estimated rows for the query was showing 100.

100 felt like an awfully specific number. And there were two scenarios I knew of where the SQL Server cardinality estimator immediately used that value – table variables and multi-statement table value functions with SQL 2016 compatibility or earlier or as part of an APPLY where interleaved execution doesn’t apply instead of a JOIN.

The statement in question didn’t use either so what was the issue?

Read on for the solution.

Comments closed

Vlad Drumea is looking for a string:

This post contains a script that I’ve written and used a few times when I’ve needed to search for a string in an entire SQL Server database.

You can find more of my scripts by checking out the SQL Server Scripts category of my blog.

As a warning, this is definitely going to be slow on larger databases. But for smaller databases, it can definitely be handy.

Comments closed

Kevin Wilkie continues a Quixotic quest. Part 1 sets up the story:

Now we get to work with a set of numbers that our elf played and a set of winning numbers for each card. This sounds like something that SQL is meant for!

As always, we load the fun input data provided by the AoC group into our database. I actually loaded it in as 2 columns – Card and String. I’m just not a fan of throwing all of our data into one column of a table and letting it all get sorted out later.

Part 2 wraps it up:

With part 1, we just had to figure out how many times each of the winners showed up. With part 2 though, we have to jump through several hoops since we now have to determine how many cards we will end up with if we win the next series of cards for every match.

I slimmed down the table that I’m working with to only 2 columns – yes, in the real world, I would have used a view, but today was not that day.

By the way, as soon as I saw OverlyLongNamesThatNoOneCanTypeWithoutUsingIntellisense I wondered when Kevin got access to my code base. I have, on a few occasions, created punishment names, names so long that they punished the people who had to type or track them. In fairness, it wasn’t just a fit of pique, though pique was a common factor in all of those situations.

Comments closed

Erik Darling doesn’t update all the rows at once like some barbarian:

The goal of batching modifications is to accomplish large numbers of row modifications with exacerbating locking problems, and being kinder to your server’s transaction log.

There is generally an appreciable difference in transaction time in modifying 1,000 rows and modifying 10,000,000 rows. Go try it, if you don’t believe me.

Click through for more information on good ways to set up batching, including the use of the highly underrated OUTPUT ... INTO ... clause on DML statements.

Comments closed

Kevin Wilkie lives the struggle. Check out part one:

Then we do something very similar to what we did in Walking Through the Advent of Code Day 2 – find the digits, and the remaining text, and we’re home free!

Notice, though, that the position we grab is found at the very end. We just find the string in the line and show where it is. Except, that doesn’t always work.

And then there’s part 2:

Hopefully, by now, you’ve read my post Walking Through the Advent of Code Day 3. After looking through it, you’re probably thinking… “What have I gotten myself into? Is SQL the way to go with all of this? Can I back out now and learn a programming language that AoC can be done in “easily”?”

Click through to experience the pain or at least have a little bit of Schadenfreude in your life.

Comments closed