1.SET NOCOUNT ON is used in procedure for improve the performance of the store procedure.
put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.
This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.
If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
@Param1 = <Default_Value_For_Param1, , 0>,
@Param2=<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Improving SQL Performance
http://www.componentace.com/help/absdb_manual/increasesqlperformance.htm
http://wabdelwahab.wordpress.com/2007/07/29/how-can-you-increase-your-query-performance-in-sql-server/
ReplyDelete