Vyas did this test quite some time ago: http://vyaskn.tripod.com/differences_between_set_and_select.htmEither 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:

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:

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'