Displaying tree path of record in SQL Server 2005
- by jskiles1
An example of my tree table is: ([id] is an identity)
[id], [parent_id], [path]
1, NULL, 1
2, 1, 1-2
3, 1, 1-3
4, 3, 1-3-4
My goal is to query quickly for multiple rows of this table and view the full path of the node from its root, through its superiors, down to itself. The ultimate question is, should I generate this path on inserts and maintain it in its own column or generate this path on query to save disk space? I guess it depends if this table is write heavy or read heavy.
I've been contemplating several approaches to using the "path" characteristic of this parent/child relationship and I just can't seem to settle on one. This "path" is simply for display purposes and serves absolutely no purpose other than that. Here is what I have done to implement this "path."
AFTER INSERT TRIGGER - requires passing a NULL path to the insert and updating the path for the record at the inserted rows identity
INSTEAD OF INSERT TRIGGER - does not require insert to have NULL path passed, but does require the trigger to insert with a NULL path and updating the path for the record at SCOPE_IDENTITY()
STORED PROCEDURE - requiring all inserts into this table to be done through the stored procedure implementing the trigger logic
VIEW - requires building the path in the view
1 and 2 seem annoying if massive amounts of data are entered at once.
3 seems annoying because all inserts must go through the procedure in order to have a valid path populated.
1, 2, and 3 require maintaining a path column on the table.
4 removes all the limitations of the above but require the view to perform the path logic and requires use of the view if a path is to be displayed.
I have successfully implemented all of the above approaches and I'm mainly looking for some advice. Am I way off the mark here or are any of the above acceptable? Each has it's advantages and disadvantages.
