Try this query without the joins first:
select
level1.res_name as level1
from resource as level1
where isnull(level1.man_id,0)=0
This returns the rows where man_id isn't referencing anything. In other words, this returns the topmost-level managers only. For your particular example it works like this:
the FROM clause returns the entire table:
level1.Res_id level1.Res_name level1.Man_id
------------- --------------- -------------
1 sam 3
2 Biju 4
3 adrian NULL
4 Helen 3
5 Micah 4
the WHERE clause leaves just one row:
level1.Res_id level1.Res_name level1.Man_id
------------- --------------- -------------
3 adrian NULL
finally, the SELECT takes from it only one column:
level1
------
adrian
Adding one self-join like in Chris's query,
select
level1.res_name as level1,
level2.res_name as level2
from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
where isnull(level1.man_id,0)=0
gives you every top-level manager's direct subordinates. More specifically, the join results in the following row set:
level1.Res_id level1.Res_name level1.Man_id level2.Res_id level2.Res_name level2.Man_id
------------- --------------- ------------- ------------- --------------- -------------
1 sam 3 NULL NULL NULL
2 Biju 4 NULL NULL NULL
3 adrian NULL 1 sam 3
3 adrian NULL 4 Helen 3
4 Helen 3 2 Biju 4
4 Helen 3 2 Biju 4
5 Micah 4 NULL NULL NULL
The WHERE clause filters it down to just this:
level1.Res_id level1.Res_name level1.Man_id level2.Res_id level2.Res_name level2.Man_id
------------- --------------- ------------- ------------- --------------- -------------
3 adrian NULL 1 sam 3
3 adrian NULL 4 Helen 3
And finally the SELECT clause returns only the names:
level1 level2
------ ------
adrian sam
adrian Helen
In the same manner, adding one more join, like this:
select
level1.res_name as level1,
level2.res_name as level2,
level3.res_name as level3
from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
where isnull(level1.man_id,0)=0
brings about the next level of subordinates:
this is the result of joining the third instance of the table to the results of the first join:
1.Res_id 1.Res_name 1.Man_id 2.Res_id 2.Res_name 2.Man_id 3.Res_id 3.Res_name 3.Man_id
-------- ---------- -------- -------- ---------- -------- -------- ---------- --------
1 sam 3 NULL NULL NULL NULL NULL NULL
2 Biju 4 NULL NULL NULL NULL NULL NULL
3 adrian NULL 1 sam 3 NULL NULL NULL
3 adrian NULL 4 Helen 3 2 Biju 4
3 adrian NULL 4 Helen 3 5 Micah 4
4 Helen 3 2 Biju 4 NULL NULL NULL
4 Helen 3 5 Micah 4 NULL NULL NULL
5 Micah 4 NULL NULL NULL NULL NULL NULL
(I've shortened the table aliases for convenience: 1 stands for level1, 2 for level2, and 3 for level3)
this is what remains after the WHERE filter:
1.Res_id 1.Res_name 1.Man_id 2.Res_id 2.Res_name 2.Man_id 3.Res_id 3.Res_name 3.Man_id
-------- ---------- -------- -------- ---------- -------- -------- ---------- --------
3 adrian NULL 1 sam 3 NULL NULL NULL
3 adrian NULL 4 Helen 3 2 Biju 4
3 adrian NULL 4 Helen 3 5 Micah 4
and this is what SELECT extracts from the above and gives you back:
level1 level2 level3
------ ------ ------
adrian sam NULL
adrian Helen Biju
adrian Helen Micah
Same continues for the rest of the joins. With your example, however, there will be no more rows, as the hierarchy in the table doesn't go deeper than two levels. Consequently, the other two columns in Chris's original query, level4 and level5, will return NULLs:
level1 level2 level3 level4 level5
------ ------ ------ ------ ------
adrian sam NULL NULL NULL
adrian Helen Biju NULL NULL
adrian Helen Micah NULL NULL
If Chris's assumption that four joins should be enough for most cases does not work for your particular case (and I don't mean the simple example in your question, of course), you can throw in more self-joins using the same pattern.
I'd stay away from the scripting and detach/attach methods. I've done two migrations in the past nine months and was convinced that a better method would be to backup and restore the databases. That way you have a last-known good configuration point-in-time restore.
So here's my thought process.
- Restart the source instance in single-user mode
- Backup your databases
WITH COPY_ONLY, COMPRESSION
- Stop the old instance. Take it offline. Pull the network cables, if you must.
- Restart the new instance in single-user mode
- Restore
master
- Change the location of
tempdb (see below)
- Restore
model and msdb
- Restart the instance.
- Restore your user databases.
Please note that you will need to do some cleanup on the instance names since you're moving from what I assume will be a named instance from your Multi-Instance Cluster to what I assume will be the default instance on a dedicated piece of hardware.
I may have misunderstood what you meant by "replicated." Do you mean that you're going to provision LUNs of the same size (you've already mentioned letters) on your SAN that you attach to new hardware? In this case, you're not describing a DR scenario at all. You're simply describing the provisioning of hardware for your new SQL instance. Disaster recovery would be how you plan to recover from the failure of these instances.
Naturally you know that you're moving away from a High Availability solution. I hope you're not relying on your SAN to do both HA and DR.
Best Answer
It may be to do with the new cardinality estimator. Try running the query with this disabled. Also try the FORCE ORDER hint, eg
I would also ask what foreign keys, indexes and constraints do you have in place?