The Bit Bucket

It’s unfortunate that the SELECT query in SQL isn’t written in the order that operations logically occur (if not physically). I suspect that’s one of the things that makes learning SQL a bit harder than it needs to be.

Without getting into really complex queries, you need to understand the logical order of the operations.

FROM

The starting point is to determine where the data is coming from. This is normally a table but it could be other sets of rows like views or table expressions.

2021-03-09

I’ve previously talked about how the WHERE clause is used to limit the rows that are included in a query. If we’re using a GROUP BY, then WHERE is determining what goes into the grouping. But what if you want to apply a limit that’s based on the outcome of the grouping? That’s what HAVING does.

If I execute the following query:

SELECT Size, 
       COUNT(ProductID) AS NumberOfProducts
FROM dbo.Products 
WHERE IsShownOnPriceList <> 0
GROUP BY Size;

I see this output:

2021-03-08

I recently wrote about the ALL operator in T-SQL. It’s used to check how a given value compares to a list of values. That list generally comes from a sub-query that returns a single column. While the ALL operator isn’t well known, there are also other related operators SOME and ANY.

SOME and ANY are synonyms. You can use either.

Instead of returning whether or not all the values in a list meet the logical operation, these return whether any of the values in the list meet the requirement.

2021-03-05

I was answering a question in a forum the other day. I was asked if SQL Server and T-SQL had the ALL operator. It does, yet few people seem to either know about it or how to use it.

The basic idea of the ALL operator is to allow you to compare a single value to a set of values, using a logical operation. For example, in the query below:

2021-03-04

I tend to read a large number of entrepreneur-related books. Some are better than others. I almost didn’t get a copy of  Passive Income: How to Make Money from Home, Skyrocket your Income at Lightning Speed as the title pretty much put me off as it sounded so cheesy, but for some reason I did listen to the audiobook.

I also don’t normally write reviews for books that I really don’t like. I didn’t like this book.

2021-03-03

When I’m building Azure Data Factory pipelines, I often want to rescale, i.e. change the Service Level Objective (SLO) of Azure SQL Databases before and after processing. For example: I might have a database that sits at S0 or S1 all day long when not being processed (to allows for the odd adhoc query), but I want it at S6 to do overnight ingestion of data and loading of analytic data models. Then I want it to go back to what it was before.

2021-03-02

When you calculate an aggregate, the default is that it applies to the entire table, but you might not want that.

For example, I might want to calculate the longest shelf life for products. But I want to calculate that for each size of product.

If we look at the first example, we have added a GROUP BY clause to our query, and it returns exactly that. The output is on the left hand side below the query. For each size (determined by the GROUP BY), the maximum of the shelf life is returned.

2021-03-01

I was pleased to get sent a copy of Benjamin Nevarez’s new book High Performance SQL Server. I’ve known Benjamin for a long time. He’s a very skilled SQL Server professional, and you’ll see him at conferences around the world. (Or at least once Covid is tackled more completely).

And the technical reviewer for the book is another very skilled old friend in Mark Broadbent. So my expectations were high for the new edition of this book.

2021-02-26

I had a question from Dale Kerr the other day about whether we had a tool in our SDU Tools collection, that calculated the day of the week (i.e. Tuesday, Thursday) for a given day of the year, across a range of years.

We don’t have a specific tool for that, but the a CTE returning the list of years makes it easy.

(UPDATE: SDU Tools version 21 will have WeekdayAcrossYears that implements this)

2021-02-25

Richard Dawkins is a controversial figure. I’ve got some mixed opinions on him. On one hand, I suspect that in a hundred year’s time, The God Delusion will be regarded as a seminal piece of writing. On the other hand, I’ve seen how some of my religious friends find him abrasive. Most of the time when I see this though, what I suspect much of the criticism of him comes from, is that people just don’t like having their long term beliefs challenged. (They would also say, at times, ridiculed). That’s not surprising. Other times though, even his supporters think he gets too strident in what he says and how he describes things.

2021-02-24