Is the query I wrote not doing what you need?

;with

HierarchyDown (ID, ParentID) as

(select individualid, parentid

from dbo.Table

where individualid = @InputParam_in

union all

select t2.individualid, t2.parentid

from dbo.Table t2

inner join HierarchyDown hd

on t2.parentid = hd.id),

HierarchyUp (ID, ParentID) as

(select individualid, parentid

from dbo.Table

where individualid =...

Having one index with both might help. Adding them to other indexes for your queries would probably be even better.

Very important in all cases.

It should be somewhere between 1 and 100.

In other words, it depends on your data.

As Michael said: More likely to reduce performance than increase it in your case.

It depends.

If you have a lot of selects on certain conditions, then a materialized view based on those conditions can increase performance for those selects.

On the other hand, materialized views,...

SSIS is great for that. For scheduled data transfers between servers, SSIS would be very high on my list of potential tools.

It depends on the amount of data and the connection speed, really.

Is it possible it's a concurrency issue?

Your idea is a good one. You might even look into using an indexed view instead to populate the temp table, to improve performance.

There's no performance difference between the two.

I think your sample with Left and Right may be missing the cast to varchar inside the Left and Right functions, from what you...

In SQL 2008 you could use the DateOffset type, but there really isn't a way to do that in SQL 2000/2005, without building a complex control of your own.

You could...

;with Last7 (Date) as

(select getdate() -1

union

select getdate() -2

union

select getdate() -3

... -- up to 7)

select Date

from Last7

left outer...

If I'm reading your request correctly, it seems to me that a copy of the database for each period would be a bit of overkill. How about one copy...

Is there a reason to not move the UDF's logic into that proc? That would solve this whole problem, but you'll have to make sure it won't cause other...