Shulou(Shulou.com)05/31 Report--

How to use SETNOCOUNT to optimize storage in SQLServer? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

How to set SETNOCOUNT in SQLServer to optimize storage

The help in MSDN is as follows:

SETNOCOUNT causes the returned results to contain no information about the number of rows affected by the Transact-SQL statement.

Syntax: SETNOCOUNT {ON | OFF}

Note:

When SETNOCOUNT is ON, no count is returned (representing the number of rows affected by the Transact-SQL statement). Returns a count when SETNOCOUNT is OFF.

The @ @ ROWCOUNT function is updated even when SETNOCOUNT is ON.

When SETNOCOUNT is ON, DONE_IN_PROC information for each statement in the stored procedure is not sent to the client. When you execute a query using the utilities provided by MicrosoftSQLServer, "nnrowsaffected" is not displayed in the query results at the end of Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

If some statements contained in the stored procedure do not return a lot of actual data, this setting can significantly improve performance because it greatly reduces network traffic.

The SETNOCOUNT setting is set at execution or run time, not at parse time.

Permissions: SETNOCOUNT permissions are granted to all users by default.

Conclusion: we should add words such as SETNOCOUNTON to the header of the stored procedure and SETNOCOUNTOFF when exiting the stored procedure in order to optimize the stored procedure.

How to set SETNOCOUNT in SQLServer to optimize storage

Description:

1: when viewing SQLServer help, we should pay attention to the "permissions" section, because some statements require certain permissions, and we often ignore them.

2:@@ROWCOUNT returns the number of rows affected by the previous statement, including the number of records found, the number of rows deleted, the number of records updated, and so on. Do not think that only the number of records found is returned, and @ @ ROWCOUNT should be immediately followed by the judgment statement, otherwise @ @ ROWCOUNT will return 0.

3: if you use a table variable, use an alias for the representative name in the conditional expression, otherwise the system will report an error.

4: there must be transactions in the operations of the CUD class.

5: the T-SQL statement (IF) used to check the @ @ ERROR system function using an error handler actually clears the @ @ error value in the process, can no longer capture anything but zero, and must use SET or SELECT to catch the error code immediately.

This is the answer to the question about how to use SETNOCOUNT to optimize storage in SQLServer. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.