SQL Server Performance

Improving preformance in derived tables?

Discussion in 'T-SQL Performance Tuning for Developers' started by asafrash, Aug 4, 2004.

  1. asafrash New Member

    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?

Share This Page