You're trying to build a "Parent/Child" table, which is frequently referred to as an "Adjacency List". I see by your comments that it's going to be used for file directory structures in some way.
I also see that you're setting yourself up to perpetuate the need for RBAR to traverse the directory structure. That means that you're actually missing a few columns if you want whatever project you're working on to result in a very high performance solution and so we can't yet recommend any indexes because the columns you actually need indexes on are missing.
Please see the following articles on a very high performance hybrid method not only for making it so queries against the hierarchy run insanely fast, but maintenance of the hierarchy is easy and even more insanely fast.Hierarchies on Steroids #1: Convert an Adjacency List to Nested SetsHierarchies on Steroids #2: A Replacement for Nested Sets Calculations
Also and just in case you don't know (many don't), be careful where you use the tokens of "foo" and "bar" in any of your discussions or code because you might not know what the source of those names are but someone you speak to may. They're a derivative the pronunciation guide (fo͞obär) of the military slang term "FUBAR", which is a NSFW (Not Safe For Work) term especially in the presence of people that are sensitive to swear words. I won't even use the terms in code and especially not as examples of code (and, yes, I was in the military).
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
When you put the right degree of spin on it, the number 318
is also a glyph that describes the nature of a DBAs job. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs