CODEWITHSUNDEEP
sqlsql-server
Accribus
Accribus

Reputation: 150

Derived table VS Subquery in SQL Server: pros and cons

I'm learning SQL Server and and interested in knowing what circumstances dictate the use of each concept. I have simple example below that would return the same data. My questions is, in real-life examples, how does one decided which technique to use?

select empName
, empTitle
, empID
from (select empName, empTitle, empID from tblManagers) as mgrs

versus:

select empName
, empTitle
, empID
from tblEmployees
where empID in (select empID from tblManagers)

Upvotes: 1

Views: 2633

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Sql server is smart enought, so first statement:

select empName
, empTitle
, empID
from (select empName, empTitle, empID from tblManagers) as mgrs

is converted to:

select empName
, empTitle
, empID
from tblManagers

There is one table scan. The second statement produces 2 table scans and then does nested loops for in operator. So the first statement is a winner hear.

As a recommendation you should always check on data. You should check execution plans. Shorter and good looking statement is not always the best. One and the same statement could give you different results on different data etc etc. It depends on many things.

You can see three statement along with their execution plans here http://sqlfiddle.com/#!6/2f53f/3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your two queries do different things, so I cannot comment on them.

In SQL Server, this is about three things -- none of which are performance related:

  • Do you have to use a CTE because you are using a recursive CTE?
  • Are you using the query-logic multiple times in the code?
  • Do you have a preference for one or the other?

SQL Server treats non-recursive CTEs and subqueries exactly the same way from a performance perspective. They both get inserted into the execution plan as "raw SQL". That is, there is no separate compilation or materialization of either one. As far as I know, there still isn't even a compiler hint for materialization.

CTEs generally have some advantages. You can refer to them multiple times in the same query, providing an opportunity to re-use code. For instance, I often have a structure where I define "constant" parameters for queries as:

with params as (select 'x' as val1)

Then I can cross join that into any subquery that I need, and the parameter only need to be changed once.

CTEs can refer to each other without nesting. This helps simplify the look of queries.

CTEs support recursion.

That said, I often use both.

Upvotes: 0

Related Questions