Wednesday, July 23, 2014

Changing default constraints with powershell

I don't think it's a secret that I loves me some powershell. I think it's one of those "force multiplier" tools that allow you to do what otherwise might be very tedious fairly simply. Case in point: in this StackOverflow question, the poster asked if there was an easy way to change a user-defined function that was used in multiple default constraints in his database.

If you've ever used a user-defined function for a default constraint and tried to change it, you're well acquainted with the error message that essentially tells you "you can't change the definition of that function because it's being used!". So, in order to change the function, you need to drop all of the default constraints that reference the function, alter the function, and then re-create all of the previously dropped constraints. Depending on how widely used the function is, that could be a lot of work!

The below script will create two T-SQL scripts: one to drop all of the constraints that reference the function and one to add them all back. In practice, you'd run the drop script, alter your function's definition, and then run the create script. As you can see below, I used AdventureWorks2012 for my database and getdate for my function. Change those as appropriate for your situation and you should be good to go.


import-module sqlps;
$s = new-object microsoft.sqlserver.management.smo.server '.';
$db = $s.databases[ 'adventureworks2012' ];

$drop_options = new-object microsoft.sqlserver.management.smo.scriptingoptions;
$create_options = new-object microsoft.sqlserver.management.smo.scriptingoptions;

$drop_options.filename = 'c:\temp\default_drops.sql';
$drop_options.tofileonly = $true;
$drop_options.scriptdrops = $true;
$drop_options.AppendToFile = $true;

$create_options.filename = 'c:\temp\default_creates.sql';
$create_options.tofileonly = $true;
$create_options.scriptdrops = $false;
$create_options.AppendToFile = $true;

foreach ( $table in $db.tables ) {
    foreach ( $column in $table.columns ) {
        if ( $column.defaultconstraint.Text -eq '(getdate())' ) {
            $column.defaultconstraint.script( $drop_options );
            $column.defaultconstraint.script( $create_options );
            $column | select parent, name, defaultConstraint;

        }
    }
}

Wednesday, July 2, 2014

Looking at tracer tokens

People seem to be scared of replication in general. I can't say that I blame them; it's reputation for being fragile and non-intuitive is (in my opinion) well-deserved. However, there are some nice things. One of my favorite "costs almost nothing" features is tracer tokens.

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.