The efficiency of various SQL writing

Problem

This is often the case: the same purpose can be achieved with different SQL notation. A man who is in a dead end wants to understand that it is true, but what is the best? Here are some of my list, please enlighten the brothers. Please also ask the Brothers to list similar questions that you have encountered.

(1) Insert more than one data at a time:
CREATE TABLE TB (ID int, name NVARCHAR (30), note NVARCHAR (1000))
INSERT TB SELECT 1, ' DDD ', 1
UNION all SELECT 1, ' 5100 ', ' D '
UNION all SELECT 1, ' 5200 ', ' E '

You can also do this:
CREATE TABLE tb1 (ID int, name NVARCHAR (30), note NVARCHAR (1000))
INSERT TB1 (ID, name, remark) VALUES (1, ' DDD ', 1)
INSERT TB1 (ID, name, remark) VALUES (1, ' 5100 ', ' D ')
INSERT TB1 (ID, name, remark) VALUES (1, ' 5200 ', ' E ')
_________________________________
Which of the above two methods is efficient?

(2) When assigning value:
SELECT @a=n ' AA '
SET @a=n ' AA '
_________________________________
Which of the above two methods is efficient?

(3) When taking the first few data
SET ROWCOUNT 2 Select * from TB ORDER by FD
Select top 2 * out TB ORDER by FD
_________________________________
Which of the above two methods is efficient?

(4) When the condition is judged
where 0< (select COUNT (*) from TB where ... ）
where exists (SELECT * from TB where ...). ）
_________________________________
Which of the above two methods is efficient?

(5) The use of nullif-----> empathy its inverse function isnull
Update TB set fd=case when fd=1 then null else FD end
Update TB set FD=NULLIF (fd,1)
_________________________________
Which of the above two methods is efficient?

(6) When a substring is fetched from a string
SUBSTRING (' ABCDEFG ', 1, 3)
Left (' Abcderg ', 3) _
________________________________
Which of the above two methods is efficient?

(7) What is the difference between except and not?

(8) What is the difference between intersect and union?
Here is Sanda's answer:

(1) Insert more than one data at a time:

The 1th kind is better, but also has to have one, because the 1th kind of union all is as a statement whole, the query optimizer will try to do the optimization, also must calculate this result to insert again first.

2. If it is a single assignment, there is nothing good to compare.
However, if you are assigning values to more than one variable, I have tested that SELECT a one-time assignment is more efficient than assigning the set individually.

3. SET rowcount and top are the same, including the execution of the plan, etc. are all the same

4. This is generally exists fast, of course, the specific also depends on the condition of your subquery, whether it will refer to the column of the object in the outer query.
exists checks to have a value returns, and does not return the result set, the count needs to count all satisfies the condition, returns one result set, therefore generally exists fast.

5. It's supposed to be the same.

6. Basically the same

7. Except will repeat, not in no (unless you explicitly specify in select)
The column that except is used for comparison is all columns, unless you write a subquery limit column, not in does not
8. Intersect is a distinct value (intersection) of two queries, and union is all the distinct values (and sets) of two query results.