Edited, try that again now...

Sounds like HierarchyID is exactly what you are after

http://technet.microsoft.com/en-us/library/bb677290.aspx

Sounds like you are doing a lot of guess work to identify the issue.

Run a server side trace capturing RCP:Completed and SQLBatch:Completed events with ( at very least the cpu...

Indeed Lynn is right, cost relative to batch is really "Estimated cost relative to cost of Estimated Batch".

If the actual io is not similar to the estimated io then...

That sums up the sqlbits experience 😉

Also "many joins" is different if you:

a join b

b join c

c join d

d join e

e join f

f join g

or

a join b

a join...

From a default point of view, this should present no problems.

First though you need to ensure that design of the tables is 'correct' and properly normalised.

Indexing and statistics will...

Hi,

So just to confirm,

this is a parentt-child hierarchy and you wish to start at leaf nodes and work back to root ?

What would you expext to happen if the row...

Hi ,

this is because you are also grouping on WEEK

GROUP BY StockCode, [Week]

Remove that and you should be ok

Dave

Hi ,

To my mind these are two fundementally different queries.

Just because you have a sequential transactionID which means you happen to get the same result, this is an unfair...

Happy new year to all the threadzians, have a good 2013.

Just out of interest, what business problem are you fancing in the real world ?

This sort of problem always strikes be as

a) an academic exercise

b) a poor database...

Indeed there are

->-> VP Dev Dept

->->-> Team Lead Team1

...

Hi there,

Firstly there is quite a big descrepancy between estimate and actual row counts throughout the plan this (normally) points to out of date statistics.

use "UPDATE STATISTICS" to...

Hi,

you seem to be missing some very important data here.

your xml shows that "Brenda Smith" is a child of "Alexander Tuckings", but why ?

What data specifies that relationship...