SQL在所有查询中将noCount设置为on

Question

我有一个存储的程序，如下所示：

@userInput int

as
declare isSuperUser int
declare IDs table(
recordID int
);

set isSuper = select isSuper from [User]
if(@isSuper = 1)
Begin
    insert into recordID select * from TableA
End
else
Begin
    insert into recordID select * from TableB
End

select * from recordID

我想在每个查询中添加"set noCount on“，所以问题是:存储procedureA中的性能是否等同于存储procedureB？

商店procedureA：

@userInput int

as
declare isSuperUser int
declare IDs table(
recordID int
);
set noCount on
set isSuper = select isSuper from [User]
if(@isSuper = 1)
Begin
    insert into recordID select * from TableA
End
else
Begin
    insert into recordID select * from TableB
End

select * from recordID
set noCount off

商店procedureB：

@userInput int

as
declare isSuperUser int
declare IDs table(
recordID int
);
set noCount on
set isSuper = select isSuper from [User]
set noCount off
if(@isSuper = 1)
Begin
    set noCount on
    insert into recordID select * from TableA
    set noCount off
End
else
Begin
    set noCount on
    insert into recordID select * from TableB
    set noCount off
End
set noCount on
select * from recordID
set noCount off

Answer 1

SET NOCOUNT ON可防止为存储过程中的每个语句向客户端发送DONE_IN_PROC消息。对于包含多个不返回太多实际数据的语句的存储过程，或者对于包含Transact-SQL循环的过程，将SET NOCOUNT设置为ON可以显著提高性能，因为网络通信量大大减少。

TechNet

当storedProcedure正在执行时，没有将数据发送回客户端的意义，设置noCount On可以提高性能，因为它减少了网络流量。在您的存储过程B中，您正在将NoCount on切换到off，这会增加网络流量。