jace.bennett (7/18/2013)
Yup that just about does it I think. Thanks so much for that.Particulars of the projection aside, my original question was about how this style query plays with views. I want the results of that query indexed, and kept largely in sync with the adjacency list (learned a new term, thanks, guys). What is my best option? Will a View over that query be performant in the face of the occasional update? Or will it have to rerun the entirety of the query and recreate the index? Should I just run the projection on a schedule or try to work out something with triggers?
The rCTE I provided against your adjacency list will start being less performant if you have many levels in the hierarchy. But if you say, flatten your hierarchy this way in an overnight job, putting those results into another table, that might not work out too bad for you.
I guess it depends on how often you need to access the results of the rCTE.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St