SQL Server Performance Forum – Threads Archive

Improving preformance in derived tables?

Does anyone know the action scheme when performaing a select in derieved tables?
example… declare @Test table (num int name varchar(50) )
insert into @Test
select 1,’ryan’
union
select 2,’Tom’
union
select 3,’gerry’
union
select 4,’mark’
…..lets say this table has 1000000 values first option for derived table…
select x.name from
(
select num as num,name as name from @Test
)
where x.num<100000 is this example equal in runtime approach to select num as num,name as name from @Test
where num<100000 ill try to explain my point ….
in the first example does sql server perform the inner select first
(for all 1000000 results), and then on these results does the second outer select
or
the where clause in the second outer select limits the results in the inner select? which is the case?

---

]]>