Lukk: definitely agree with you. One of my biggest concerns when I left the job where I built this hybrid hierarchy was whether or not anyone left behind would be able to manage it. I documented the heck out of it, asked them if they were comfortable with it, got reassurances that either they'd figure it out or they'd call me (and pay consulting rates) if they needed help. Not a very good sign, I have to say.

Unfortunately, that company didn't have any 2008 servers, and had only vague plans about maybe obtaining some one day in the indeterminate future, so that wasn't really an option. The stuff in this article is moderately complex, but not horribly so, and doesn't depend on a proprietary data type nor on features that most companies don't have yet.

danielk1 (1/28/2009)I just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.

Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.

That is a very good point Daniel and I would definitely consider that if I knew there is a chance the app will be ported to other DB servers, otherwise I would definitely go for hierarchyID (if it proves to be fast enough).

This discussion also applies to app architecture - e.g. should we have a separate DAL in the project if we know that we will only use MS Sql Server?

ventsislav.mladenov (1/28/2009)Or you could use CLR store procedures which are very fast in execution and sometimes are faster than T-SQL.http://blog.vmladenov.com/?p=197

Since you say "Now the fact is that the C# store procedure is faster than T-SQL code. I’m wondering why but this is the result." in you blog, I'd like to see the T-SQL method code and test data you tested against. I'm always willing to learn something new.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

danielk1 (1/28/2009)I just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.

Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.

My 2cents.

Just double checking... You've never used and will never use CONNECT BY until it becomes part of the ISO Standard. And you never used DATEDIFF until it became part of the standard. And you've never used GETDATE() because it's not part of the standard.

Here we go again... I can understand that some GUI code might be made to follow ISO standards (heh, depending on which release you're talking about), but since none of the RDBMS vendors are 100% compliant with ISO standards and the very standards themselves tend to lag what's available as powerful extensions to ANSI SQL, I'll just go ahead and start a war by saying that true code portability in the batch world is a myth.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

Just double checking... You've never used and will never use CONNECT BY until it becomes part of the ISO Standard. And you never used DATEDIFF until it became part of the standard. And you've never used GETDATE() because it's not part of the standard.

Actually, no. I leave this to the code to handle.

The DBAs in all the companies I work for, demand us to follow all ISO standards without deviation.

Moreover, the applications I have written, both GUI and Web, are 100% portable to many of the top databases available including: MS SQL, MySQL, DB2, Oracle, and others (tested and implemented).

I will not debate the issue as I am not saying I am right nor am I saying I am wrong - it is just one way of solving an issue. The article and others like it are of course another way. In IT, there are many ways to solve a given issue, some better than others.

With this said, if performance using a non-ISO standard was significantly improved, the DBAs and other Stake Holders would have to evaluate their thinking and perhaps make an exception, however, as of now, performance is virtually instant and there are no areas to improve, so we will remain with the ISO standards.

Why have standards if they are not to be followed and adhered? Sure, they may become outdated. No one is saying they are perfect and even if they were, in six months there will likely be something else better, different, or faster, requiring the standards to be revisited, invoking a new standard.

Ah... so you're not a batch programmer. And that's the area of the code I was talking about. GUI programmers have all sorts of tools like Hibernate, etc, to write "portable" code... well, at least DB "agnostic" code so far as the GUI is concerned.

It would be really interesting to see if any of your DBA's use GETDATE() or any of the other "proprietary" code offerings of SQL Server. I'd have to say that the first time they reference Master.sys.sysColumns, they're probably in violation. :)

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

I have to admit, my code is usually about as portable as the Rocky Mountains. In other words, you could move it to another database engine in geologic time spans, but not much faster than that.

I also have to admit that I've seen projects that were "completely standard and portable", where someone actually did try to move it to another RDBMS, and spent more time debugging because of partially documented differences, undocumented differences, variations in standards compliance in the RDBMS itself, etc., than they would have spent building the thing over from scratch.

I have yet to see a case where code portability actually solved anything worthwhile for anyone. Doesn't mean they don't exist, just means I haven't seem them, in my limited experience.