Store Procedure Nubie 1

[DotNetBlocks]

Hello All,
I am new to store procedures, and i was wondering if there was good primer on how to write them effectively?

Any help would be greatly appreciated.

Thanks,
Babloome

 

[LakshmiKiran]

SQL Server Stored Procedures Optimization Tips

Here are twelve helpful tips for ensuring that you've constructed your SQL Server stored
procedures to perform in the most efficient manner possible.

1. Use stored procedures instead of heavy-duty queries.

This can reduce network traffic, as your client will send to the server only the stored procedure
name (perhaps with some parameters) instead of all the text from a large heavy-duty query.
Stored procedures can be used to enhance security and conceal underlying data objects as well.
For example, you can give the users permission to execute the stored procedure to work with
restricted sets of columns and data.

2. Include the SET NOCOUNT ON statement in your stored procedures to stop the message
indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic due to the fact that your client will not receive the message
indicating the number of rows affected by a Transact-SQL statement.

3. Call stored procedures using their fully qualified name.

The complete name of an object consists of four identifiers: the server name, database name,
owner name, and object name. An object name that specifies all four parts is known as a fully
qualified name.

Using fully qualified names eliminates any confusion about which stored procedure you want to
run and can boost performance because SQL Server has a better chance to reuse the stored
procedures execution plans if they were executed using fully qualified names.

4. Consider returning the integer value as a RETURN statement instead of returning an integer
value as part of a recordset.

The RETURN statement exits unconditionally from a stored procedure, so the statements following
RETURN are not executed. Though the RETURN statement is generally used for error checking,
you can use this statement to return an integer value for any other reason. Using the RETURN
statement can boost performance because SQL Server will not create a recordset.

5. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored
procedure to run in a database other than the master database.

The prefix "sp_" is used in the system stored procedures names. Microsoft does not
recommend using the prefix "sp_" in user-created stored procedure names as SQL Server
always looks for a stored procedure beginning with "sp_" in the following order:
the master database, the stored procedure based on the fully qualified name provided,
followed by the stored procedure using dbo as the owner (if one is not specified).

When you have the stored procedure with the prefix "sp_" in a database other than master,
the master database is always checked first. If the user-created stored procedure has
the same name as a system stored procedure, the user-created stored procedure will
never be executed.

6. Use the sp_executesql stored procedure instead of the EXECUTE statement.

The sp_executesql stored procedure supports parameters. So, using the sp_executesql
stored procedure instead of the EXECUTE statement improves readability of your code
when many parameters are used.

When you use the sp_executesql stored procedure to execute a Transact-SQL statement
that will be reused many times, the SQL Server query optimizer will reuse the execution
plan it generates for the first execution when the change in parameter values to the
statement is the only variation.

7. Use the sp_executesql stored procedure instead of temporary stored procedures.

Microsoft recommends using temporary stored procedures when connecting to earlier
versions of SQL Server that do not support the reuse of execution plans. Applications connecting
to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure
instead of temporary stored procedures in order to have a better chance of reusing the execution
plans.

8. If you have a very large stored procedure, try to break down the stored procedure into
several sub-procedures, and call them from a controlling stored procedure.

The stored procedure will be recompiled when any structural changes are made to a table or
view referenced by the stored procedure (an ALTER TABLE statement, for example), or when
a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored
procedure. So, if you break down a very large stored procedure into several sub-procedures,
there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures
will not.

9. Try to avoid using temporary tables inside your stored procedures.
Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

10. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

11. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your
query will vary each time it is run from the stored procedure.

The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server
does not cache a plan for this procedure and the procedure is always recompiled at run time.
Using the WITH RECOMPILE option can boost performance if your query will vary each time it is
run from the stored procedure, because in this case the wrong execution plan will not be used.

12. Use SQL Server Profiler to determine which stored procedures have been recompiled too often.

To check if a stored procedure has been recompiled, run SQL Server Profiler and choose to trace
the event in the "Stored Procedures" category called "SP: Recompile". You can also trace the
event "SP: StmtStarting" to see at what point in the procedure it is being recompiled. When you
identify these stored procedures, you can take some correction actions to reduce or eliminate the
excessive recompilations.


Few useful stored procedures
sp_enumerrorlogs
=================
This stored procedure lists all the error log files present in the current server.

<Usage>
sp_enumerrorlogs


2. sp_enumOleDBDataSources
============================
Lists all the visible OleDB datasources available to this server.

<Usage>
sp_enumOleDBDataSources


3. sp_gettypeString
===================
Gets the textual representation of the datatype

<Usage>
Declare @tabID Int, @typeString Varchar(30)
Select @tabID = OBJECT_ID('Authors')
Exec sp_gettypeString @tabID, 1, @typeString OUTPUT
Select @typeString

4. sp_MScheck_uid_owns_anything
===============================
This indeed is an interesting SP which returns 1 if the user owns any object and returns 0 when the user owns nothing.

<Usage>
Declare @Result Int, @UID Int
Select @UID = USER_ID()
Exec @Result = sp_MScheck_uid_owns_anything @Uid
Select @Result

5. sp_MSforEachDB
================
This is one of the interesting SPs I’ve used. It lists all the databases available.
In the example below you can see that I first list all the databases available.
And then in the next example use the same with some DBCC command for the entire database:

<Usage>
sp_MSforEachDB 'Print 'DB Name : ?''

OR

sp_MSforEachDB 'DBCC CheckDB(?)'


6. sp_MSforEachTable
==================
This is yet another cool feature. Use this SP to do an operation on each and every table in the connected database.

<Usage>
sp_MSforEachTable @command1 = 'Exec sp_help [?]'

7. sp_MSget_qualified_Name
This stored procedure gives you the fully qualified representation of the object.

<Usage>
Declare @ObjID Int, @ObjectName Varchar(50)
Select @ObjID = Object_ID ('Authors')
Exec sp_MSget_qualified_Name @ObjID, @ObjectName OUTPUT
Select @ObjectName

8. sp_MSis_pk_col
=================
Checks if the column entered is the primary column or not. 1 indicates Yes and 0 indicates no.

<Usage>
Declare @result Varchar(30)
Exec @result = sp_MSis_pk_col 'authors', 'au_id' , 1
Select @result

9. sp_MSloginmappings
====================
This utility SP is used to get all the logins available in the system and lists login, databases, user and alias mappings.

<Usage>
sp_MSloginmappings

10. sp_MStable_has_unique_index
=============================
This stored procedures allows you to find if the object ID passed has an unique index defined.

<Usage>
Declare @result Int, @ObjID Int
Set @ObjID = Object_ID('Authors')
Exec @result = sp_MStable_has_unique_index @ObjID
Select @Result

11. sp_MStablerefs
=================
Lists all the tables that reference the passed table name.

<Usage>
sp_MStablerefs 'titles'


12. sp_MStablespace
==================
Shows the space occupied by the table in the database.

<Usage>
sp_MStablespace 'Authors'

13. sp_readerrorlog
==================
This command is in continuation of the first command where we read all the available error logs.
Here we go ahead and read the error logs from the system. This is similar to using master..xp_readerrorlog.

<Usage>
sp_readerrorlog 2

14. sp_tempdbspace
==================
We need to understand that tempdb is a special kind of database as compared to other databases.
You can see the space utilization of this database using this stored procedure.

<Usage>
sp_tempdbspace