Category: SQL Server

ANSI Blandard

Sometimes there are very good reasons to use either coalesce or isnull, owing to them having different capabilities, behaviors, and support across databases.

But isnull has some particular capabilities that are interesting, despite its limitations: only two arguments, specific to SQL Server, and uh… well, we can’t always get three reasons, as a wise man once said.

There is one thing that makes isnull interesting in certain scenarios. Let’s look at a couple.

Green Easy

First, we’re gonna need an index.

CREATE INDEX party 
ON dbo.Votes
    (CreationDate, VoteTypeId) 
INCLUDE
    (UserId);

Yep, we’ve got an index. Survival of the fittest.

Here are some queries to go along with it. Wouldn’t want our index getting lonely, I suppose.

All that disk entropy is probably scary enough.

SELECT TOP (10) 
    u.DisplayName
FROM dbo.Users AS u 
WHERE NOT EXISTS
( 
    SELECT 
        1/0 
    FROM dbo.Votes AS v 
    WHERE v.UserId = u.Id 
    AND v.VoteTypeId IN (1, 2, 3)
    AND ISNULL(v.CreationDate, '19000101') > '20131201' 
)
ORDER BY u.CreationDate DESC;

SELECT TOP (10) 
    u.DisplayName
FROM dbo.Users AS u 
WHERE NOT EXISTS
( 
    SELECT 
        1/0 
    FROM dbo.Votes AS v 
    WHERE v.UserId = u.Id 
    AND v.VoteTypeId IN (1, 2, 3)
    AND COALESCE(v.CreationDate, '19000101') > '20131201' 
)
ORDER BY u.CreationDate DESC;

Pager Back

The first query uses isnull, and the second query uses coalesce. Just in case that wasn’t obvious.

I know, I know — I’ve spent a long time over here telling you not to use isnull in your where clause, lest ye suffer the greatest shame to exist, short of re-gifting to the original gift giver.

Usually, when you wrap a column in a function like that, bad things happen. Seeks turn into Scans, wine turns into water, spaces turn into tabs, the face you remember from last call turns into a November Jack O’Lantern.

But in this case, the column wrapped in our where clause, which is the leading column of the index, is not nullable.

SQL Server’s optimizer, having its act together, can figure this out and produce an Index Seek plan.

The null check is discarded, and end up with a Seek to the CreationDate values we care about, and a Residual Predicate on VoteTypeId.

Big Famous

The second query, the one that uses coalesce, has a few things different about it. Let’s cut to the plan.

Rather than 157ms, this query runs for over a minute by five seconds. All of the time is spent in the Top > Index Scan. We no longer get an Index Seek, either.

Notice that the predicate on CreationDate is a full-on case expression, checking for null-ness. This could be an okay scenario if we had something to Seek to, but without proper indexing and properly written queries, it’s el disastero.

The reason that the query changes is due to the optimizer deciding that a row goal would make things better. This is why we have a Nested Loops Join, and the Top > Index Scan. It doesn’t work out very well.

This isn’t the only time you might see this, but it’s probably the worst.

SUBTLERY

You can also see this with a pattern I often advocate against, using a Left Join to find rows that don’t exist:

SELECT TOP (10) 
    u.DisplayName
FROM dbo.Users AS u
LEFT JOIN dbo.Votes AS v
    ON  v.UserId = u.Id
    AND v.VoteTypeId IN (1, 2, 3)
    AND ISNULL(v.CreationDate, '19000101') > '20131201'  
WHERE v.Id IS NULL
ORDER BY u.CreationDate DESC;

SELECT TOP (10) 
    u.DisplayName
FROM dbo.Users AS u
LEFT JOIN dbo.Votes AS v
    ON  v.UserId = u.Id
    AND v.VoteTypeId IN (1, 2, 3)
    AND COALESCE(v.CreationDate, '19000101') > '20131201'  
WHERE v.Id IS NULL
ORDER BY u.CreationDate DESC;

It’s not as bad here, but it’s still noticeable.

The plan with isnull looks about like so:

At 163ms, there’s not a lot to complain about here.

The coalesce version does far worst, at just about 1.5 seconds.

We Learned Some Things

In SQL Server, using functions in where clauses is generally on the naughty list. In a narrow case, using the built-in isnull function results in better performance than coalesce on columns that are not nullable.

This pattern should generally be avoided, of course. On columns that are nullable, things can really go sideways in either case. Of course, this matters most when the function results in an otherwise possible Index Seek is impossible, and we can only use an Index Scan to find rows.

An additional consideration is when we can Seek to a very selective set of rows first. Say we can get things down to (for the purposes of explanation only) around 1000 rows with a predicate like Score > 10000.

For the remaining 1000 rows, it’s not likely that an additional Predicate like the ones we saw today would have added any drama to the execution time of a relatively simple query.

They may, however, lead to poor cardinality estimates in more complicated queries.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

I Apologize

I’ve been working with CDC and CT way too much, and even I’m annoyed with how much it’s coming out in blog posts.

I’m going to cover a lot of ground quickly here. If you get lost, or there’s something you don’t understand, your best bet is to reference the documentation to get caught up.

The idea of this post is to show a few different concepts at once, with one final goal:

• How to see if a specific column was updated
• How to get just the current change information about a table
• How to add information about user and time modified
• How to add information about which procedure did the modification

The first thing we’re gonna do is get set up.

ALTER DATABASE Crap
    SET CHANGE_TRACKING = ON;
GO 

USE Crap;
GO

--Bye Bye
DROP TABLE IF EXISTS dbo.user_perms;

--Oh, hello
CREATE TABLE dbo.user_perms
(
    permid int,
    userid int,
    permission varchar(20),
    CONSTRAINT pu PRIMARY KEY CLUSTERED (permid)
);

--Turn on CT first this time
ALTER TABLE dbo.user_perms
    ENABLE CHANGE_TRACKING  
    WITH (TRACK_COLUMNS_UPDATED = ON);

With that done, let’s stick some rows in the table, and see what we have for changes.

--Insert some rows
INSERT
    dbo.user_perms(permid, userid, permission)
SELECT
    x.c,
    x.c,
    CASE WHEN x.c % 2 = 0 
         THEN 'db_datareader'
         ELSE 'sa'
    END
FROM 
(
    VALUES (1),(2),(3),(4),(5),
           (6),(7),(8),(9),(10)
) AS x(c);

--What's in the table?
SELECT 
    cc.*
FROM dbo.user_perms AS cc

--What's in Change Tracking?
SELECT 
    ct.*
FROM CHANGETABLE(CHANGES dbo.user_perms, 0) AS ct;

This is what the output look like:

What Changed?

To find this out, we need to look at CHANGE_TRACKING_IS_COLUMN_IN_MASK, but there’s a caveat about this: it only shows up if you look at specific versions of the changed data. If you just pass null or zero into CHANGETABLE, you won’t see that.

Let’s update and check on some things.

--Update one row, change it to 'sa'
UPDATE up
  SET up.permission = 'sa'
FROM dbo.user_perms AS up
WHERE up.permid = 2;

--No update?
SELECT 
    ct.*
FROM CHANGETABLE(CHANGES dbo.user_perms, NULL) AS ct;

--No update?
SELECT 
    ct.*
FROM CHANGETABLE(CHANGES dbo.user_perms, 0) AS ct;

We updated a row, but if we look at the wrong version, we won’t see the change:

But we will see that permid = 2 has a different change version than the rest of the rows.

Zooming In

If we want to see the version of things with our change, we need to figure out the current version change tracking has stored. This is database-level, but it’s also… wrong.

But to start us off, we query CHANGE_TRACKING_CURRENT_VERSION.

The max version always seems to be one version too high. Doing this will return nothing from CHANGETABLE:

--No update?
DECLARE @ctcv bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
SELECT  @ctcv AS CHANGE_TRACKING_CURRENT_VERSION;

SELECT 
    ct.*
FROM CHANGETABLE(CHANGES dbo.user_perms, @ctcv) AS ct;
GO

To get data back that we care about, we need to do advanced maths and subtract 1 from the current version.

--No update?
DECLARE @ctcv bigint = 
    (SELECT CHANGE_TRACKING_CURRENT_VERSION() -1);
SELECT  @ctcv AS CHANGE_TRACKING_CURRENT_VERSION;

SELECT 
    ct.*,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('dbo.user_perms'), 
                    'permission', 
                    'ColumnId'), 
                    ct.SYS_CHANGE_COLUMNS) AS is_permission_column_in_mask
FROM CHANGETABLE(CHANGES dbo.user_perms, @ctcv) AS ct;

This is also when we need to bring in CHANGE_TRACKING_IS_COLUMN_IN_MASK to figure out if the permission column was updated.

Audacious

The next thing we’ll wanna do is tie an update to a particular user. We can do that with CHANGE_TRACKING_CONTEXT.

As a first example, let’s just get someone’s user name when they run an update.

--Update one row, change it to 'sa'
DECLARE @ctc varbinary(128) = 
    (SELECT CONVERT(varbinary(128), SUSER_NAME()));

WITH CHANGE_TRACKING_CONTEXT (@ctc)
UPDATE up
  SET up.permission = 'sa'
FROM dbo.user_perms AS up
WHERE up.permid = 4;

To validate things a little, we’re gonna join the results of the CHANGETABLE function to the base table.

SELECT 
    up.permid, 
    up.userid, 
    up.permission,
    ct.SYS_CHANGE_VERSION, 
    ct.SYS_CHANGE_OPERATION,
    CONVERT(sysname, ct.SYS_CHANGE_CONTEXT) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES dbo.user_perms, 0) AS ct
LEFT OUTER JOIN dbo.user_perms AS up
    ON ct.permid = up.permid;

We get sa back from SYS_CHANGE_CONTEXT, so that’s nice. But we’re doing this a bit naively, because the SYS_CHANGE_OPERATION column is telling us that sa inserted that row.

That’s… technically wrong. But it’s wrong because we’re looking at the wrong version. This solution definitely isn’t perfect unless you really only dig into the most recent rows. Otherwise, you might blame the wrong person for the wrong thing.

Let’s add a little more information in, and get the right row out. We’re gonna go a step further and add a date to the context information.

--Update one row, change it to 'sa'
DECLARE @ctc varbinary(128) = 
    (SELECT CONVERT(varbinary(128), 
                        'User: ' +
                        SUSER_NAME() +
                        ' @ ' + 
                        CONVERT(varchar(30), SYSDATETIME())) );

WITH CHANGE_TRACKING_CONTEXT (@ctc)
UPDATE up
  SET up.permission = 'sa'
FROM dbo.user_perms AS up
WHERE up.permid = 6;


DECLARE @ctcv bigint = 
    (SELECT CHANGE_TRACKING_CURRENT_VERSION() -1);
SELECT  @ctcv AS CHANGE_TRACKING_CURRENT_VERSION;

SELECT 
    up.permid, 
    up.userid, 
    up.permission,
    ct.SYS_CHANGE_VERSION,
    ct.SYS_CHANGE_CREATION_VERSION,
    ct.SYS_CHANGE_OPERATION,
    ct.SYS_CHANGE_COLUMNS,
    CONVERT(sysname, ct.SYS_CHANGE_CONTEXT) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES dbo.user_perms, @ctcv) AS ct
LEFT OUTER JOIN dbo.user_perms AS up
    ON ct.permid = up.permid;
GO

Cool! Now we have the right operation, and some auditing information correctly associated with it.

Proc-nosis Negative

To add in one more piece of information, and tie things all together, we’re going to add in a stored procedure name. You could do this with a string if you wanted to identify app code, too. Just add something in like Query: dimPerson or whatever you want to call the section of code generated the update.

CREATE OR ALTER PROCEDURE dbo.ErikIsSickOfChangeTracking
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

DECLARE @ctc varbinary(128) = 
    (SELECT CONVERT(varbinary(128), 
                        'User: ' +
                        SUSER_NAME() +
                        ' @ ' + 
                        CONVERT(varchar(30), SYSDATETIME()) +
                        ' with ' +
                        OBJECT_NAME(@@PROCID)) );

WITH CHANGE_TRACKING_CONTEXT (@ctc)
UPDATE up
  SET up.permission = 'sa'
FROM dbo.user_perms AS up
WHERE up.permid = 8;

DECLARE @ctcv bigint = 
    (SELECT CHANGE_TRACKING_CURRENT_VERSION() -1);
SELECT  @ctcv AS CHANGE_TRACKING_CURRENT_VERSION;

SELECT 
    up.permid, 
    up.userid, 
    up.permission,
    ct.SYS_CHANGE_VERSION,
    ct.SYS_CHANGE_CREATION_VERSION,
    ct.SYS_CHANGE_OPERATION,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
        (COLUMNPROPERTY(OBJECT_ID('dbo.user_perms'), 
                        'permission', 
                        'ColumnId'), 
                        ct.SYS_CHANGE_COLUMNS) AS is_column_in_mask,  
    ct.SYS_CHANGE_COLUMNS,
    CONVERT(sysname, ct.SYS_CHANGE_CONTEXT) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES dbo.user_perms, @ctcv) AS ct
LEFT OUTER JOIN dbo.user_perms AS up
    ON ct.permid = up.permid;

END;

EXEC dbo.ErikIsSickOfChangeTracking;

Which will give us…

It’s Over

I don’t think I have anything else to say about Change Tracking right now.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.