Voice of the DBA

Author: way0utwest

Visualizing SQL

While I think SQL is interesting, I know some people struggle with the way the language work. Someone at work posted a link to this site: https://animatesql.com/

I think the idea is this site helps you visualize how a SQL query works. It’s not free form, and I can’t just write any SQL, but you choose a keyword and then a sample query is shown. If you press Visualize, it walks through how this query is processed.

Below, I choose the WHERE keyword and then pressed visualize. The sample query had a NOT before the parenthesis, which I removed.

The animation then works through each row, showing whether it fits the criteria. Here I have captured what things look like in the middle:

Eventually this goes through the process again with the SELECT part of the query and pulls results from the yellow rows, according to the columns listed in the query.

If you try the different keywords, you see different visuals and queries. You can modify them slightly, and the comments in the query window let you know what you change. The modifications are small, but they do let you see how well things work in different ways, such as if you change the value or remove a logical operator, as I did.

As I tried a few different items, it made sense to me, but I wonder if it would for someone that was learning. I think that this ought to have some explanation of what is happening (i.e. comparing the name and rating in each row to be null or not, and matching the criteria). For the GROUP BY, I really think someone might not understand what they are seeing without some explanation.

I also think the lack of NULL in the data, and showing a blank, is highly misleading as well.

This looks like something a developer built, and I think it is creative and helpful, but like many 1.0 things, this needs some evolution and improvement.

April 18, 2022
A Third Space

It’s 2022, two years into a pandemic, and it finally seems that many things are returning to the way they were in 2019. Life outside of work is fairly normal for me, and I have a number of trips. Work is starting to pick up and we’ve seen a few conferences and SQL Saturday events pop up on the schedule.

Work is still mostly remote for many technology workers. Not all, and some companies are starting to require workers to come back to the office, part or full time. My son got a job as a software engineer and he has to go to the office every day for six months. After that, if he proves himself reliable, he can work at home 2-3 days a week.

For many tech people I talk with, there is a still a mostly remote atmosphere. The feelings about this situation vary dramatically from person to person, and from role to role. I find as many managers love remote work as hate it. I find more tech people prefer remote work, but not all, and certainly not all like working at home. Depending on your environment, and how many others might be at home (and their ages), you might find remote work more stressful than commuting to an office.

A friend pointed me to an article on someone that doesn’t love working at home and needs a third space. My friend feels the same way, as isolation and quiet can be hard to cope with for long periods. As much as I have loved working from home for nearly 20 years, I haven’t done this for 20 years. I’ve traveled regularly and when I have long stretches at home, I usually spend some periods in a Starbucks or another space just to be around people. Talking to my dogs isn’t bad, but they often don’t talk back in a way that satisfies my need for background noise.

My friend’s company has a subscription to WeWork, which allows remote employees to get some social interaction, or just ambient noise, as needed. We’ve experimented with this at Redgate as well, and while I don’t know I’d go to WeWork over a coffee shop or restaurant, I do appreciate the ability to have a space where 5 or 10 of us could gather in an ad hoc manner periodically. If I had more employees in Denver, I would look forward to meeting them a few times a month.

For those of you working at home, do you want a place to gather on a regular or periodic basis? Do you miss interacting with live people? Or do you love being alone at home. I certainly like the latter many days of the week, but I miss interacting with people on a regular basis. I look forward to regular trips to Redgate offices and more events where I can talk technology with people whose hands I can shake.

Steve Jones

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

April 18, 2022
Daily Coping 18 Apr 2022

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to set yourself an exercise goal or sign up for an activity challenge.

I use Garmin Connect with my Forerunner watch to track my activity. I think I’ve been doing well this year overall. Here are the activities I’ve done this year, as of last week:

I tend to do my thing, but with this tip, I decided to join a challenge. There are some available, and I decided on this one:

With a bum ankle, I didn’t want to do any walking challenges. For April, the challenge is cycling, which is something I do 2-3 times a week, either as a warmup for lifting or just to burn calories. I need to go 675km before June 30.

For April, I have a few indoor cycling activities, but I need to start doing more and adding some distances. I tend to cycle inside for now, especially as my ankle acts up. I can go slower without the pressure of guessing how far away to ride based on how I feel. I tend to go 5-7 mi before lifting and 12-15 on ride days, which gets me into the 8-12km range or 19-24km. If I say I’ll often go 12km, I need 56 days of riding. That’s going to be tough as I bet I lose 3 weeks for surgery.

I’ll see what I do, and try to post some updates. My aim here is to work harder in April and early May, especially as I’ll be on the road a bit and riding a bike is easy in a hotel. I don’t know when I’ll be able to ride more in June with rehab, but I’m hoping I can get some km in.

April 18, 2022
Constant Azure Improvement

This week I noticed a number of changes in the Azure platform from their change feed. Actually, every week has a crazy number of changes across the entire platform, but this week a number of these were related to data and databases. That isn’t always the case, but I saw some interesting items.

We have zone redundancy for the general purpose tier of Azure SQL database, as well as AAD security principals in the master database and a new DMV for backups. I think Azure SQL Database is a great product, though one that has felt quite limited at times. The use of DTUs at first, the high pause period for Serverless and limited options at lower tiers were always annoying.

The point at times seemed to be to get customers to place a big financial bet that a higher tier would meet their needs. I had hoped that some of that was just a blip in time as technology improved and more features would be deployed to all tiers. After all, the original Azure SQL database was a key-value store.

That seems to be coming true as I continue to see improvements to the offerings. Azure SQL Serverless now has a 1 hour pause period minimum instead of the original 6 hour time. Now Cosmos DB has also lowered their autoscale offering. Cosmos DB can be provisioned at a lower entry point for autoscale, at 100RU/s instead of 400RU/s, which might get more people to experiment with a PoC over a bit of time and use the database. I know I’m thinking of trying to mess with some data on the platform. I don’t think I’d need 100 RU/s, much less 400, but I can better live the cost for a few months.

Hyperscale has been an interesting version of Azure SQL Database. I know quite a few customers that aren’t sure if they need it, but they considered experimenting with it. The problem was that once you choose Hyperscale, you can’t go back to another tier. That’s quite a disincentive to experimentation, which seems to be part of the philosophy of choosing the cloud. There is now a preview of the migration of Hyperscale back to the general purpose tier. I’m guessing it took a little time for the Azure team to implement this, which is why there was a restriction, but it looks like that limit will go away.

The cloud is an amazing place, as I’ve written before. It enables things to happen at a pace, and with ease that I’ve never seen in a data center without a lot of prep work. This week it seems that the Azure cloud advanced quite a bit in the area that I care about, the data platform space.

There was also the general availability release of the Azure SQL Migration extension for ADS.

Steve Jones

April 16, 2022