What is the difference between the set and select assignment modes in SQL ?, Sqlselect

What is the difference between the set and select assignment modes in SQL ?, Sqlselect

The variables in the T-SQL are divided into local variables and global variables, the use of local variables is first declared, and then assigned a value. Global variables are defined and maintained by the system, which can be used directly, but are generally not defined.

Local variable

In the T-SQL, the name of a local variable must be prefixed with a tag.

Declaer @ variable_name DataType

Where @ variable_name is the variable name DataType is the data type.

You can assign values to local variables by using the Set or Select statements.

Set @ variable_name = value

Or

Selecte @ variable_name = value

Ø the SET value assignment statement is generally used to assign data variables specified by variables.

The Select value assignment statement is generally used to query data in a table. If more than one record is queried, the value of the last record is assigned to the variable.

	Set	Select
Assign values to multiple variables at the same time	Not Supported	Supported
The expression returns multiple values.	Error	Assign the last returned value to the variable.
The expression does not return a value.	The variable is assigned a NULL value.	Keep original variable value

We recommend that you use Set because the Set statement has no results and consumes less resources. Therefore, it is more efficient to use Set when assigning values to a variable.

Note: SELECT can also assign the value of the scalar quantum query to the variable. If the scalar query does not return a value, the variable is SET to null (in this case, the value is exactly the same as that of the SET query ).
The concept of benchmarking quantum queries may be unfamiliar to everyone. The following is an example (compared with the green section above ):

Declare @ name varchar (128)
Set @ name = 'select initiator'
Select @ name = (select name from fuzhiTEST where id = 4)
Print @ name -- run correctly: displays NULL
GO