The Lesser Used Functions

Posted on January 10, 2025 by way0utwest

Recently, I reviewed an article that examined the bitwise functions that were added to T-SQL in SQL Server 2022. As I was looking over the article, I started to wonder if anyone was using these in production code. I used to do bitwise work early in my programming career, when memory and space were tight. However, it always felt like I was hiding some information that a subsequent developer (or my future self), might easily miss.

I looked through some other changes to the T-SQL language in the last few versions and made a list. This week I wonder if any of you use these functions in production code?

There are some interesting changes in here, and I can see the use for these functions, but I suspect these are specialized functions built for specific situations (or customers). I don’t expect many people to use them outside of those situations, but maybe I’m wrong. Perhaps some of you like doing bitwise operations, you like the logical CHOOSE/IIF, or maybe you can stomach approximate calculations.

I’m glad that the T-SQL language continues to grow. I would like to see more changes that ease the development of database code, though I would like Microsoft to ensure these new functions perform well. Some of the changes added in the past haven’t done so, especially when a dotNet function is wrapped with T-SQL. Performance matters and many of these functions will be called in queries that need to compete a result with thousands of rows.

Let us know today if you’ve found these functions useful in your work, or if there are changes made in the past that you would like to see improved.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on The Lesser Used Functions

A New Word: Querinous

Posted on January 10, 2025 by way0utwest

querinous – adj. longing for a sense of certainty in a relationship; wishing there were some way to know ahead of time whether this is the person you’re going to wake up next to for twenty thousand mornings in a row, instead of having to count them out one by one, quietly hoping your streak continues

I hope most of you don’t have querinous for long in your relationships, and certainly none of it if you’re married or otherwise committed. As I watch my kids date, and I think back to my own time as a young man looking for love, I remember this feeling. I remember wondering if new relationships were moving forward or was I missing something because of an infatuation.

We all wish we would see the future, in many ways, but certainly in relationships. I think a querinous feeling is inevitable as you begin a relationship and get to know someone else.

From the Dictionary of Obscure Sorrows

Posted in Blog | Tagged , | Comments Off on A New Word: Querinous

Adding the Right Value

Posted on January 8, 2025 by way0utwest

I caught this post about a career presentation from Kendra Little and it resonated a bit with me. The summary of the post (it’s a bit long) is that there is valuable work that supports and benefits a team of people, called glue work. However, glue work isn’t necessarily technical and it isn’t necessarily recognized as valuable by management. In fact, it might unintentionally lead you from a career in engineering to one in project management.

I haven’t been someone who has been marginalized in positions, who has been taken for granted or expected to do certain tasks. I think some people get taken advantage of, and this is especially true for women, who both volunteer more and are volunteered by others more for these tasks. While I have been quick to document decisions or actions, and sometimes help out with project work, I do that in addition to any technical work. I’ve felt that as a developer, my job is to produce code. If I can help with other things, then I’m a Developer+, but I ultimately know that code my clients can use is what matters.

I think that people sometimes mistake valuable work for the team with valuable work for the people looking at the team. They know teams fall apart because someone doesn’t do the glue work. Or they have a s*** project manager who isn’t managing the project. I think that is very common, and because many of us have an “I need to succeed” mentality, we sometimes stop coding to pick up other tasks, essentially volunteering to be the pseudo-project manager or team manager. That’s fine, but if you fill your days with non-coding tasks, you are not moving toward senior engineer status.

At the same time, I know that many of you don’t want to work long hours, nor do you want to let tasks drop that need to be handled. There’s tension there, as either the employee feels overworked and taken advantage of, or they work themselves into burnout.

Sometimes there isn’t a choice and your boss might expect you to work extra hours on tasks adjacent to or related to your work. I have seen a few people work for one of the big-name tech companies where employees had tasks outside of their normal work that they were expected to complete outside of their core 40 hours. If you have a boss like that, I’d look for a new position. That’s my advice. I’m happy to work extra hours in limited doses, but not on a regular basis.

I’d say the same thing if your fellow employees don’t step in and do their share of the load of glue work. If we’re a team, we all do those things. If we don’t, we’re not a team, and I don’t like working in places where we aren’t a team.

Whatever you decide to do, make sure that you are clear on what is expected of you to get that raise/bonus/promotion and focus most of your efforts there. It doesn’t matter what you think is important; it matters what those who make decisions about your career think is important.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on Adding the Right Value

Using Flyway Prepare for State-Based Deployments

Posted on January 8, 2025 by way0utwest

One of the neat enhancements made to Flyway was the addition of state-based workflows and tooling. A lot of people have loved SQL Compare or SQL Source Control for deployments. As I’ve worked with customers, they’ve asked for this, and the v11 version of Flyway includes this for the Enterprise edition.

This post will look at using these two commands to deploy a change to a database from the command line instead of Flyway Desktop.

If you want to know more about Flyway, all my posts are together in one feed.

My Current Environment

I have Flway v11.0 installed and I have two databases: FWState_1_Dev and FWState_3_QA. These two databases are in my flyway.toml file, where I see them configured as development and qa.

2024-12_0208

I also created a “deployments” folder under my project, which is where I want deployment scripts. I’ll do this manually, but in a pipeline, I’d do this in an automated fashion. As you can see, this is an empty folder.

2024-12_0209

In Flyway Desktop (FWD), you can see that I have a change made in my dev db that’s ready to deploy. I could click “deploy” and run this from FWD, but I want to experiment with automation here, so I’m practicing at the command line.

2024-12_0203

I can verify this change isn’t deployed by checking the database.

2024-12_0210

Let’s get started.

Flyway Prepare

The prepare command is designed to create the deployment script. It can use a variety of sources, but in this case, I’ll use the schema model that I’ve saved in my git repo. This is where I (in general) want to pull from, using PRs and branches to manage work. In this case, I’ll pull from main, where I’ve got that change.

To get my changes, I’ll use a few parameters with Flyway. One thing to note, each of these parameters includes a namespace. I think this will go away at some point, but it’s good to be explicit in any case as we have lots of “source” parameters in different namespaces.

Second, use a single hyphen. That somewhat offends my unix/linux’y self, as the parameter names are more than one character. I’m used to 2 hyphens, which generate an error in Flyway.

2024-12_0211

The basic parameters I’m using are:

  • prepare.source – the location of my source. In this case, the schemaModel defined in my flyway.toml file as “schema-model” in the file system.
  • prepare-target – the target to look at to build the script. For me this is an environment preconfigured (see above) as qa, so “env:qa”
  • prepare.scriptFilename – the default is D__deployment.sql, but I want it in a subfolder, so I’ll explicitly put this in.

My CLI call is this:

flyway prepare -prepare.source=schemaModel -prepare.target=”env:qa” -prepare.scriptFilename=”deployments\FWState__deployment.sql”

When I run this in my project folder, I see these results:

2024-12_0212

If I look in my deployments folder, I see the file:

2024-12_0213

The contents of the file are here:

2024-12_0214

This looks like what I’d get in FWD if I clicked “Deploy”.

2024-12_0204

This step just created the file. I need another call to deploy this.

Flyway Deploy

The Flyway Deploy command will execute a deployment script against a target. The idea here is that one script is created from a state-based project and deployed with this command.

As with prepare, I’ll use a few parameters here. The ones I need are:

  • scriptFilename – the file to deploy. I’ll use the same one as above.
  • environment – the name of the environment where I am deploying, in this case, no need for the prefix.
  • executeInTransaction – this should default to true, but I’ll be explicit as I might change this in different platforms (postgreSQL, Oracle, etc.) and so if someone copies this pipeline, I want this to be clear.

This gives me this command:

flyway deploy -scriptFilename=”deployments\FWState__deployment.sql” -environment=qa -executeInTransaction=true

When I run this, it works.

2024-12_0215

I can verify this in SSMS.

2024-12_0216

I’ve deployed changes, and if I look at the FWD deploy tab, I see no changes.

2024-12_0217

I could put these two commands in a pipeline and have them work in an automated fashion.

If you do that, use variables in your pipeline, or ensure people commit (and merge) changes to the flyway.toml file that is used for the various options.

Summary

This post showed a quick way to start automated state-based deployments in Flyway, using the new prepare and deploy commands. A few options were used to control these deployments from a command line.

There are more options that you can include to control deployments and I’ll look at some other ways of doing this in the future. For now, this gets my deployments working easily.

Flyway is an incredible way of deploying changes from one database to another, and now includes both migration-based and state-based deployments. You get the flexibility you need to control database changes in your environment. If you’ve never used it, give it a try today.

Posted in Blog | Tagged , , | 1 Comment