EDIT:  As Tara points out:

Vyas did this test quite some time ago: http://vyaskn.tripod.com/differences_between_set_and_select.htm Either I never read it, or I forgot I read it.  Well hopefully I pulled some different points together differently here than Vyas did, and at the very least, I hope I made my feeling clear about a program that has to loop over 2 million times.  Thanks for the heads up Tara...                                     

OK, this always comes up from time to time, and it always seem that people are both sides of the fence. "SET is faster because....", "No, SELECT is faster because".  Well there should be no debate about it, yet I've failed to find a definitive explanation of what the true story behind this is.  In any case I've wanted to look into this.

In this thread the question comes up again.  In that thread, Peter posts a link to a SQL Server Magazinee Article that discusses this topic.  While it was a good read, I had a hard time buying it.  The author (who is listed as "Reader") posted that in using SET, it is optimized and when a single value is set that this is more effecient.  They then go on to say that after 1 million iterations with multiple value stes, SELECT was 59% more effecient for each operation.  Now the fact that they are doing checks between each assignment seems to cause a potential unexpected interference by SQL server to the outcome of the results.  Now, in above article, the first line states "Loops are fairly common in SQL Server stored procedures. ", which in itself is sort of a red herring, since if you are coding that way, I would sugest that you step back and rethink your process.  If you can't find a set based solution for 99% for what you have to do, then drop me a line,  or head on over to SQL Team.

So I set forth for my own test.  I did my tests with an undisturbed loop where dattime values were grabbed before and after the loops of pure sets.  I did for 1,000, 10,000, 1,000,000 and 10,000,000.  Looking Kalen's book (Inside SQL Server 2000), she has a chapter th differences between the two, but nothing about performance.  I'll need to google around some more, however,  there must be an explanation about the internals.  I did the test for Multiple variable assignments, and another set for single variable assignments.  For the 1 million iteration (and it's really 2 million assignments)  I got the following:

SELECT_MS_Multiple  SET_MS_Multiple
------------------                  ---------------
43470                              202963

SELECT_MS_Single   SET_MS_Single
----------------                    -------------
42453                             45746

Now in both Cases, SELECT wins, in the case of Multiple assignments, SELECT seems to blow SET's doors off.  Now, I need to reiterate this again.  If you are finding that you have a process that needs to loop 1 million times, you either are backed into a corner due to previous developement that can't be changed, you've run into the 1% of the time that you have to, or you have a flawed application design. 

If Anyone sees anyuthing wrong with this test, or  if anyone has any comments I would look forward to it.  With all that said, I use SELECT almost exclusively.  Here's the code:

DECLARE   @SET1 int,      @SELECT1 int,      @SET2 int,           @SELECT2 int
DECLARE   @SET3 int,      @SELECT3 int,      @SET4 char,          @SELECT4 char
DECLARE   @SET5 char,     @SELECT5 char,     @SET6 char,          @SELECT6 char
DECLARE   @SET7 datetime, @SELECT7 datetime, @SET8 datetime,      @SELECT8 datetime
DECLARE   @SET9 datetime, @SELECT9 datetime, @SETA varchar(8000), @SELECTA varchar(8000)

DECLARE @x int, @s1 datetime, @s2 datetime, @e1 datetime, @e2 datetime, @c int
DECLARE @s3 datetime, @s4 datetime, @e3 datetime, @e4 datetime

SELECT @x = 1, @s1 = getDate(), @c = 1000000

WHILE @x < @c
  BEGIN
 SELECT    @SELECT1 = 1
  , @SELECT2 = 2
  , @SELECT3 = 3
  , @SELECT4 = 'a'
  , @SELECT5 = 'b'
  , @SELECT6 = 'c'
  , @SELECT7 = '2001-09-11'
  , @SELECT8 = GetDate()
  , @SELECT9 = '1999-12-31'
  , @SELECTA = 'This is a test of the emergency Broadcationg System.  This is only a test'

 SELECT    @SELECT1 = 0
  , @SELECT2 = 0
  , @SELECT3 = 0
  , @SELECT4 = ''
  , @SELECT5 = ''
  , @SELECT6 = ''
  , @SELECT7 = ''
  , @SELECT8 = 0
  , @SELECT9 = 0
  , @SELECTA = ''
 
 SET @x = @x + 1
  END

SELECT @x = 1, @s2 = getDate(), @e1 = getDate()

WHILE @x < @c
  BEGIN
 SET       @SELECT1 = 1
 SET   @SELECT2 = 2
 SET    @SELECT3 = 3
 SET   @SELECT4 = 'a'
 SET   @SELECT5 = 'b'
 SET   @SELECT6 = 'c'
 SET   @SELECT7 = '2001-09-11'
 SET   @SELECT8 = GetDate()
 SET   @SELECT9 = '1999-12-31'
 SET    @SELECTA = 'This is a test of the emergency Broadcationg System.  This is only a test'

 SET       @SELECT1 = 0
 SET   @SELECT2 = 0
 SET    @SELECT3 = 0
 SET   @SELECT4 = ''
 SET   @SELECT5 = ''
 SET   @SELECT6 = ''
 SET   @SELECT7 = 0
 SET   @SELECT8 = 0
 SET   @SELECT9 = 0
 SET    @SELECTA = ''
 
 SET @x = @x + 1
  END

SELECT @e2 = getDate()