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
go

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!

SELECT 'CREATE CERTIFICATE ' + QUOTENAME([name]) 
    + ' AUTHORIZATION ' + USER_NAME([c].[principal_id]) 
    + ' FROM BINARY = ' + CONVERT(VARCHAR(MAX), CERTENCODED([c].[certificate_id]), 1)
    + ' WITH PRIVATE KEY (BINARY = ' 
    + 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.