T-SQL: Variable Assignment Using SET vs. SELECT

Use SET instead of SELECT to make a variable assignment because it is ANSI standard and recommended in SQL Server doc.

References:

• SET @local_variable (Transact-SQL)
• T-SQL: SET vs SELECT when assigning variables
• Differences between SET and SELECT in SQL Server

-- You could assign variable with SELECT.
DECLARE @total_rows INT = 0;
SELECT @total_rows = COUNT(*) FROM customer;
PRINT @total_rows;

-- However, it is better to use SET.
DECLARE @total_rows INT = 0;
SET @total_rows = (SELECT COUNT(*) FROM customer); -- Parentheses around SELECT statement is required.
PRINT @total_rows;