Monday, March 10, 2014

Using the ANY operator

I'll be the first to admit that I don't know everything about T-SQL. But I do like to think that I've had broad enough exposure to know of the existence of certain things so that when their use is appropriate I know where in TFM to R.

Hit Any key to continue

Case in point: I was working with one of my developers recently who had an interesting problem. Of the columns that we were already capturing via CDC, he was only interested in a given change if any of a subset of those columns were changed. His initial approach was to use fn_cdc_has_column_changed which was very slow (and the documentation even suggests against using it for that very reason!).

By way of example, let's say that I have CDC set up against the Production.Product table in AdventureWorks2012 that captures change for all columns in the table. But, for whatever reason, we have a process that only cares about changes to the Name, StandardCost, and ListPrice columns. If any (or all) of those columns are updated, we want to know. If none of those columns are updated, we don't want our query to return a row for that change.

I came up with a solution involving fn_cdc_get_column_ordinal and fn_cdc_is_bit_set that I thought was a little clever. Plus, it marks the first time that I've ever used the ANY operator in actual code. Here goes:


with cte as (
    select * from (values
        ('Name'        ),
        ('StandardCost'),
        ('ListPrice'   )
    ) as x(col)
)
, column_ordinals AS (
    select sys.fn_cdc_get_column_ordinal('Production_Product', col) as [ordinal]
    from cte
)
SELECT *
FROM cdc.[fn_cdc_get_net_changes_Production_Product](
    sys.fn_cdc_get_min_lsn('Production_Product'), 
    sys.fn_cdc_get_max_lsn()
    , 'all with mask'
) AS fcgacpp
WHERE [__$operation] = 4 --after update
    AND 1 = ANY (
        select [sys].[fn_cdc_is_bit_set]([ordinal], [__$update_mask])
        from [column_ordinals]
    )

Let's break it down. First, I've got two common table expressions. The first of which just takes the names of the columns we're interested in, pumps them through a table value constructor to produce a table. The second CTE takes the results of the first and figures out what position each column is in in the CDC change table. For instance, we find that the Name column is column number 2.

Next, we use the cdc.fn_cdc_get_net_changes_... function to, well, get the net changes for that capture instance. The magic comes in the where clause. The first predicate is just saying that we're only interested in "after update" rows. The second predicate looks at the update mask that comes along with the CDC record and if one of the columns that we care about was updated returns a 1. Otherwise, it returns a 0. Once that calculation is done for all columns, that result set is fed to the ANY operator and if any of the columns that we care about was updated, that predicate will evaluate as true and the row will show up in the result set.

Tuesday, December 24, 2013

A little fun with HierarchyId

I was recently enhancing the DDL trigger that we have implemented on all of our servers. The requirement was to e-mail the team whenever a security related event came up. Here's the trick: how do we alert on just the security events without having to hard code all of them? Here's how I did it.

Thursday, November 21, 2013

VLF Fragmentation Analysis

While doing a little analysis of VLF Fragmentation, I found one database that had some trouble. Specifically, it had CDC enabled and the log reader agent had stopped a while ago. So, in short, the log had been growing unchecked for quite some time. After fixing the underlying problem, I decided to shrink the log. In order to help me find out where in the ring the log was, I came up with a little script.

Monday, October 21, 2013

On software delivery and NASCAR

Sometimes, I come up with what I think are good analogies for the way things work. My latest was about the necessity of being safe in deploying to a production environment. Here goes:

NASCAR is an exciting sport for both the spectators as well as the driver and crew. But, what makes it possible at all are all the safety measures that are in place in the event of catastrophe. I think that the viewership wouldn't be quite as high if every time there was a crash the driver came out of the car with his face melting off. When you're moving at the edge of human reaction times and engineering, things are bound to happen from time to time. It's what you do in reaction to that that is the difference between a sport and grizzly spectacle.

Monday, October 7, 2013

I'm presenting!

I'm a little late in announcing this, but I've been accepted to speak at SQL Saturday 238 this coming Saturday. Come see me at the U!

And, in a shocking turn, I'm also speaking at SQL Saturday 256 in Kalamazoo, MI on Nov 2. Hope to see you there.

Friday, July 19, 2013

A small CDC nugget

In the style of Jim Anchower, it's been a long time since I rapped at ya. Life's been busy, but that's little excuse, eh?

I recently read an article on SQL Server Central here that gives a very decent overview of CDC processing. One thing that I pulled out was an undocumented stored procedure called  sys.sp_cdc_dbsnapshotLSN that will, as the name suggests, give you the LSN of a database snapshot. Pretty useful stuff if you need to re-initialize your warehouse!

Friday, October 26, 2012

Changing the operator on a job

So I was in the position today to change the operator on a bunch of SQL Agent jobs here. Since the server was very remote, I didn't want to clickety-clack through it. Not that I'd do it that way anyways, but either way, I used Powershell.