Voice of the DBA

Author: way0utwest

  • Daily Coping 7 Oct 2021

    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 accept yourself and remember you are worthy of love.

    This is one of those tough things for me. I’m definitely a giver, and with history as someone that grew up in a broken home, I’m usually more concerned with others than myself. I also sometimes question if I deserve something.

    Over time, I’ve learned that no matter how I feel, I am worthy of being loved and cared for. I still struggle, but I constantly remind me of this.

  • Using NULLIF–#SQLNewBlogger

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

    I ran across the NULLIF() function recently, and I realized I’d never used it in code. It’s an interesting function, one that I didn’t think would be useful, but I found a couple places.

    NULLIF Behavior

    This function is essentially short for “return a null if these two values are equal.” There are two parameters you pass in and if they are equal, you get a NULL back. Somewhat strange function, but here are a few examples:

    2021-09-20 15_42_19-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

    The interesting one is that 1 and NULL come back with the first value. We can’t determine if NULL is equal to 1, so we assume not.

    Using This Function

    When would you use this? As I said, I have never thought to use this, but I did find a couple interesting items. A mixture of NULL and a certain value is one place, if you can use the NULL. For example, let’s say I have some data in a table:

    2021-09-20 15_44_46-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

    I have some blanks and some NULL values. Suppose I want to query and show the category, but if that is a NULL or blank string, show the SubCat instead. I can do this with a CASE, but that get’s ugly. NULLIF makes this easy to read.

    2021-09-20 15_45_56-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

    The other interesting place I thought of here was with aggregates and potentially filtering out some values. Aggregates tend to ignore NULL, so what if I have this data:

    2021-09-20 15_48_24-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

    Suppose I want the average sale, but not with the zero values. Those might be returns, and we don’t want to skew our average. I could use NULLIF to make this easy to code. Notice the short code below and the difference from the straight average:

    2021-09-20 15_49_03-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

    I could use CASE, but which is easier to read?

    2021-09-20 15_50_20-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

    I think NULLIF is, if you know how the function works.

    SQLNewBlogger

    This was a function I stumbled on and wasn’t sure how to read. I spent about 10-15 minutes searching around the Internet looking for a reason to use this code. I saved the link for them and added it into the post. I spent about 10 minutes creating a little code example and then running it.

    I then wrote this post, which was about 10 minutes, mostly because I used screen shots for code, which were quick to grab and paste in.

    This is a nice example of learning something, understanding how it works, and then thinking where it could be useful.

  • Choosing State Over Migrations

    I ran across an article about state v migration based development for Snowflake. While I think that this article is a bit slanted because the author is building a tool, it does present some good reasons why people worry about migration-based approaches. This article also references a work from Alex Yates on the two approaches.

    Everything we do in relational databases, and in some other platforms like Snowflake, is a migration script. If you run SQL Compare and get the differences, you are generating a migration script. It might contain lots of changes, but it’s still a migration script. I wish I could write CREATE OR ALTER TABLE scripts, but that’s not a part of the SQL specification (yet).

    The concerns in the article are valid, though some are a bit outdated. I know that I am biased a bit as my employer, Redgate Software, sells tools in this area. However, over the years, we’ve supported and worked on both approaches. We do a lot of research, and I get to provide feedback on the good and bad of how we approach the problem. Ultimately, I think the world is very chaotic and we should be as flexible as possible with developers.

    I find that most developers like the state approach. Even if they want to write code themselves, having an easy way to capture the state of objects and keep track of them is useful. When we see a table difference, we want to see a view of the old table and new table in a CREATE TABLE statement: the state approach.

    For deployments, we always need migration scripts, but we want to build them as appropriate for the situation. Cherry-picking out specific changes is often what we need when urgent hot fixes need to be deployed, or a piece of work isn’t tested. In those cases, we might need to deploy some changes and not others, which often require us to put a subset of changes into a migration script(s). That’s a hassle, and these situations are often where we find humans making mistakes.

    Ultimately I prefer state sometimes and migrations others. If I had to pick only one, it would be migrations, but I’d ensure I had a state view of the schema using a tool like SQL Compare. I want to be able to see history and capture changes. in a human-readable format.

    One approach isn’t better, and both have advantages and disadvantages. We are often under pressure to be more like DevOps software developers, adaptive and agile. We can use either state or migrations to do this. The important thing is that your whole team understands the approach we are using and the potential limitations. They also need to work together to smooth out your database deployments.

    Steve Jones

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

  • Daily Coping 6 Oct 2021

    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 let go of other people’s expectations of you.

    I really try to live my own life, as I want to live it with my wife. The goal is to think about, and choose, those things that matter to us, not to others. This sometimes means that others might feel I haven’t made good choices and that can be hard. However, I do work to find confidence that I am making the decisions that are important to me and my wife.

    I try to teach kids I coach to approach life like this as well. Find out who you are and do the things that matter to you.

    This doesn’t mean you avoid responsibilities and accountability for actions. Instead, this means that you accept those while navigating the life that you want to build. In your career, in relationships, with family, with friends, and in your community.

    Embrace life, but make it your life, not anyone else’s.