For my next test, to get rid of some of the RBAR (though not eliminating it completely), I added an ID column to the table, and then ran:

;with

CTE1 (ID,...

(Had to split this post in half to avoid an error on the boards. Not sure why.)

Results:

===================================================

Proc 1 Run 1

===================================================

SQL Server parse and compile time:

CPU...

Jeff Moden (3/18/2008)

---

Sorry Gus... I actually didn't mean it that way (better)... I just meant it's different and you don't need the CTE if you don't want it. They...

I have a test box. Actually, have one at work and one at home. Trust me, I blow them up regularly. Ended up with a test of...

Glad you got it working.

praseodymiumm (3/18/2008)

---

Thank GSquared! I really appreciate your help.

If I could ask another question?

These queries will be used as data sources to build OLAP cubes (Cognos Transformer). Depending on which...

srienstr (3/18/2008)

---

69.5% of the total batch cost for the derived table method according to the execution plan, but only 31.8% of the runtime (according to the getdate() queries I inserted...

jdoconsulting (3/18/2008)

---

Does anyone have a good example of using a CTE to rollup assembly costs in a nested Bill of Materials? It seems to me that this should be done...

Tao:

The clarity thing is totally a personal preference. To me, being able to look at the query at the end, especially in things like complex updates, and have that...

Adam Haines (3/17/2008)

---

Actually you can click on Tool --> --> options --> scripting. From here you can set the default to script does not exists, script indexes, foreign keys...

For me, the certs are going to be more of a means of filling in the (huge) gaps in my knowledge.

I'm totally self-trained on this, and it's mainly been by...

Thanks Jeff. That's definitely better. (As mentioned, mine was thrown together and tested against one case.)

paulsasik (3/18/2008)

---

The query provided by Tao is fine and was the first solution i thought of when i read the example problem. However, the CTE example will effortlessly resolve the...

Tao Klerks (3/18/2008)

---

SELECT TOP 1 dept_id, n

FROM (

SELECT dept_id, count(*) AS n

FROM employee

GROUP BY dept_id

)...

Tao Klerks (3/18/2008)

---

...My instinct would be: Try to rewrite your statement to use joins and derived tables rather than subqueries, before moving to CTEs as a way of simplifying your...