Storing Trees in Sql Server to handle parent child hierarchy

Consider a table category

id name parentid
1 Php 0
2 Perl 0
3 ASP 0
4 Php Script 1
5 Php code 1
6 perl Script 2
7 Asp script 3
8 Asp code 3


If you want to retrieve childs of a particular parent in a tree hierarchy then you have to perform left join of same table

select p.name as parentname , c.id as childid,c.name as childname from category p left join category c on p.id=c.parentid where p.id=1;

Result:
+------------+---------+-----------+
| parentname | childid | childname |
+------------+---------+-----------+
| PHP | 4 | Php Script|
| PHP | 5 | Php code |
+------------+---------+-----------+

If you want to retrieve childs below retrieved 'Php script' and 'Php code' then you have to perform two left join to retrieve the result. Inorder to reduce this complexity I am
going to explain how to store the tree hierarchy in the table itself. For this purpose u have to maintain two extra fields depth and nodes

Consider a simple example

id parentid depth name nodes
1 0 0 Php /1/
2 0 0 Perl /2/
3 0 0 ASP /3/
4 1 1 Php Script/1/4/
5 1 1 Php code /1/5/
6 2 1 perl Script/1/6/
7 3 1 Asp script /1/7/
8 3 1 Asp code /1/8/

With the help of nodes field you can easily find the list of childs below each parents.
For example all childrens of below Php Script will have node value starting from /1/4/.

Downside

In the above example the number of nodes are less so it is easy to enter the nodes value manually. If nodes are in hundreds and above then it is complex to enter the nodes value, so there a three option to reduce this problem

1. Depth and nodes is automatically calculated when ever we query the table.
2. use Triggers to recalculate these whenever table is modified(update, insert or delete operation is done).
3. Combination of two.