If all we're comparing is the effectiveness of two SQL commands, measure them on the server. Why would network latency, network traffic load, web server traffic load, etc., matter...

I think these two are redundant. Just use the second one, and it will handle the first one.

Don't use "if (select count(*)...) > 0". Use "if exists (select 1 ...)". Performs better. Less steps for the server.

If you're using domain authentication for the connection to the server (Windows security), you can include Where statements in the function that use the User_ID to determine what rows to...

I modified your function to use Int instead of UniqueID, since my hierarchy table uses that for the node ID. Otherwise, ran it as-is.

Fastest run time was 5434 milliseconds....

RyanRandall (4/22/2008)

---

GSquared (4/22/2008)

---

Ryan, that will work if you want to get rid of them completely. What's needed here is eliminating duplicates, but keeping one per set. For that,...

Ryan, that will work if you want to get rid of them completely. What's needed here is eliminating duplicates, but keeping one per set. For that, it needs...

John, I'd be interested in seeing that function, if you can post it. I've done a lot of tests on hierarchical data, and in my tests, the CTE out-performs...

Okay, now that I got the regex working (more accurately, now that I stopped making stupid mistakes while trying to use it), I got some figures on speed:

On the table...

Matt: I must be doing something wrong with the regex. It doesn't appear to handle the situation in your last post. I posted how I put it together...

What I mean by undo commands, is that you can build a proc that will take the data out of the log table and put it back into the main...

Jeff, what I'm saying is that, while I can get a major speed boost in a database by doing things like allowing read uncommited, or dropping locks and transactions by...

Art, it seems to me you could include your filter statements in the Where clause of the insert, and then select the whole table. That would save the database...

Jeff Moden (4/20/2008)

---

john.arnott (4/18/2008)

---

It's been suggested that "COALESCE" is preferable to ISNULL.

It allows for a list of arguments, the first of which evaluates to not null being returned.

Coalesce is ANSI...

Matt: You got a book!?!? I'm so jealous!

I built an Access database (I had to look up the word "relational" in a dictionary to find out what the...