This was really good!
Thanks, Joe. I appreciate you stopping by especially with the code to convert Nested Sets to an Adjaceny List.
I would drop the needlessly proprietary code (ISNULL? We have COALESCE today, CAST not CONVERT, AS not = for aliases, etc).
I appreciate your thoughts on that but as soon as you use a variable (or just about anything else of particular good use), you lose portability no matter which database engine you're writing code for. Besides true portability being a myth, I've never believed in trying to make code truly portable because you can miss out on a lot of the horsepower of some of the extensions to SQL. For example, I used the proprietary ISNULL because it's a bit faster than COALESCE for several reasons. I also intentionally used the proprietary UPDATE to avoid doing multiple table scans during multiple updates to do what could be done in a single scan to create the Left and Right Bowers. Most other engines won't allow you to update a variable and a column at the same time and that's a real shame but I'm still going to use the technique in SQL Server because it can be used to easily avoid multiple and, therefor, unnecessary table scans.
I also use the alias = expression form of assignment for several reasons... it's the same form as the UPDATE statement in virtually any SQL engine and it makes the code a whole lot easier to read because the all-important column alias will always start at character 9 in my formatted code. No need to look for it at the ragged-right end of multiple length expressions to find the target.
To wit, even you write non-portable code. For example, the very first piece of code you offer in your fine book titled "Trees and Hierarchies in SQL for Smarties" (1st edition) has the following code on page 6.
DECLARE [font="Arial Black"]ARRAY [/font]GraphList
[font="Arial Black"]OF RECORD [/font][edge CHAR(1), in_node INTEGER, out_node INTEGER];
ANSI or not, that's not truly portable code because it doesn't port to SQL Server. ANSI means nothing when it comes to portability except maybe for simple CRUD procs (which are normally taken care of by various ORMs now-a-days) because no database engine is 100% ANSI compliant even on such simple things as how to declare a variable. I rue the day that they ever are because there would be no competition to improve. 😉 In fact, ANSI has adopted many things as “ANSI Standard” that were once proprietary extensions to various dialects of SQL.
Heh… and let’s see you port a trigger between Oracle and SQL Server with no changes. Now THAT’s some serious “fun”!
The same is true on the Oracle side of the fence (for example). If someone were to try something similar to this article in Oracle, I'd tell them not to bother because it's nearly as (or maybe even more so) efficient to use CONNECT BY for such things. If it turned out that I could get even more speed using something proprietary to Oracle, I'd use that instead because we're not playing around with 10 or even 10 thousand rows. When you work with millions of rows, every microsecond you can save per "function" per row adds up in a hurry.
Like I said, I appreciate your thoughts on the subject but will continue to use and advertise the power of the SQL extensions for whatever database engine I happen to be working in to get the best performance possible. Besides, I've only had to port code once since 1995 and it just isn't that difficult to do. It's just not worth crippling the performance of code by avoiding the proprietary extensions just on the very remote chance that you might have to port code someday.
BWAAA-HAAAA!!! Imagine it if someone told you you couldn't use the code from the previously cited page 6 because it wouldn't port to SQL Server. That would be a fun moment to watch! :w00t:
Got features? Use them! 😉
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)