SQL Server 2000 Performance Guidelines - Stored Procedures

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman


Use Set NOCOUNT ON in Stored Procedures

Use the SET NOCOUNT ON statement to prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure. For example, if you have eight operations in a stored procedure and you have not used this option eight messages are returned to the caller. Each message contains the number of affected rows for the respective statement.


Do Not Use the Sp_ Prefix for Custom Stored Procedures

SQL Server always looks in the master database for a stored procedure that begins with the sp_ prefix. SQL Server then uses any supplied qualifiers such as the database name or owner. Therefore, if you use the sp_ prefix for a user-created stored procedure, and you put it in the current database, the master database is still checked first. This occurs even if you qualify the stored procedure with the database name. To avoid this issue, use a custom naming convention, and do not use the sp_ prefix.

Personal tools