Seasonal Clock Changes and SQL Server
In this post we’ll look at the behaviour of a SQL Server application that needs to deal with events that are logged over the period of a UK clock change where the clocks change from GMT (Greenwich Mean Time) to BST (British Summer Time). The Problem Let’s say we have an events table with a…
Keep reading
Upgrading PostgreSQL on Debian Linux with pg_upgradecluster
In my previous post, we upgraded a PostgreSQL 15 cluster to version 18 using pg_upgrade. In this post, we’ll contrast that with using pg_upgradecluster. As before, the upgrade process is: Note that we have one less step than the pg_upgrade version, that step being creating the new cluster. The environment is the same as before:…
Keep reading
Using Files in UNC Paths with PowerShell in SQL Server Agent
In this post, we’ll look at how PowerShell job steps may have issues connecting to network locations from within SQL Server Agent job steps. Timeless Backup Job When working with SQL Server backups, I bet we’ve all seen agent jobs that look like this: It’s a classic backup job – run the backup using T-SQL…
Keep reading
Upgrading PostgreSQL on Linux with pg_upgrade
In this post, we’ll look at how to upgrade a PostgreSQL installation on Linux. In this example, we’ll look at an in-place upgrade on Debian from PostgreSQL version 15 to 18. The version 15 cluster will already be up and running and we’re going to use pg_upgrade to do it. The upgrade process is: The…
Keep reading
Left join pitfalls – LEFT JOIN + INNER JOIN = INNER JOIN
This post is a quick public service announcement to serve as a reminder to be careful with joins in SQL, particularly in queries with a large number of INNER and LEFT JOINs. An INNER JOIN onto a LEFT JOIN will effectively make that LEFT JOIN an INNER JOIN. Example The test query below is against…
Keep reading
Optimizing Window Functions in Derived Datasets
In this post, we’ll look at some interesting behaviour I’ve stumbled across with window functions and derived datasets in SQL Server. I came across a query in a workload that was looking to get the most recent thing per group, a representative example in the Stack Overflow 2010 Database is below. The database is provided…
Keep reading
How Many MR2s still on the road – MongoDB Version
In my previous blog post, How Many Mk1 Toyota MR2s are still on the road, or currently being restored in the UK? we looked at how I used the UK’s MOT History API to download all the MOT data since 2005 and how I crudely filtered that down to just Toyota Mk1 MR2s and performed…
Keep reading
How Many Mk1 Toyota MR2s are still on the road, or currently being restored in the UK? – Answers in Data
In this blog post we look at a small project I was involved in to analyse publicly available UK MOT data to answer the above question (and more!) I am a huge fan of Toyota MR2 Mk1s and at the time of this project (early 2025), owned one myself. Being a fairly active member of…
Keep reading
Filtered Indexes and Plan Parameterization
In this post, we’ll look at how filtered indexes in SQL Server work when the query is parameterised on the column of the filtered index. The query The following query in the Stack Overflow 2010 Database, provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, will find all users with the name John whose…
Keep reading
Installing MongoDB on Debian
In preparation for an upcoming project (stay tuned for more details), I needed to set up a MongoDB database server. In this post, we’ll walk through how to install and configure MongoDB on Debian 12. At the time of writing, Debian 13 is the most up to date stable version of Debian, though MongoDB is…
Keep reading