Category Archives: Intermediate

I’m going to make you love backups

Seriously.

This event is past, but you can watch the recording here!

On April 12 (11am Central time), I’m going to show you about two dozen ways to make your backups better, easier, faster, and more complete…using Minion Backup, of course.

Go ahead and register now.

minion backupBetter

You want to order your backups? Exclude one database? Just back up your read only databases at the end of the quarter? I’ll show you how to do all that, and of course more.

Easier

Of course we made Minion Backup plug-and-play…after all, this is the tool we use, at home and at client sites.

But even better, everything, including schedules, is table based. No more sorting through 9 different backup jobs to make sure that the new DB34 database has full backups on second Saturdays!

Faster

Did you know you can tune backups? If you did, pat yourself on the back…you’re in a slowly growing minority.

If you didn’t, today’s your lucky day! You can determine how many and what resources to throw at your backups to make them faster – and I’ll give you a big spoiler right here – that also makes your restores run faster.  You can learn all about that here in Sean’s Backup Tuning class (here’s the demo code), or you can get the highlights during my MB class…

…and hear about how to make your backup tuning dynamic!

Complete

Soooo…does your backup solution do everything?

  • Does it cover certificates?
  • Does it have tons of data logged to tables? Live insight, so you can see how far along any given backup is?
  • Does your backup solution automatically move and copy backup files?
  • How about retention, can you set backup file retention easily, and per database, and per backup type?

I’m telling you…you’re going to love this. Come by the session and see.

So did you register yet? Go ahead, I’ll wait.

See you there!

Happy days,
Jen

 

Database snapshots for everyone!

Database snapshots have been available in SQL Server Enterprise edition in the last several versions. But if you’re lucky enough to have 2016 at work, go on and upgrade to SP1….you get extra-lucky special database snapshots in any version!

(Be sure to read the footnote for “Database snapshots” in that link.)

What are database snapshots for?

Oh what’s that? You don’t use snapshots? Well let’s see a couple of good use cases for them:

  • Take a database snapshot before an upgrade, data load, or major change. It’s WAY faster than a backup, and you can roll back the changes if you need to, almost instantly.
  • Customize integrity checks. When you run DBCC CheckDB or DBCC CheckTable, behind the scenes SQL Server creates a snapshot of the database to run the operation against. You can also choose to create a custom snapshot…among other reasons, so you can specify where the snapshot files are placed. This is especially useful for shops running tight on disk space.

Speaking of integrity checks and snapshots, Minion CheckDB is slated to come out on  this coming February 1.  Be on the lookout…it’s going to blow your mind.

Happy days,
Jen

Tricky TSQL: NOT IN (NULL)

We’ve done this before, but we can go one better this time.

Let’s take this step by step.

NULL means “I don’t know”. It stand for an unknown value.

Nothing can be equal to NULL. We simply can’t say that 1 = NULL, or ‘ABBA’ = NULL,  because we don’t know what value NULL might possibly be in the real world.

Nothing can be NOT equal to NULL. We can’t say that 1 <> NULL, or ‘ABBA’ <> NULL. NULL may well turn out to be 1, or ABBA, or whatever value we’re trying to check. We simply don’t know.

A simple example

So let’s take a very simple example, to demonstrate how this works. This particular query will return the value ‘True’, because 1 is demonstrably equal to 1:

IF 1 = 1
   SELECT   'True!';

Yep, that makes sense. I can tell: 1 = 1.

This query will not return ‘True’; it will return nothing, because 1 cannot be proven to be equal to NULL:

IF 1 = NULL
   SELECT   'True!';

We have an unknown, and nothing can possibly be equal to the unknown.

Another way to put this, is to use the keyword IN:

IF 1 IN ( SELECT    NULL AS a )
   SELECT   'True';

That parenthetical SELECT NULL simply returns NULL, so the query ends up asking: “is the value 1 in the set of values (NULL)?” We have no idea, so the query does not return ‘True’.

Almost the same thing, but trickier

Here’s yet another query that will not return ‘True’; 1 cannot be proven to be NOT equal to NULL:

IF 1 <> NULL
   SELECT   'True!';

NULL is unknown, and nothing can possibly be NOT equal to the unknown.

NULL will mess up your  NOT IN queries

And the absolute trickiest bit – the one that will trip you up, if you’re not thinking? NOT IN.

This query also returns nothing!

IF 1 NOT IN ( SELECT    2 AS a
              UNION
              SELECT    NULL AS a )
   SELECT   'True';

The IF statement asks: “is 1 in the collection of values (2, NULL)?” And we must say, “ah, no idea. That can’t be proven. Therefore we can’t return true.”

The value 1 cannot be determined to not be in the set of (2, NULL).

Play around with these, let it sink in. And watch out for NULLs in your NOT IN result sets!

One Fix

So, always use IS NULL or IS NOT NULL for your NULL comparison needs. And if you have a [value] NOT IN situation, read on.

Here’s what I did* to fix this exact situation in a recent query:

 SELECT ID
 FROM   Table1
 WHERE  ID NOT IN ( SELECT  ID
                    FROM    Table2
                    WHERE   ID > 0 );

While we could have used ID IS NOT NULL instead of ID > 0, it would make any relevant index on Table2 useless to us. In this particular case, ID is restricted to only positive above 0, and NULL.

To read up on even more solutions, see Mladen Pradjic’s article JOIN vs IN EXISTS.

Happy days,
Jen
www.MidnightDBA.com/Jen