<p>I have a table in SQL server that has the normal tree structure of Item_ID, Item_ParentID.
Suppose I want to iterate and get all CHILDREN of a particular Item_ID (at any level).</p>

<p>Recursion seems an intuitive candidate for this problem and I can write an SQL Server function to do this. </p>

<p>Will this affect performance if my table has many many records? 
How do I avoid recursion and simply query the table? Please any suggestions?</p>

Perhaps some more detail is in order.
If you have a master-detail relationship as you describe, then won't a simple JOIN get what you need?
As in:
```
```
SELECT
  SOME_FIELDS
FROM
  MASTER_TABLE MT
 ,CHILD_TABLE CT
WHERE CT.PARENT_ID = MT.ITEM_ID
```
```