• CraigIW (2/10/2010)


    nicholasw (2/10/2010)


    Instead of:

    IF EXISTS(SELECT 1 FROM ......WHERE....)

    Would the following be quicker still?

    IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)

    No, as the "top 1" is only done after the "select 1 from".

    Funny enaugh, it is not always that simple. I seen cases where a:

    select top 1 max(id)

    outperformed

    select max(id)

    many times over, and I suspect top sometimes acts as a undocumented hint. I got some testing to do on it still, and if it pans out it might also be a trick to control table variables better by using top to provide an estimate other then the constant 1 and thus get better query plans. It could also be just a side effect of the optimizer assigning costs to each operation and in rare cases come to a different plan due to a slightly higher ** estimate ** of the costs. In any case, it does not hurt to test both methods if you see a query perform worse then you anticipated.