Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've searched the internet and get to this question. It's exactly describe my needing, only difference is that i need to store that settings in sql server database.

How can i implement that in SQL Server?

Whenever i select setting for level1-child1 i want all overridden settings from child1 plus inherited ones form level1. for example:

level1: max-value:100; min-value:10; enable-alarm:true; level-child1: min-value:30;

after select: level1-child1: max-value:100; min-value:30; enable-alarm:true;

Currently i use hierarchy path for implementing hierarchies.


share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You don't give a lot of detail on your current structure, so I'm assuming each node in the hierarchy is in a simple table with a single parent relationship like so and the path being a string of ID like /<rootNodeID>/<GPNodeID>/<PNodeID>/<LeadNodeID> and that you intend to store the settings in a property bag arrangement rather than needing to update the DB structure when a new setting is added to the applcation logic, resulting insomething like this:

Nodes                 NodeSettings
=============         ============      Settings
NodeID   (PK) <--.--  NodeID  (FK)      =========
ParentID (FK) ---'    Name    (FK) ---> Name (PK)
FullPath              Value             

In this arrangement you can pull all settings that have a value anywhere up the tree from a given location (with values closer like so:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 ns.value
                FROM   NodeSetting ns
                JOIN   Nodes n ON n.NodeID = ns.NodeID AND '<PathToTargetNode>' LIKE n.FullPath+'%'
                WHERE  ns.Name = s.Name
                ORDER BY LEN(n.FullPath) DESC
FROM   Settings s

The use of a sub-query like this my not be efficient enough if you have many settings but whould be fine otherwise. Of more concern is using LIKE that way: it will not be able to properly use any index on Nodes.FullPath so an index scan (or worse, a table scan) could be performed for each call of the sub-query. Off the top of my head I can't think of a way to significantly optimise this as a single query without adding new structure to your storage of the relationships between nodes such as keeping map between ascendants and descendants like:

Ascendant  (FK->NodeID)
Descendant (FK->NodeID)

(making sure to store the node's relationship with itself, as AscNode='NodeID', DescNode='NodeID', Distance=0). Then you can do:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 s2.value
                FROM   RelationshipDistance r
                JOIN   Settings s2 ON n.NodeID = s.NodeID AND s2.Name = s.Name
                WHERE  r.Descendant = '<IDOfNodeYouAreLookingFor>'
                ORDER BY r.Distance DESC
FROM   Setting s

The disadvantage here is needing to maintain the extra structure (through triggers or work in another layer of your code).

Of course this is assuming that you want to return the settings from a single statement. You could always define a procedure that walks up the path collecting the settings in a temporary table or table variable, adding new settings found at each step towards the root - this is likely to be more efficient than a single statement version that causes index/table scans.

EDIT: Updated first example to more normal form so it does not require an extranious distinct. See revision history for the difference.

share|improve this answer
Thanks for your solution –  r.zarei Oct 15 '13 at 15:55
thanks again. The query for the first method has a minor bug, i think it should have distinct in select [distinct] name .... –  r.zarei Oct 21 '13 at 16:26
Ah yes, that would be a problem with the design: the settings should be in their own table with the many-to-many relationship between settings and nodes held separately. Adding distinct "fixes" the output but it would be better to fix the structure so I'll update the answer when I find time later. –  David Spillett Oct 22 '13 at 8:18
I've used first approach;currently i have somthing about 1000 settings.getting setting for a hierarchy takes 0.1~0.3ms (with extra joins), can i improve it? can we use HierarchyId datatype with provided by sql server –  r.zarei Oct 23 '13 at 8:53
Most of out clients are still running SQL2005 so I've not had chance to play with HierarchyId types yet, so possibly but I can't currently help you there. Also, as stated in the full answer, the second technique is expected to be significantly faster as in the first you are most likely index scanning the nodes for each setting. You migth find the updated first technique quicker as it will find the right rows instead of finding many duplicates and throwing the extras away with distinct. –  David Spillett Oct 23 '13 at 10:27
show 2 more comments

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.