In SQL Server 2000 and earlier versions, I often heard people ask “How do I output a row number beside each row that’s output in my query?”
I remember some people arguing that it wasn’t a valid request, as it didn’t feel “set-based” but it was an appropriate request, and it could be dealt with in a set-based manner. Sometimes it’s very, very useful to be able to do that.
2021-03-15
There’s a café in Melbourne that I’ve liked going to for a while. It’s not far from where I live when I’m in Melbourne, and it has all the makings of a really nice cosy suburban café. But there’s something missing.
When I sat there the other day, my meal came as ordered but it was just a shadow of what it normally looks like. I put that down perhaps to a different chef that day. It’s not great that they haven’t all agreed on what those meals are usually made like, but that could have been a one off thing.
2021-03-12
In the last few weeks, I’ve seen a surprising number of database scripts that deploy procedures and functions by dropping the object if it exists, then recreating it. Please don’t write scripts like this.
When you drop a procedure, you not only drop the procedure, but you drop all permissions associated with it. If you then create the procedure again, you’ll have the procedure, but you won’t have the permissions.
2021-03-11
Over the years, I’ve had a number of friends tell me that they really enjoyed Starting Strength by Mark Rippetoe, and illustrated by Jason Kelly. Many regard it as one of the best and most useful fitness books ever written. So I had to read it, even though some of its strongest supporters (pun intended) revered it, almost to some sort of cult status.
What an interesting book.
As I age, I realise the importance of mobility and core strength. I just wish I’d put more importance on it during my life.
2021-03-10
I’m a big fan of Azure Data Factory (ADF), but one of the things you need to get used to with tools like this, is that the UI keeps changing over time. That makes it hard for several reasons:
I think Microsoft don’t quite get how much blog posts, etc. supplement their own documentation.
2021-03-09
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.
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