jace.bennett (7/18/2013)
I'm thinking these tables:Set(
Id,
Name,
Dimension,
...)
Person (
Id,
Name,
...)
SetRelationships (
ParentSetId,
ChildSetId
)
PersonSets (
PersonId,
SetId
)
And then these tables give me a lot of my queries directly. The ones I want to denormalize would be
PersonContainedBy(
PersonId
SetId
)
SetContainedBy(
SetId,
AncestorId
)
Which would be a flattening of the hierarchy described in the tables.
I.E. if A contains B, and B contains C per the tables, Then C would have records for A and B in the ContainedBy view.
I guess my question is if I build a materialized (or indexed) view over a Recursive CTE, how performant will that be? If I change the underlying data, will it have to recalculate the whole view, or just the changed records?
I guess I am missing why you think you need a recursive cte for this. Your data is denormalized so it would seem to eliminate the need for recursion.
Your definition of the problem is so vague that I am having a hard time envisioning what you are doing here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/