SQL writing Efficiency

Q:
(1) When multiple data entries are inserted at a time:
Create Table Tb (ID int, name nvarchar (30), remarks nvarchar (1000 ))
Insert TB select 1, 'ddd ', 1
Union all select 1, '20140901', 'D'
Union all select 1, '20140901', 'E'
You can also do this:
Create Table tb1 (ID int, name nvarchar (30), remarks nvarchar (1000 ))
Insert tb1 (ID, name, remarks) values (1, 'ddd ', 1)
Insert tb1 (ID, name, remarks) values (1, '201312', 'D ')
Insert tb1 (ID, name, remarks) values (1, '20140901', 'E ')
_________________________________
Which of the above two methods is efficient?
A:
1st is better, but there must also be a amount of control, because the 1st types of Union all are as a whole statement, the query optimizer will try to optimize, at the same time, you also need to calculate the result before inserting it.
Q:
(2) assignment:
Select @ A = n''
Set @ A = n'a'
_________________________________
Which of the above two methods is efficient?
A:
If it is a single assignment, there is nothing to compare.
However, if you assign values to multiple variables, it is better to assign values one by one using the SELECT statement ..
Q:
(3) When obtaining the first few data records
Set rowcount 2 select * from TB order by FD
Select Top 2 * from TB order by FD
_________________________________
Which of the above two methods is efficient?
A:
Set rowcount and top are the same, including the execution plan.
Q:
(4) condition judgment
Where 0 <(select count (*) from TB where ......)
Where exists (select * from TB where ......)
_________________________________
Which of the above two methods is efficient?
A:
This is usually because exists is fast. Of course, it depends on the subquery conditions behind you to see whether the columns of objects in the outer query will be referenced.
Exists checks that a value is returned, and no result set is returned. Count counts all the conditions that meet the condition and returns a result set. Therefore, exists is faster in general.
Q:
(5) Use of nullif -----> Similarly, use of 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?
A:
It should be the same
Q:
6) when a substring is obtained from a string
Substring ('abcdefg', 1, 3)
Left ('abcdef', 3 )_
________________________________
Which of the above two methods is efficient?
A:
Basically the same
Q:
(7) What is the difference between memory T and not in?
A:
Distinct T will be repeated, not in won't (unless explicitly specified in select)
When T is used to compare all columns, not in does not exist unless the subquery restricts columns.
Q:
(8) What is the difference between intersect and union?
A:
Intersect is the non-repeating value (intersection) of both queries, and union is the non-repeating value (union) of the two query results)