Press "Enter" to skip to content

Day: April 19, 2024

Branch Cleanup with git prune

Published 2024-04-19 by Kevin Feasel

Steve Jones breaks out the branch cutters:

As I’ve been working with SQL Saturday and managing changes to events, I’ve accumulated a lot of branches. Even though I’m a solo developer, I decided to use branches, as I expect others to share this load in the future. This post looks at how to start cleaning those up.

As a team gets larger, the necessity of regular branch maintenance increases, but so does the complexity of it: it’s really easy to have one person not pay attention to the e-mails and get burned when old branch deletion en masse does happen.

Leave a Comment

Database Subetting and Data Generation

Published 2024-04-19 by Kevin Feasel

Phil Factor tells us about two possibilities for loading a lower environment:

When dealing with the development, testing and releasing of new versions of an existing production database, developers like to use their existing production data. In doing so, the development team will be hit with the difficulties of managing and accommodating the large amount of storage used by a typical production database. It’s not a new problem because the practical storage capacity has grown over the years in line with our ingenuity in finding ways of using it.

To deal with using production data for testing, we generally want to reduce its size by extracting a subset of the entities from a ‘production’ database, anonymized and with referential integrity intact. We then deliver this subset to the various development environments.

Phil gets into some detail on the process behind subsetting and then covers data generation as an alternative.

Leave a Comment

Using Powershell to Set the SQL Server Port Static

Published 2024-04-19 by Kevin Feasel

Vlad Drumea doesn’t want a dynamic port number:

This post demos a script that I’ve put together to automate the configuration of the static TCP port for a SQL Server instance using PowerShell.

The script is derived from another PowerShell script that I’ve written to help spin up SQL Server test instances in my home lab.

This should be helpful you’re working in a restrictive environment where you can’t install additional PowerShell modules, and you couldn’t take advantage of dbatools’ Set-DbaTcpPort.

Click through for the script, but also use dbatools whenever you can because it’s a good product and I haven’t done any unpaid shilling for them in far too long.

Leave a Comment

The Equivalency of Views and Common Table Expressions

Published 2024-04-19 by Kevin Feasel

Erik Darling makes a comparison:

Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.

Think of it like this:

  • Views are like a permanent home
  • Common table expressions are like a mobile home

You can put equally terrible queries in either one and expect equally terrible results.

Read the whole thing for additional spicy analogies and similes.

I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…

Leave a Comment

Processes in PostgreSQL

Published 2024-04-19 by Kevin Feasel

Semab Tariq continues a series on internals in PostgreSQL:

PostgreSQL is a client/server type relational database management system. It has a multi-process architecture that runs on a single host. A collection of multiple processes that manage a database cluster is usually referred to as a PostgreSQL server. In PostgreSQL, every operation is treated as a process, and each action we undertake within PostgreSQL follows an append-only approach. This means that every time we execute an action such as an insert, update, or delete, a new tuple is created rather than modifying the existing data directly.

Consequently, PostgreSQL does not execute updates or deletes in place. Instead, it appends new data or marks existing data as obsolete. This append-only methodology ensures data integrity and allows for efficient management of database changes over time.

Read on to learn more about how these processes work.

Leave a Comment