Voice of the DBA

Category: Blog

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:

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:

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.

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:

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:

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

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.

October 6, 2021
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.

October 6, 2021
Daily Coping 5 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 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.

October 5, 2021
Using SQL Data Compare with Joins
Recently a customer was asking about how they could move data with SQL Data Compare from one server to another. In this case, the customer had a complex join and WHERE clause they wanted to use with SQL Data Compare. This post will show a simple way to do this.

The Source Data

I can’t show customer data, but here is a mock up using the AdventureWorksDW sample database. I have this query that contains some information I want to sync from one database to another:
```
SELECT c.FirstName,
        c.LastName,
        dd.FiscalQuarter,
        dd.CalendarYear,
        fis.OrderQuantity,
        fis.SalesAmount,
        fis.PromotionKey
        fis.SalesOrderNumber

FROM dbo.FactInternetSales fis
     INNER JOIN dbo.DimDate dd
         ON dd.DateKey = fis.OrderDateKey
     INNER JOIN dbo.DimCustomer c
         ON c.CustomerKey = fis.CustomerKey

WHERE ProductKey = 310
       AND dd.CalendarYear = 2011;
```
This query includes multiple tables and a WHERE clause. In SQL Data Compare, there is no place to enter a query to use as a source for data. None of these sources allow for a query.

I can, however, simulate this query in a view. I add a line above the code and I have a view.
```
CREATE VIEW aDemoView AS

SELECT c.FirstName,
        c.LastName,
        dd.FiscalQuarter,
        dd.CalendarYear,
        fis.OrderQuantity,
        fis.SalesAmount,
        fis.PromotionKey
        fis.SalesOrderNumber

FROM dbo.FactInternetSales fis
     INNER JOIN dbo.DimDate dd
         ON dd.DateKey = fis.OrderDateKey
     INNER JOIN dbo.DimCustomer c
         ON c.CustomerKey = fis.CustomerKey

WHERE ProductKey = 310
       AND dd.CalendarYear = 2011;
```
I then need to check the option in the SQL Data Compare project to include views.

Once I do this, I see my view, although it is unmapped. Note views are at the bottom of the dialog.

Now I need a target.

Make a Table From the View

In the destination database, I need a comparison target. In this case, what I would do is take the definition of the view and use that to create a table. In this case, I’d start with the SELECT column list. I replace SELECT with CREATE TABLE, as shown here:
```
CREATE TABLE DemoView

(
        c.FirstName,
        c.LastName,
        dd.FiscalQuarter,
        dd.CalendarYear,
        fis.OrderQuantity,
        fis.SalesAmount,
        fis.PromotionKey, 
        fis.SalesOrderNumber
```
Next, I need to remove the aliases and round out the data types.
```
CREATE TABLE DemoView

(
        FirstName varchar(100),
        LastName varchar(100),
        FiscalQuarter tinyint,
        CalendarYear int,
        OrderQuantity int,
        SalesAmount NUMERIC(10,4),
        PromotionKey TINYINT, 
        SalesOrderNumber VARCHAR(20)
        )
```
Last, I will need a comparison key, so I’ll add the SalesOrderNumber as a PK.

CREATE TABLE DemoView
(
        FirstName varchar(100),
        LastName varchar(100),
        FiscalQuarter tinyint,
        CalendarYear int,
        OrderQuantity int,
        SalesAmount NUMERIC(10,4),
        PromotionKey TINYINT,
        SalesOrderNumber VARCHAR(20) CONSTRAINT DemoViewPK PRIMARY KEY
   )
GO

I execute this in my target database and this gives me a destination for the data.

The SQL Data Compare Map

Once I have a source and target, I can map them in SQL Data Compare. First, I select both objects in the Tables and Views tab and then click Map.

This moves the two items up to the top pane.

I need a comparison key in order to move data, and I select the “Not Set” on the left side. This gives me a place to set the comparison key. We need to know how to determine which rows are different from which other rows.

Once this is done, I click the item in the main pane and click “Compare”.

When this completes, I see my results. This table is shown as different, and I see the rows below.

All of these rows are only in the source, which is the left side. I can click “Deploy” and get a script to move all this data to the other server.

Options

In this case I had a fairly specific set of data in the view with a WHERE clause. If I wanted to keep this more generic, I could always use the WHERE clause in SQL Data Compare to select a set of data to move here.

The other option would be to SELECT .. INTO this data into a table on the source. I could load this into a table that I used for a comparison with the target. If I repeated this in the future, I could either truncate and reload this table, or just add to it, potentially with a different SELECT.

In general, I prefer to bulk move information and control the data to move outside of SQL Data Compare. It’s easy to forget to change a WHERE clause somehow in a project. Much easier to control what I load into the source and then move everything to the destination.

Summary

The easy way to move complex data from source to destination is by putting the complex data into a source location. Either a table of some sort or a view. Then we can use SQL Data Compare to easily move this to a destination in another database.

SQL Data Compare is an amazing product for syncing sections of data between databases. It might not work for all situations and can be slower with very large sets, but it’s a fantastic tool for DBAs.
October 4, 2021