Results 1 to 9 of 9

Thread: SET vs SELECT

  1. #1
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94

    Red face Unanswered: SET vs SELECT

    Whileiwas going through the guidelines for DB programming on my project, i came across the following guideline.

    Do not use SET,instead use SELECT for setting the value for a variable.

    example
    Instead of,
    SET @Variable='Value'

    Use
    Select @Variable='Value'.

    I'm bit curious to know what difference will it make in terms of performance of the stored procedure.


    Please enlighten my soul.
    In GOD we believe. Everything else we Test!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I have seen it written recently that declaring variables for the purpose of manipulating the data in the variable before using it in your query is less advantagious for performance than just using the stored procedure argument and the manipulation inline in the same query.

    The details I am not smart enough to remember.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I would be very surprised if there is any perf difference between SET and SELECT.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least based on my experience, using SET is faster than using SELECT for simple assignments. SELECT is SQL's "800 poiund gorilla", it carries a lot of extra baggage. Using SELECT for an assignment is like using a firetruck to get a drink of water... It will certainly do the job, but it is overkill.

    -PatP

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Thrasymachus
    I have seen it written recently that declaring variables for the purpose of manipulating the data in the variable before using it in your query is less advantagious for performance than just using the stored procedure argument and the manipulation inline in the same query.

    The details I am not smart enough to remember.
    I am not kidding. It is in the Inside SQL 2K5 T-SQL Programming book. I have it in my backpack. If I have time at lunch I will dig up the reasoning.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Pat Phelan
    At least based on my experience, using SET is faster than using SELECT for simple assignments. SELECT is SQL's "800 poiund gorilla", it carries a lot of extra baggage. Using SELECT for an assignment is like using a firetruck to get a drink of water... It will certainly do the job, but it is overkill.

    -PatP
    I guess I am in good shape then, because I always use SET for this purpose, probably because it's faster to type

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    At a neophyte's conceptual level, I would hazard a guess that SET is faster because it's not creating query plans, passing to the optimizer, executing set based logic etc etc. SET just says, "hey, the variable in this address? Yeah... make it this value"

    **disclaimer: Last time I hazarded a guess from a purely conceptual level I was grossly mistaken.**
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I bet strictly speaking SET is faster than SELECT. But I am quite certain that if you are manipulating that variable it is better to trash the varibale and do it inline in your query.

    soooo bored today. got stuff to do but i have been conditioned to the point, if it ain't an emergency, who cares?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is the best Set Vs select article I know.
    http://vyaskn.tripod.com/differences...and_select.htm

    Google "Set Vs select" "select Vs Set" "SQL Server" and you will you get loads of hits. One is not better than the other (though only one is ANSI) but there are a couple of features\ gotchas worth baring in mind.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •