Press "Enter" to skip to content

Category: T-SQL Tuesday

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

A Story of Bad Data Modeling

Published 2023-07-12 by Kevin Feasel

Kendra Little unravels a puzzle:

I double-checked my queries. Had I goofed in my sql? Nope. Next, I looked into if some of the data was in an inconsistent state.

What I found was worse than what I’d imagined. As a data person, it made me feel sad and icky.

That’s because it’s usually not too hard to clean up bad data. It’s almost always much harder to fix a badly designed data model which is already established in production.

Read on for a tale as old as time: the clarion call of expediency now causing pain later.

Comments closed

Getting Last-Used Report Parameters for SSRS

Published 2023-04-14 by Kevin Feasel

Brian Bønk shares some history:

A couple of years back I needed to find the latest used parameters from a reporting services – SSRS (yes, it is old!) report. The functionality was to find the latest used parameters for all parameters dynamically and use them as the default values for each parameter in the report, when tjhe user opened the report. If the uesr had not used the reprot before, it should ask for the parameters.

This was a alot of fun for me to do, as I needed to query the internal tables from the reporting services database. The tables ExecutionLogStorage and the Catalog was on play. This due to the fact that I needed the active user’s latest used parameter values from each specific report and this in runtime when the report was opened.

Click through to see how Brian did it.

Comments closed

Building a Wordle Solver in Powershell and T-SQL

Published 2023-04-12 by Kevin Feasel

Shane O’Neill takes this T-SQL Tuesday to heart:

I got swept up in that wave for a while in the same way I got swept up in the other trends of my time, like Pokemon, Sodoku, and Angry Birds.

Eventually, I stopped when I found a PowerShell script by Kieran Walsh ( github | twitter ) where you could narrow down to the correct answer by putting in the results of your guess each round.

This hack led to me realising how much time I was spending on Wordle and that I should stop, much like I did with Pokemon, Sodoku, and Angry Birds.

So, what better thing to do than to try and recreate that PowerShell script in T-SQL

And given Shane’s time restriction, I’d say the end result is a good one.

Comments closed

Stuffing Characters with STUFF

Published 2023-04-12 by Kevin Feasel

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 closed

T-SQL and Fun Puzzles

Published 2023-04-11 by Kevin Feasel

Rob Farley puzzles it out:

Back 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 closed

The Power of Date Truncation

Published 2023-02-14 by Kevin Feasel

Magda Bronowska rounds to the nearest minute:

From MS Learn:

DATETRUNC() function returns an input date truncated to a specified datepart.

On the surface the work similarly to DATEPART(), however that function returns integer values, opposed to the dates returned by DATETRUNC() (we will see that better in the example below).

Read on for plenty of examples of this, as well as two more syntax updates in SQL Server 2022.

Comments closed

The Benefit of IS DISTINCT FROM

Published 2023-02-14 by Kevin Feasel

Rob Farley enjoys the syntax:

This month, Deepthi Goguri (@dbanuggets) asks us about our favourite new feature in SQL Server 2022 or Azure. And while there are always a few, I’m going to write about why I have a particular fondness of “IS [NOT] DISTINCT FROM“, despite the fact that it’s overly wordy and the functionality isn’t actually new at all.

People understand my point that it’s a little wordy. Typing “IS NOT DISTINCT FROM” instead of “=” doesn’t sound fun to anyone, and I think “==” or “IS” ought to be fine. The fact that the functionality isn’t new… well that statement seems to raise a few eyebrows.

Read on for Rob’s take on what IS DISTINCT FROM (and its negative cousin) actually do and what performance-killing alternative people used prior to that.

Comments closed