performance - Speed of SELECT vs. SET in T-SQL

Question

I've been led to believe that for single variable assignment in T-SQL, set is the best way to go about things, for two reasons:

• it's the ANSI standard for variable assignment
• it's actually faster than doing a SELECT (for a single variable)

So...

SELECT @thingy = 'turnip shaped'

becomes

SET @thingy  = 'turnip shaped'

But how fast, is fast? Am I ever really going to notice the difference?

Answer 1

SET is faster on single runs. You can prove this easily enough. Whether or not it makes a difference is up to you, but I prefer SET, since I don't see the point of SELECT if all the code is doing is an assignment. I prefer to keep SELECT confined to SELECT statements from tables, views, etc.

Here is a sample script, with the number of runs set to 1:

SET NOCOUNT ON

DECLARE @runs int
DECLARE @i int, @j int
SET @runs = 1
SET @i = 0
SET @j = 0

DECLARE @dtStartDate datetime, @dtEndDate datetime


WHILE @runs > 0
    BEGIN
        SET @j = 0
        SET @dtStartDate = CURRENT_TIMESTAMP
        WHILE @j < 1000000
            BEGIN
                SET @i = @j
                SET @j = @j + 1
            END
        SELECT @dtEndDate = CURRENT_TIMESTAMP
        SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SET_MILLISECONDS


        SET @j = 0
        SET @dtStartDate = CURRENT_TIMESTAMP
        WHILE @j < 1000000
            BEGIN
                SELECT @i = @j
                SET @j = @j + 1
            END
        SELECT @dtEndDate = CURRENT_TIMESTAMP
        SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SELECT_MILLISECONDS

        SET @runs = @runs - 1
    END

RESULTS:

Run #1:

SET_MILLISECONDS

5093

SELECT_MILLISECONDS

5186

Run #2:

SET_MILLISECONDS

4876

SELECT_MILLISECONDS

5466

Run #3:

SET_MILLISECONDS

4936

SELECT_MILLISECONDS

5453

Run #4:

SET_MILLISECONDS

4920

SELECT_MILLISECONDS

5250

Run #5:

SET_MILLISECONDS

4860

SELECT_MILLISECONDS

5093

Oddly, if you crank the number of runs up to say, 10, the SET begins to lag behind.

Here is a 10-run result:

SET_MILLISECONDS

5140

SELECT_MILLISECONDS

5266

SET_MILLISECONDS

5250

SELECT_MILLISECONDS

5466

SET_MILLISECONDS

5220

SELECT_MILLISECONDS

5280

SET_MILLISECONDS

5376

SELECT_MILLISECONDS

5280

SET_MILLISECONDS

5233

SELECT_MILLISECONDS

5453

SET_MILLISECONDS

5343

SELECT_MILLISECONDS

5423

SET_MILLISECONDS

5360

SELECT_MILLISECONDS

5156

SET_MILLISECONDS

5686

SELECT_MILLISECONDS

5233

SET_MILLISECONDS

5436

SELECT_MILLISECONDS

5500

SET_MILLISECONDS

5610

SELECT_MILLISECONDS

5266