Ever since the hierarchyid data type was introduced in SQL Server 2008 CTP4, there’s been a fair amount of discussion about renaming some of the methods of the data type to make them a bit more intuitive. In addition, there’s been discussion about “missing” methods that don’t exist in the hierarchyid data type. Well…
Because hierarchyid is a .NET-based system UDT, you can implement these “missing” methods yourself. There’s a couple of ways to go about this:
1. Use your own assembly that uses the hierarchyid (that’s Microsoft.SqlServer.Types.SqlHierarchyId to you) as parameters. Because its a “normal” SQL Server type, you can use you use it anywhere you can use a “native” SQL Server data type, like DATE.
2. Code your own UDT that inherits from SqlHierarchyId. Perhaps call it HIERARCHYID2, to further upset those folks who are offended by DATETIME2.
Wow. Did you say inheritence from a system UDT? Before we go down this path, bear in mind that this works with the CTP4 version of SQL Server 2008, but there’s no guarentees about it working in the next CTP or release. I’ve heard nothing to that effect, but you never know. I’ve coded up a little stub and it seems to work, but…
I thought that T-SQL doesn’t support UDT inheritence. Technically, it doesn’t. That is, the SQL Server system catalogs (sys.types, etc) don’t track UDT inheritence. This means that in order to allow T-SQL to “see” methods and properties in the base class, you need to write methods that do nothing but delegate to the base class. Because all the methods that you care about are public (by definition) you can do this. Simply override the methods that you don’t want to pass through. I wrote about this a long time ago, see this blog post from 2004.
A few other considerations. Your UDT must be a class, not a struct, of course. Your UDT must implement IBinarySerialize and use UserDefined serialization, because the base class does. Microsoft.SqlServer.Types allows partially trusted callers, so your assembly should be able to work with permission_set safe. Finally, remember that SQL Server does not allow you to expose overloaded methods in assemblies, although you can use them in your internal implementation.
I’m working to expand the stub implementation, so if anyone has some neat ideas for derived methods and properties, I’d be interested in hearing from you. And, when CTP5 comes out…GEOGRAPHY2 anyone? If it doesn’t do exactly what you want, change it.
By the way, I’ve wondered if deriving from a system data type isn’t analogous to using undocumented system stored procedures, a practice which everyone I meet seems to disapprove of, but they do it anyway. I’m think at this point that it’s more analogous to using a documented/supported system stored procedure, like sp_spaceused, in your own script. They can’t change HIERARCHYID post-SQL Server 2008 without break all existing code. So you’re safe, I think. Other opinions?