Using NULLIF–#SQLNewBlogger

Posted on October 6, 2021 by way0utwest

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.

Posted in Blog | Tagged , , | Comments Off on Using NULLIF–#SQLNewBlogger

Choosing State Over Migrations

Posted on October 6, 2021 by way0utwest

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.

Posted in Editorial | Tagged | Comments Off on Choosing State Over Migrations

Daily Coping 6 Oct 2021

Posted on October 6, 2021 by way0utwest

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.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 6 Oct 2021

Daily Coping 5 Oct 2021

Posted on October 5, 2021 by way0utwest

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 not compare how you feel inside to how others feel outside.

One of the things I’ve learned in my life is that many times other project a much different view of their life than they feel. I notice I do the same thing. I might appear happy or joyous when I’m not feeling that way inside. I may be full of stress, anxiety, concern, or some other negative feeling from life.

If I deliver a presentation or go to a public space, I may not show those feelings.

In this age of social media where it seems others are loving life, having success, and doing fun things that you envy, it’s easy to think their life is better than yours. This might be exacerbated in your mind when you aren’t enjoying life. You might feel you are unworthy or much less successful/capable/satisfied/etc. in life than others.

Remember that you don’t really know how others are doing, even if they are your close friends. You may not even realize how your family members truly feel.

Focus on yourself, which is what I try to do more often. I may look at the things others do as ideas for my own life, but I try very hard not to make comparisons.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 5 Oct 2021