Press "Enter" to skip to content

Category: T-SQL

The Equivalency of Views and Common Table Expressions

Published 2024-04-19 by Kevin Feasel

Erik Darling makes a comparison:

Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.

Think of it like this:

  • Views are like a permanent home
  • Common table expressions are like a mobile home

You can put equally terrible queries in either one and expect equally terrible results.

Read the whole thing for additional spicy analogies and similes.

I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…

Comments closed

Data Analysis with Window Functions

Published 2024-04-18 by Kevin Feasel

Erika Balla looks out the window:

Window functions are an advanced feature of SQL that provides powerful tools for detailed data analysis and manipulation without grouping data into single output rows, which is common in aggregate functions. These functions operate on a set of rows and return a value for each row based on the calculation against the set.

In this article, we delve into window functions in SQL Server. You will learn how to apply various window functions, including moving averages, ranking, and cumulative sums, to achieve comprehensive analytics on data sets. 

Click through for several examples.

Comments closed

Snake Draft Ordering in SQL Server

Published 2024-04-15 by Kevin Feasel

Aaron Bertrand demands order:

I play fantasy football. To start each season, there is a draft, where players are randomly assigned draft order. We’ve started some seasons with a “snake” draft, to make the selection of players most fair and balanced. A snake draft works like this (a simple example with four players):

  • First pick in the first round picks last in the second round.
  • Second pick in the first round picks third in the second round.
  • Third pick in the first round picks second in the second round.
  • Last pick in the first round picks first in the second round.
  • Then, in the third round, it reverts to the original order: The first pick from the first round picks first again (giving them two picks back-to-back).

Read on to see how to implement this in T-SQL. I’m happy to report that I anticipated the answer, though Aaron notes a couple of issues that I didn’t catch in my “compile the code in my head” approach.

Comments closed

Returning a Row when there’s No Row to Return

Published 2024-04-11 by Kevin Feasel

Erik Darling has an existential dillema:

Rather selfishly, I do this for my stored procedures, for all the reasons in the first sentence. Especially when debugging stored procedures, you’ll want to know where things potentially went wrong.

In this post, I’m going to walk through a couple different ways that I use to do this. One when you’re storing intermediate results in a temporary object, and one when you’re just using a single query.

Read on for an example of how to do this.

Comments closed

A Review of Powerops

Published 2024-04-05 by Kevin Feasel

Nikola Ilic reviews a new tool:

In the last 2-3 months, I had an opportunity to try and test the new external tool, called Powerops. This is an external tool developed by Truviz, the company you might already know as the creators of various Power BI “goodies”.

Now, you are probably expecting “another Power BI external tool that solves a specific challenge”…I’ll tell you straight away – you can’t be more wrong! Powerops is a mix of various existing external tools – think of DAX Studio, Tabular Editor, ALM Toolkit, Measure Killer, etc. – but it’s also more than a simple sum of the features of the aforementioned tools.

Read on for the full review. Pricing is free for the first two months, and then either buy a license or you can upload up to two files per month and remain in the free tier.

Comments closed

Finding Row Counts in SQL Server

Published 2024-04-03 by Kevin Feasel

Kevin Wilkie breaks out the abacus:

Today, I was working with SQL Server to get row counts from several tables so I thought I’d be smart and work with some functions in SQL Server to make it smarter / easier.

Now, if I am truly only getting “straight” row counts from these tables, I would be able to create a query like the below that would provide the answers with no problem:

Read on for the normal approach, as well as a more complicated approach made necessary due to some business logic requirements.

Comments closed

Dealing with Parameter Sniffing using Multiple Execution Plans

Published 2024-04-03 by Kevin Feasel

Andy Brownsword deals with statistical skew in the data:

Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query.

Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we can use Dynamic SQL to solve this challenge. Here we’ll demonstrate one technique: Comment Injection.

My one note about a good post (other than, you should read it) is that parameter sniffing is not itself a bad thing. 95%+ of the time, it’s a great thing. It’s that last 5% or so that give it a bad name.

Comments closed

Working with TRY-CATCH in SQL Server

Published 2024-03-28 by Kevin Feasel

Steve Jones gives it the ol’ college try:

This is a common error handling technique in other languages. C# uses it, as does Java, while Python has TRY EXCEPT. There are other examples, but these are good habits to get into when you don’t know how code will behave or if there is something in your data or environment that could cause an issue.

In SQL, I think many of us get used to writing one statement in a query and forget to do error handling, or transactions. However, this can be a good habit as your code might grow and people might add more statements that should execute.

Read on for a few examples of how to use SQL Server’s TRY-CATCH functionality. It’s not perfect, but as Steve shows, there are definitely good uses for it.

Comments closed

Working with INTERSECT and EXCEPT

Published 2024-03-28 by Kevin Feasel

Erik Darling wounds me:

I have never once seen anyone use these. The most glaring issue with them is that unlike a lot of other directives in SQL, these ones just don’t do a good job of telling you what they do, and their behavior is sort of weird.

Unlike EXISTS and NOT EXISTS, which state their case very plainly, as do UNION and UNION ALL, figuring these out is not the most straightforward thing. Especially since INTERSECT has operator precedence rules that many other directives do not.

I’ve used EXCEPT to check if two datasets are equivalent for testing purposes: A EXCEPT B should be zero rows, and B EXCEPT A should be zero rows. It has built-in handling of any NULL madness. Set intersections have their uses as well.

Comments closed

UNION and UNION ALL

Published 2024-03-27 by Kevin Feasel

Erik Darling hires the Pinkertons:

UNION and UNION ALL seem to get used with the same lack of discretion and testing as several other things in T-SQL: CTEs vs temp tables, temp tables vs. table variables, etc.

There are many times I’ve seen developers use UNION when result sets have no chance of being non-unique anyway, and many times I’ve seen them use UNION ALL when there would be a great benefit to discarding unnecessary duplicates.

Erik’s explanation goes about three steps beyond “UNION is bad so always use UNION ALL.” It’s a must-read for anybody who regularly writes T-SQL queries.

Comments closed