Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the better way (with regards to performance) to set a value to variable?

  1. By SET command:

    DECLARE @VarString nvarchar(max);
    SET @VarString = 'john doe';
    SELECT @VarString;
    
  2. By SELECT command:

    DECLARE @VarString nvarchar(max);
    SELECT @VarString = 'john doe';
    SELECT @VarString;
    
share|improve this question
1  
Using SELECT is not semantic, but i'm curious to know if it's faster than SET. –  Rafael Kassner May 11 '12 at 16:49
1  
You may want to look into this stackoverflow.com/questions/189588/… –  SQL Learner May 11 '12 at 17:05
    
@RafaelKassner - SELECT is faster when assigning values to multiple variables at once. Otherwise, the performance difference is negligible. –  Nick Chammas May 11 '12 at 21:35
    
@SQLLearner - That report gives inconclusive results. First the poster states that SET is faster, then half-way down he adds: "Oddly, if you crank the number of runs up to say, 10, the SET begins to lag behind." –  Nick Chammas May 11 '12 at 21:48
add comment

3 Answers

up vote 7 down vote accepted

Don't pick one or the other strictly for performance.

Pick based on what fits your style and development needs, as is advised in the conclusion to this excellent comparison between SELECT and SET (emphasis and minor formatting added):

Best practice suggests not to stick to one method. Depending on the scenario you may want to use both SET or SELECT.

Following are few scenarios for using SET:

  • If you are required to assign a single value directly to variable and no query is involved to fetch value
  • NULL assignments are expected (NULL returned in result set)
  • Standards are meant to be follow for any planned migration
  • Non scalar results are expected and are required to be handled

Using SELECT is efficient and flexible in the following few cases:

  • Multiple variables are being populated by assigning values directly
  • Multiple variables are being populated by single source (table , view)
  • Less coding for assigning multiple variables
  • Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed
share|improve this answer
add comment

I think the consensus is there's a negligible performance difference between the two (with SET being faster). But if you are like me, I'd use SET where appropriate for readability purposes. The next guy who will maintain your code will thank you for that ;-)

share|improve this answer
    
Can you post benchmarks proving that SET is faster? –  AlexKuznetsov May 11 '12 at 21:12
    
Alex, please take a look at this as an example stackoverflow.com/questions/189588/… –  MarlonRibunal May 11 '12 at 21:16
    
Marlon, that report gives inconclusive results. Note that half-way down the poster notes that: "Oddly, if you crank the number of runs up to say, 10, the SET begins to lag behind." –  Nick Chammas May 11 '12 at 21:40
    
That's the reason why my answer is leaning towards "readability purposes". The use case scenario in your answer are great points. –  MarlonRibunal May 11 '12 at 21:53
add comment

I always use SET unless I am populating multiple variables from the result set of 1 query. That way, by using SELECT we only query the table once.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.