The Bit Bucket

The simplest way to join two tables is what’s called a cross-join. So this is where I say from products, cross-join, sales territories.

SELECT *
FROM dbo.Products
CROSS JOIN dbo.SalesTerritories;

Now what that will do though is it will give me every combination of rows from the two tables. So I’ll get one row from the second table for each row in the first table. This is often called a Cartesian product of the two tables and it can quickly end up generating a very large number of rows.

2025-01-28

Most programming languages have some way of forming loops. Now some of them have a variety. For example, a language like Basic or Excel had a For Next loop, a Do While loop, a While Until loop, etc.

In T-SQL, while you could conceivably construct loops manually by using a GOTO statement, no-one does that and it would be considered a bad idea.

Instead, we form all the loops we need by using a WHILE statement. It can be used to create all the variety of loops mentioned above.

2025-01-27

In general, we try to avoid procedural logic when writing T-SQL. However, it is possible.

The most basic procedural statement is the IF statement. It allows us to apply basic conditional logic. Instead of keywords like CASE that allow you to apply logic to determine values, the IF statement allows you to decide which statements are executed.

IF @Value > 10 PRINT 'Large';

The condition can also include other clauses linked with AND and OR:

2025-01-26

It’s been another big week for Down Under podcasts. I really enjoyed recording another new Cosmos Down Under podcast this afternoon. It’s now edited and released.

Show 12 features Farah Abdou.

Farah is a Machine Learning Engineer and researcher in Quantum Reinforcement Learning (QRL) with a proven track record of developing AI solutions that address social and environmental challenges.

She has excelled in various roles, including teaching, engineering, and project management, contributing to impactful projects in natural language processing, computer vision, and database systems.

2025-01-25

As well as the variables that you declare, SQL Server has a number of built-in system variables and some built-in system functions.

The system variables are the ones with @@ at the start of their name. Here is an example that’s often used:

SELECT @@VERSION;

It is documented here.

On my SQL Server 2022 system, the value returned is this:

Now while that is a reasonable example of a system variable that has been used for a long time, note that it’s a bit messy as there is a lot of information in a single returned value. There are now better ways to get at the system version in a more useful way:

2025-01-25

I recently watched a YouTube video with Ali Abdaal that talked about productivity. Not sure what it was exactly, but I really liked listening to Ali. So I was pleased to find that his book Feel-Good Productivity was available on Audible.

I really liked this book. I found Ali great to listen to and compelling in his story. It’s another one where I really also appreciate that he narrated the book himself.

2025-01-24

Variables in T-SQL work very much the way they do in other languages. A variable really is nothing more than a name given to a memory location. In T-SQL, variable names must start with an @ sign. An example would be:

@CustomerName

As well as defining a name, variables have other properties.

The first is that we have a data type. It defines the types of things, like strings or numbers and so on, that can we store in that location.

2025-01-24

If ever there was a blog post that I didn’t ever expect to be writing it’s this one.

For such a long time, Notepad has been such a simple and stable application. That no longer seems to be the case.

Symptoms

Recently I had a situation where Notepad just didn’t work properly. I saw these things:

• If I double-clicked a .txt file, it no longer opened. What would happen instead, is that Notepad started, then reported that it could not find the file that I’d just double-clicked.
• In Windows Explorer, wherever I expected to see a text file icon like the one in the image above, what I saw was a simple rectangle (white with a black border).
• If I opened Notepad first, then used File>Open to go to the same files, they opened ok.

It was all rather frustrating.

2025-01-17

I’m a long-term fan of Annabel Crab and her work. So I was pleased to get a chance to listen to a book she wrote a while back, called The Wife Drought: Why Women Need Wives and Men Need Lives. It didn’t disappoint.

There was an old adage that behind every successful man, there was an awesome woman. That’s a view that needs updated wording, but it’s true that the people who really get ahead in the world, have a supportive partner who fits the role that wives traditionally did. And still today, that’s mostly still wives in that role not husbands, even though that proportion is slowly changing.

2025-01-08

A while back, I received another book from my friends at PackT. It was A Developer’s Guide to Cloud Apps Using Microsoft Azure by Hamida Rebai Trabelsi.  I have been meaning to get a chance to read it, and I finally did.

I don’t know Hamida. I think I need to add her to my LinkedIn contacts though, as she clearly has similar interests in many areas. Hamida is a fellow MVP from Tunisia and currently working in Canada. Her MVP award was for developer technologies.

2024-12-16