Love using ctes, perhaps too much. Yet getting rid of scalar function calls in our sql codebase made these a necessity it was either that or table values functions-which are great if you need the content in multiple places and wish to preserve the rules used in one place.
A good case for me is the current quote system on a project I'm doing right now. I needed to get the rules and calculations exposed in a single query. The only way I could do that, making it readable as well, was one with about 6 Ctes. Several hold summation tables, another holds valid quotes and lines - I don't need to select out non deleted items more than once, I just use a CTE called validQuotes.
As ever I have to look at the query plan, see if anything looks super slow and rewriting. Generally I get excellent performance.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Love using ctes, perhaps too much. Yet getting rid of scalar function calls in our sql codebase made these a necessity it was either that or table values functions-which are great if you need the content in multiple places and wish to preserve the rules used in one place.
A good case for me is the current quote system on a project I'm doing right now. I needed to get the rules and calculations exposed in a single query. The only way I could do that, making it readable as well, was one with about 6 Ctes. Several hold summation tables, another holds valid quotes and lines - I don't need to select out non deleted items more than once, I just use a CTE called validQuotes.
As ever I have to look at the query plan, see if anything looks super slow and rewriting. Generally I get excellent performance.