When to use SET and SELECT?

Question

I know that as in virtually all issues related to "either" there must be situations where it is best to use SET or SELECT, in a Procedure (for example).

At work we always wear the same SELECT, even to assign value to only one variable, eg:

@declare @variavel varchar(2)
select @variavel = 'ok'

This is best practice or because it is a simple assignment would be better to use SET? Is there any performance/memory advantage in using SELECT in situations like this?

Answer 1

I always had this doubt, but I never researched and even this happens a lot to me. No Stackoverflow in English has this topic explaining when to use one and the other, the main differences are:

1. SET is ansi standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can do several assignments at the same time.
3. If assigned from a query, SET can only assign a value scale. If the query returns multiple values/lines then SET an error may occur. SELECT shall assign one of the values to the variable and will hide the fact that several values have been returned (then, probably would never know why something was wrong in another place)
4. When assigning from a query if there is no value returned the SET assign NULL, where SELECT will not make the assignment (therefore, the variable will not be changed from its previous value). See code below
5. With regard to differences in performance there are no differences direct between SET and SELECT. To SELECT ability to do various assignments in a single action gives a slight advantage of performance on SET.

Translating into item 3 code:

Take a test yourself through the code below.

declare @var varchar(20)
set @var = 'Joe'
set @var = (select Campo from SuaTabela)
select @var 

select @var = Campo from SuaTabela
select @var

The first code will return the following error message:

Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.

The second will return you a value.

Translating into code item 4:

declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var Agora é null */
--SAÍDA NULL

set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty' -- AQUI NÃO É ATRIBUÍDO NULL
select @var
--SAÍDA Joe

It is quite common to use in the system where I work SELECT in FUNCTIONS to concatenate comma-separated values instead of displaying one per line.

In practice

Imagine that I wish to know all the emails of your customers and these should come separated by comma in a single result.

One FUNCTION that would solve your problem will be something like:

CREATE FUNCTION [dbo].[fncEmails](@IDCliente int)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @RETORNO VARCHAR(MAX)

        SELECT @retorno = COALESCE(@retorno + ', ', '') + Email
        FROM Clientes 
   RETURN @retorno
END

Upshot:

'[email protected], [email protected], [email protected]'

Sqlfiddle

Answer 2

For working with mysql, adopted the following practices also in sql server:

• utilise set for assigning value to control variables;
• utilise select for other cases.

In other words, when I need to initialize a variable that is used to control a loop, for example, I use the set (ex set @contador = 0); where the allocation depends on any consultation at the base, use the select (ex select @contador = count(*) from usuarios). As the difference in performance is practically non-existent, there are no problems in the execution.

Note that you could use the set even with basic consultations (ex set @contador = (select count(*) from usuarios)), but I believe it becomes more complex for reading.

Answer 3

I’ve always had the impression that the use of SET for simple assignments was faster, because it believed that by not involving database mechanisms were simpler and faster execution.

But in tests I did in loops where I put three commands SET and a command SELECT with the 3 assignments, the performance of the second was 30% better.