CHOOSE’ing a Beer: #SQLNewBlogger

Posted on March 26, 2025 by way0utwest

We recently published an article on CHOOSE at SQL Server Central. I thought it was a good intro, but as someone noted in the comments, how do you use CHOOSE? Do you have to hard code choices?

This post shows you don’t.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A Scenario

I have a table that contains some data. In this case, about beer. I like beer, and this was a fun little demo. I’m not recreating the DDL because, well, you might like different beers.

2025-03_0145

In any case, this is simple to set up.

If I wanted to choose some data from this table based on an index, I could do something like this. This code populates the first index in choose with beers and the second with brewers. CHOOSE is 1-based indexing.

DECLARE @i INT = 1;
SELECT
   CHOOSE (@i, beername, brewer)
FROM dbo.Beer AS b2;

This returns me the beers.

2025-03_0146

If I changed the value to 2, I get brewers. I show both below.

2025-03_0147

How would I use this? Maybe a user is asking to edit either a home or shipping address. I can index these by returning the column data as index 1 or 2, and linking the user suggestion to the index. They choose home, we pass in 1. If we qualify the query with a WHERE clause to one customer, they get just their data to edit.

I could even do something silly, like getting values from different places. For example, here I’ll use string_split on a value.

DECLARE @i INT = 2;
DECLARE @s VARCHAR(20) = 'Vodka,Tequila,Bourbon'

; WITH a (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 1
),
  b (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 2
),
  c (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 3
) 
SELECT
   CHOOSE (@i, a.value, b.value, c.value)
  FROM a, b, c

This is silly, but it does return an acceptable answer.

2025-03_0148

I don’t know that there are many places that I’d use CHOOSE, but as I play with it, I can see that it could be a handy tool at times with a little creativity.

SQL New Blogger

This post took me about 15 minutes to write after I saw a comment. I set up a scenario and posted a reply, then took that code to structure this post. The STRING_SPLIT piece was the longest, as I had to futz with code, but I show some use of a new feature and how I might incorporate this into an application.

You could write your own creative blog on this, probably in 30 minutes or less. I bet you’d get asked about this in an interview as it’s kind of funny.

Posted in Blog | Tagged , , | Comments Off on CHOOSE’ing a Beer: #SQLNewBlogger

Monday Monitor Tips: Looking Back in Time

Posted on March 24, 2025 by way0utwest

Often we find out about a problem reported by a customer after the incident has passed. This might be from a trouble ticket or even an email that we didn’t see until a period of time has passed.

How can we look back at the activity of a server in the past? This post looks how a DBA can time travel back to a situation that occurred in the past.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

Time Traveling

Let’s imagine I get a ticket that said there was a problem at 2:15am from a user running a process. I didn’t get to this at 2am, but at 9:15am when I receive it, I need to look back at what was happening.

If I pick a server in Redgate Monitor, I’ll see the view below. This is of the staging02 server on monitor.red-gate.com. By default, this shows me the last hour of activity on the server.

2025-03_0085

In the upper right corner, I can see the time frame selected on the left (below) and the amount of time. I’ve selected the drop down, and there are many other choices. I also see the metric time at the top, just in case, I’ve started to mess with other values.

Note: there is a calendar control to the left that can go back to previous days if you don’t want to use the time duration drop down.

2025-03_0086

In this case, let’s jump to the last 12 hours. If I select that, you can see my display changes a bit, zoomed out to show 12 hours not 1. The four charts below haven’t changed, however.

2025-03_0087

Most of the top chart has a darker background, except for a portion at the far right, which has a white background. This white background part is the focus window, and it determines what the 4 graphs below show, as well as the query information and other data.

This is set to 1 hour, but I can expand it. If I drag the box on the left side of this further to the left, I can expand the amount of time shown. If you look below, I’ve expanded this to 7:37am as the start.

2025-03_0088

I can also slide this. I’ll slide this to the left to cover to 2:00am-3:00am part of the graph. Now I see different views below in the four graphs.

2025-03_0089

In this case, I now can focus on the 2:00am issue. I see an annotation that there was a Flyway deployment at 2:00am. You can see the annotation zoomed in with the tooltip when I hover the mouse on this icon.

2025-03_0090

I can scroll down to the query area, and I see the top queries, of which there were just a few.

2025-03_0093

The top one has a lot of duration, and if I expand it, I can see the query history. Note there was a query plan change just after 2:00, when my deployment occurred. The duration went up and then started to slightly drop. I see another plan change at 2:40am, and if I were to look back at the top, I’d see a second deployment from Flyway at that time.

2025-03_0095

I don’t quite know what changed in the deployment, but I’d start looking here to see if this affected my query.

Summary

The focus window in the overview for an instance allows you to set the time frame in which you see data related to that instance. This lets you time travel back to look at the server as it existed in the past. The amount of time you can travel back depends on your data retention settings, which we’ll examine in another tip.

Hopefully this gives you a quick tip on how you can focus your efforts to a relevant period of time when you get an issue to review.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

Posted in Blog | Tagged , , , | 1 Comment

Monoliths and Microservices

Posted on March 24, 2025 by way0utwest

I have run into a lot of people in the last few years that love decoupled software and microservices. It seems many people are aiming to move their work in this direction, and while I see some appeal, I also see tremendous additional complexity that has moved out of the software into the operations and debugging space. As I read the book Observability Engineering, I found myself thinking the complexity of setting up more logging and instrumentation in an observability framework as well as the costs of managing a system start. That caused me to think this is overkill for most software.

To be clear, I don’t think that Uber could have been built as a few monoliths, and there are other examples of such systems, but most of us don’t work at that scale. There are lessons to be learned about large, real-time software systems, and certainly Google, Amazon, Spotify, Netflix, etc. can help us understand how certain techniques work better at scale, but most of us don’t work at that scale.

Scale to me is thousands of connections and terabytes of data. Those companies tend to work at a couple orders of magnitude above that. Those are also companies that must have real time systems up to survive. The vast majority of companies I’ve worked at might suffer a loss with an outage of a system, but honestly, we could survive a day or two of recovery.

Heck, look at all of the companies that have had portions of their digital infrastructure knocked offline from ransomware the last few years. Some failed, but most didn’t. The incident sucked for IT staff, but those companies survived. I still remember the SQL Slammer worm forcing us to take our entire network offline for multiple days at a large software company. We still ran sales, and support, and other systems independently or from paper. Granted that was 20 years ago, but I’m sure Redgate and many other organizations could survive a few weeks with no network.

Uber on the other hand, that would be a disaster for them. They would likely survive, but at a high cost. How many people would jump to a new service and never look back?

I was reading a piece on coupling and complexity, which I’ll discuss a bit in another article, but it got my thinking of how often I see people overcomplicating their work by trying to move to a decoupled world instead of hiring and training people to adopt better architectures and communicate better. After all, moving to microservices isn’t going to avoid the training issue. You’ll still have to teach people more.

And while you’re at it, force every developer to pass a SQL test every year. That might help you build better systems as well. SQL isn’t going away and better SQL coding in will result in much better applications everywhere.

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 Monoliths and Microservices

Friday Flyway Tips: State-based deployment with Flyway

Posted on March 21, 2025 by way0utwest

I was asked about state-based deployments in Flyway, so I decided to show how this can work with a quick demo. This post walks through the process.

I’ve been working with Flyway and Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

A Steady State

I have a Flyway project setup already. I’ve refreshed the screen and as you can see here, there aren’t any differences between my database and my project in version control.

2025-02_0349

In SSMS, I’ll create a new table in my FWState_1_Dev database. This is where my project is pointed in Flyway Desktop (FWD).

2025-02_0350

I’ve created a new table and it exists. If I  check my QA database, FWSTate_3_QA, I don’t see this object.

2025-02_0351

If I refresh my project in Flyway Desktop (FWD), I’ll see my change. I can pick it and see the code, and then I’ll click “Save to project” in the upper right.

2025-03_0106

Once the code is saved, I see the confirmation and I have the code ready for commit to a VCS. I won’t do that here, though you should.

2025-03_0107

What I want to do now is go to the left bar and select “deploy”, which is the rocket icon.

2025-03_0108

When I do this, I see a blank screen, as I don’t have a target. I can select that on the right if I’ve configured one.

2025-03_0109

If I pick the drop down, you will see my dev and QA environments. I made changes in the dev environment, and want these changes to go to QA. I can also click the “Manage environments” button. Let’s do that.

2025-03_0110

When I do that, I see a list of environments. I use this a lot to double check that I’m deploying to the right place for this project and where to look for changes. If you work across multiple projects as I do, this is handy.

You can see below I’ve selected QA with the green checkmack in the radio button. Once you pick your environment, you can click “Confirm”. If you need a new target, click Configure.

2025-03_0111

I return to the deploy screen, and this time I see the changes that have not been deployed. In this case, that’s one change, but if there were more, I would see them. I can click any object to see the changes, and once I’ve selected what to deploy, I can click deploy.

2025-03_0113

When I click Deploy the script is generated and I see a preview. I also can see that this is from my schema model on disk being deployed to the Qa database. That’s handy as this might take a few minutes and who knows what distractions might appear in my life.

I also can execute this as a transaction or not, and copy the script if I want to examine it in another tool (or format it like SQL Prompt).

2025-03_0114

When I click Deploy, I need to confirm my deployment. Always a good step, though I think if you do this often, your muscle memory will be ready to just click confirm.

2025-03_0115

Once the deployment runs, we see a message this was successful.

2025-03_0116

If I now check the QA database, I see the object.

2025-03_0117

Summary

This post has shown how you can perform manual deployments of changes in a state-based method using Flyway. This is a quick way to move your changes from one machine to another.

Flyway can do much more, and for smoother automation, check out Flyway Enterprise.

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. It works for SQL Server, Oracle, PostgreSQL and nearly 50 other platforms.

Video Walkthrough

Here is a video of the process below.

Posted in Blog | Tagged , , , | Comments Off on Friday Flyway Tips: State-based deployment with Flyway