SELECT SQL变量 - 我应该避免使用这种语法并始终使用SET吗？

This may look like a duplicate to here, but it's not. I am trying to get a best practice, not a technical answer (which i already (think) i know).

这看起来像是重复的，但事实并非如此。我想要获得最佳实践，而不是技术答案（我已经知道）。

New to SQL Server and trying to form good habits.
I found a great explanation of the functional differences between SET @var = and SELECT @var =
here: http://vyaskn.tripod.com/differences_between_set_and_select.htm
To summarize what each has that the other hasn't (see source for examples):

SQL Server的新手，并试图形成良好的习惯。我找到了SET @var =和SELECT @var = here之间功能差异的一个很好的解释：http：//vyaskn.tripod.com/differences_between_set_and_select.htm总结一下其他没有的东西（参见示例来源））：

SET:

组：

ANSI and portable, recommended by Microsoft.
ANSI和便携式，由Microsoft推荐。
SET @var = (SELECT column_name FROM table_name) fails when the select returns more then one value, eliminating the possibility of unpredictable results.
当select返回多个值时，SET @var =（SELECT column_name FROM table_name）失败，消除了不可预测结果的可能性。
SET @var = (SELECT column_name FROM table_name) will set @var to NULL if that's what SELECT column_name FROM table_name returned, thus never leaving @var at it's prior value.
SET @var =（SELECT column_name FROM table_name）将@var设置为NULL，如果这是SELECT column_name FROM table_name返回的内容，因此永远不会将@var保留在它的先前值。

SELECT:

选择：

Multiple variables can be set in one statement
可以在一个语句中设置多个变量
Can return multiple system variables set by the prior DML statement
可以返回先前DML语句设置的多个系统变量
SELECT @var = column_name FROM table_name would set @var to (according to my testing) the last value returned by the select. This could be a feature or a bug. Behavior can be changed with SELECT @j = (SELECT column_name FROM table_name) syntax.
SELECT @var = column_name FROM table_name会将@var设置为（根据我的测试）select返回的最后一个值。这可能是一个功能或错误。可以使用SELECT @j =（SELECT column_name FROM table_name）语法更改行为。
Speed. Setting multiple variables with a single SELECT statement as opposed to multiple SET/SELECT statements is much quicker. He has a sample test to prove his point. If you could design a test to prove the otherwise, bring it on!
速度。使用单个SELECT语句而不是多个SET / SELECT语句设置多个变量要快得多。他有一个样本测试来证明他的观点。如果您可以设计一个测试来证明其他情况，请将其打开！

So, what do i do?

那么，我该怎么办？

(Almost) always use SET @var =, using SELECT @var = is messy coding and not standard.

（几乎）总是使用SET @var =，使用SELECT @var =是凌乱的编码而不是标准。

OR

要么
Use SELECT @var = freely, it could accomplish more for me, unless the code is likely to be ported to another environment.

使用SELECT @var = free，它可以为我完成更多，除非代码可能被移植到另一个环境。

Thanks

谢谢

2 个解决方案

#1

Here is my opinion - use SET for simple operations such as SET @var = 'hardcoded_value' and use SELECT for doing tricker assignments such as from a table. I almost always end up writing select into variable statements in the following way to make my intentions clear to both the compiler and any other developers: SELECT TOP 1 @var = col_name FROM some_table

这是我的意见 - 使用SET进行简单的操作，例如SET @var ='hardcoded_value'，并使用SELECT进行tricker分配，例如从表中进行。我几乎总是以下面的方式将select写入变量语句，以使编译器和任何其他开发人员都明白我的意图：SELECT TOP 1 @var = col_name FROM some_table

If I was worried about portability I wouldn't be writing T-SQL and instead would stick with an ORM layer for data access instead.

如果我担心可移植性，我不会编写T-SQL，而是坚持使用ORM层来进行数据访问。

Edit, bonus tip: In SQL 08 I like using this syntax which is fairly terse for T-SQL:

编辑，奖金提示：在SQL 08中，我喜欢使用这种语法，这对T-SQL来说相当简洁：

DECLARE @var int = (SELECT col_name FROM some_table)

DECLARE @var int =（SELECT col_name FROM some_table）

#2

There's not much left to say as the article you mention yourself already covers it nicely.

没有太多可说的，因为你自己提到的文章已经很好地涵盖了它。

Are standards important to you? If your answer is 'yes', then you should be using SET.

标准对你很重要吗？如果您的回答是“是”，那么您应该使用SET。

As for my own standards, I don't really use one over the other, it depends on the alignment of the planets. After reading the article, I must confess I have a slight preference for SET.

至于我自己的标准，我并不真正使用一个，这取决于行星的对齐。阅读完文章后，我必须承认我对SET略有偏好。

#1