Wednesday, October 7, 2015

A little hierarchyid magic to get all ancestors

I'm going to let you in on a little secret: I love the hierarchyid datatype! It's not often that I get to use it, but when I can, it solves the problem so elegantly. So when this Stack Overflow question came up, I couldn't help myself. The question boils down to: given a hierarchyid, is there a way to find all the ancestors of it? Before we answer that question (spoiler alert: the answer is "yes we can!"), let's learn to walk before we run.

HierarchyID is a CLR datatype (just like the geospatial and xml types). As such, it defines not only data, but behavior as well. The hierarchyid method reference can be found here. But for our purposes, we're only concerned with two methods: GetLevel() and GetAncestor(n). GetLevel() essentially tells us how deep in the tree this particular node is. So, if I have a hierarchyid defined as @h = '/1/2/3/4/5/', @h.GetLevel() returns 5 because the leaf node is five steps away from the root. GetAncestor(n) returns the nth ancestor of the given hierarchyid. So, to reuse our hierarchyid of @h = '/1/2/3/4/5/' from before, @h.GetAncestor(1) returns '/1/2/3/4/', @h.GetAncestor(2) returns '/1/2/3/' and so on.

So now back to the Stack Overflow question. Out of the box, there's no way to get all the ancestors for a given hierarchyid. But, it's not too hard to roll your own if you have a numbers table. Here's the code:

alter function dbo.GetAllAncestors(@h hierarchyid, @ReturnSelf bit)
returns table
as return
 select @h.GetAncestor(n.Number) as h
 from dbo.Numbers as n
 where n.Number <= @h.GetLevel()
  or (@ReturnSelf = 1 and n.Number = 0)

 union all

 select @h
 where @ReturnSelf = 1

By way of explanation, all I'm doing is returning the numbers 1-(@h.GetLevel()) from a tally table and then using those tally values as the argument to GetAncestor. As a convenience, I've also specified an argument to the function that says whether you consider the passed in hierarchyid to be its own ancestor (I've been in applications where that made sense).

So there you go. A little hierarchyid fun.

Thursday, September 10, 2015

Creating a certificate from binary - redux

Back in a simpler time, I talked about new syntax introduced in SQL 2012 that would allow you to create a certificate from a varbinary string. In that post, I mentioned that some enterprising person might be able to script it out so you didn't have to go through the copy/paste dance. Well… I've been doing a lot of creating certificates for a service broker implementation. So, I figured it was time for that enterprising individual to be me. So here goes!

    + ' AUTHORIZATION ' + USER_NAME([c].[principal_id]) 
    + ' FROM BINARY = ' + CONVERT(VARCHAR(MAX), CERTENCODED([c].[certificate_id]), 1)
    + CONVERT(VARCHAR(MAX), CERTPRIVATEKEY([c].[certificate_id], 'f00bar!23'), 1)
    + ', DECRYPTION BY PASSWORD = ''f00bar!23'')'
FROM [sys].[certificates] AS [c]
WHERE [name] = ''
This assumes (as is the case for the certificates I'm dealing with) that the the private key is encrypted by the master key both at the source and the destination. If either of those is different in your case, you may need to add an "encryption by password" clause or an extra argument to the CERTPRIVATEKEY function to provide a decryption password. But there you (and future me) have it.

Wednesday, October 22, 2014

I'm speaking at SQL Saturday #350 in Winnipeg!

I just got confirmation today that I'll be speaking at SQL Saturday #350 in Winnipeg. If you're up that way, stop in and get some of that fancy learnin'. This will mark my first trip to Canada and now makes me an international speaker. So I've got that going for me, which is nice. :)

Friday, October 17, 2014

Script foreign key drops and creates: invitation accepted!

This morning as I was going through my morning reading, I came across a post that I knew would be relevant to my interests. Specifically, Aaron Bertrand wrote about a script that he had written to drop and re-create all the foreign keys in a given database. And this time, he explicitly put out an invitation for powershell solutions to the same problem (so I don't feel too bad about it this time). So here I am and here we go.
import-module sqlps -disablenamechecking

$drop_filename = 'c:\temp\fk_drop.sql';
$create_filename = 'c:\temp\fk_create.sql';

if (test-path $drop_filename) {
    remove-item $drop_filename;

if (test-path $create_filename) {
    remove-item $create_filename;

$drop_options = new-object
$drop_options.ToFileOnly = $true;
$drop_options.FileName = $drop_filename;
$drop_options.AppendToFile = $true;

$drop_options.ScriptDrops = $true;
$create_options = new-object
$create_options.ToFileOnly = $true;
$create_options.FileName = $create_filename;
$create_options.AppendToFile = $true;

$s = new-object '.';
$db = $s.databases['AdventureWorks2014'];

foreach ($table in $db.Tables) {
    foreach ($fk in $table.ForeignKeys) {
        $fk.script( $create_options );
        $fk.script( $drop_options );
As you can see, most of the code is preparations (deleting the files if they're already there and setting up the ScriptingOptions objects). The actual meat of it is just a couple of lines long. If you run this script, you'll end up with two T-SQL files that you can run: one to drop the foreign keys and one to re-create the foreign keys. Enjoy!

Tuesday, September 30, 2014

Changing computed columns columns to persisted - the easy way

In this morning's SQL Server Central newsletter, there was a link to an article from Aaron Bertrand about how to change whether a computed column is persisted or not. He presented a T-SQL method for doing so. Here, I'll present a powershell method for doing the exact same thing.

import-module sqlps;

$s = new-object '.';
$db = $s.Databases['AdventureWorks2014'];

foreach ( $tbl in $db.Tables ) {
    foreach ( $col in $tbl.Columns ) {
        if ( $col.Computed -and (! $col.IsPersisted) -and $col.IsDeterministic ) {
            $col.IsPersisted = $true;
Seriously, that's it. If you wanted to be fancy (or just want a chance to review the script before executing it), just use ScriptAlter() instead of Alter() and pass a ScriptingOptions object it. I leave this as an exercise to the reader. With nothing against Aaron, I find the above to be much more readable. To quote Larry Wall "TMTOWTDI", though. So pick your favorite and go with it.

I'll be giving a presentation at SQL Saturday #332 on just this sort of thing. If you want to learn a little powershell yourself, consider attending my session!

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 '.';
$db = $s.databases[ 'adventureworks2012' ];

$drop_options = new-object;
$create_options = new-object;

$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.