Remixed

Aha! Found the original thread:

http://www.sqlservercentral.com/Forums/Topic896583-338-1.aspx

The explanation in the second thread is still worth a read though.

What do you mean just an APPLY? 😛

It...

Jeff,

I feel so much better now I have cost you some time writing that full explanation :laugh:

No, seriously, I hear what you're saying about the recent posts on hierarchies -...

Adding...

I would add a "SET NOCOUNT ON;" to the top of the procedure to suppress DONE_IN_PROC messages.

Also, can we see the C# code to call the procedure please?

I should point...

Demonstration code as promised:

IF OBJECT_ID(N'dbo.Employee', N'U')

IS NOT NULL

DROP TABLE...

Jeff Moden (5/15/2010)

---

Better yet... I'm going to bow out of this thread. I swore to myself that I wasn't going to show any of my new methods until PASS......

Still not quite sure why I'm doing this, but anyhow, here's the conversion code:

DECLARE @UserRegistration

TABLE...

8kb (5/15/2010)

---

Thanks for taking the time to write up this explanation. I'm going to try the OPTION (RECOMPILE) as you suggested. I think a bigger problem is the requirement to...

Thanks. For the benefit of others, it was a cool-looking report.

Jeff Moden (5/15/2010)

---

Don't be too sure... First, as good as it is (well done, Paul), Paul's code didn't convert the Adjacency List to the Hierarchical Path that HierarchyID needs. ...

Mahbub,

That's really good news - and full credit to Peter for the time he spent on this too.

It's great to see the reports, but there is some pretty sensitive personal...

8kb (5/12/2010)

---

When I try to get the most maximum ping_time of every device, SQL Server will not use the non-clustered index but does a clustered index seek instead. Even if...

I'm not saying any of this is great design, but you can do this sort of thing directly using LIKE and PATINDEX.

The following code snippet demonstrates the general idea:

DECLARE @Lookup

TABLE...