The Bit Bucket

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

Like most developers, I often need to check if a string value is a valid number or a valid date (or datetime). In T-SQL, the functions provided for this are ISNUMERIC and ISDATE. These are pretty generic functions though, and I almost never use them now. I believe you’re now much better off using TRY_CAST instead.

“Numeric” is a fairly vague concept. Which type of number are we checking for?

2021-02-23

I mentioned in a previous post that COUNT was an aggregate. The other common aggregates are shown in this table, and no surprise what they do.

SUM adds up or totals the values.

AVG calculates the average of the values.

MIN works out the minimum value.

MAX works out the maximum value.

But if you’ve started to think about how SQL Server works, you might be wondering about what happens with NULLs.

2021-02-22

A while back, I got a request to add a new function to our free SDU Tools for developers and DBAs. The user wanted to be able to find a particular day of the week, in a given week. So for example, if I give you a date of 30th April 2021, what is the Thursday in that week? So we’ve added a new function WeekdayOfSameWeek.

It takes two parameters:

2021-02-19

If you haven’t already heard, there’s a global Power BI summit coming up. It’s 19th to 23rd April and details are here:

https://globalpowerbisummit.com/

I’m presenting a session “Enterprise Power BI: Do I still need a data warehouse?”

It’s being replayed across different time zones and each time though, I’ll be there for a live Q&A. Would love to see you there.

2021-02-18

I’ve mentioned before that I have a deep interest in artificial intelligence and how it will change the world. In particular, I’m interested in the effects on jobs. So I was pleased to get to listen to another book covering this recently. It was Kevin Scott (from Microsoft)’s Reprogramming the American Dream: From Rural America to Silicon Valley - Making AI Serve Us All.

Kevin is Microsoft’s Chief Technology Officer, so I’ve come across him before. Prior to Microsoft, he was a senior vice president at LinkedIn and came across to Microsoft as part of the LinkedIn purchase.

2021-02-17