SQL Server Performance Forum – Threads Archive

Question about how an sql statement executes

I was curious if you’ve got a subquery in the where clause of another query (or a function) does that subquery or function get executed for each record that is searched through? Let’s say there’s 100 records in a table… will the subquery in the where clause get executed 100 times while searching through the records? Or will it execute once to get the result and then use that result throughout the search? What about if you have a subquery or function in the select clause? Rob Mills

---

Check the execution plan … Is it a correlated subquery? Are the columns that are being compared covered by indexes?

---

Well I’m still learning and until you asked I didn’t know what a correlated subquery was. But I looked up the definition which answers my question. No, it’s not a correlated subquery which tells me it only gets executed once. Thanks for your help! Rob Mills

---

Always check the execution plan. SQL Server may use a different approach than what your literal query statement implies – usually with good reason.

---

]]>