* to 1

  • Can I replace the * with 1 here in the below code. Will it impact the result.

    IF EXISTS (SELECT * FROM dbo.tbTest where Test_ID = @Test_ID)  -- Like change to SELECT 1

  • Hi,

    both will work perfect. There is no difference wrt performance also. I have seen in some community posts that select 1 is faster than select * but if you see the execution plan in both the cases same. So it’s your choice if you use * or 1 with the EXISTS clause

     

  • The engine is smart enough that either is fine.  You could take a look at the execution plans and statistics on both versions of your query.

     

  • You can replace the * with whatever you want (often people will use 1 or NULL yes). It shouldn't, however, have any effect on the performance however.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • rishabhdg wrote:

    Hi,

    both will work perfect. There is no difference wrt performance also. I have seen in some community posts that select 1 is faster than select * but if you see the execution plan in both the cases same. So it’s your choice if you use * or 1 with the EXISTS clause

    Not referring to the current case here but (just to say it out loud) the comparison of execution plans (even actual plans) shouldn't actually be used to determine which code is actually better because even the actual execution plans are riddled with sometime seriously wild estimates.

    Don't mistake that for me making a recommendation to not use execution plans to help write the best code possible.  Just don't use execution plans as a measurement of which code will actually perform better.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The SELECT statement is ignored.  This can be seen by running the following query, where the query runs without producing a "divide by zero" error.

    IF EXISTS ( SELECT 1/0 FROM (VALUES(1)) v(n))
    SELECT GETDATE()

    I prefer using the *.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply