Databases Reference
In-Depth Information
Stored Procedure Standards
When developers are ready to create their stored procedures, the DBA should take the time to review
their code and ensure that it follows documented standards, including proper formatting, good usage,
and good design. If problems are identified, the DBA must notify the developers, explaining the need for
the change. This provides an opportunity to review and optimize the code before it causes a production
problem. The following sections offer some guidelines on creating stored procedures.
Keep Them Small
Keep stored procedures as small as possible. If necessary, create several small procedures that are called
from one ''driver'' procedure.
When calling a stored procedure from your application, it is important that you call it using its fully
qualified name, such as
exec database_name.dbo.myProcedure
instead of
exec myProcedure
.
Using fully qualified names helps to eliminate any potential confusion about which stored procedure
you want to run, which prevents bugs and other potential problems. More important, doing so enables
SQL Server to access the stored procedure's execution plan more directly, which in turn speeds up the
performance of the stored procedure.
''DBO'' As Object Owner
In SQL Server 2000, the schema was synonymous with the owner. Unless unavoidable, all objects within
a database should be owned by dbo; if they are not, SQL Server must perform name resolution on the
objects if the object names are the same but the owners are different. When this happens, SQL Server
cannot use a stored procedure using the existing execution plan. Instead, it has to recompile and execute
the procedure.
Beginning with SQL Server 2005, the behavior of schemas changed. Schemas are no longer equivalent to
database users. Each user has a default schema, which can be set and changed. Therefore, defining all
objects in the dbo schema is not always appropriate. Because any given database user might belong to a
different default schema, it's a good idea to always refer to tables, views, stored procedures, and so on
by schema name.
Use Comments Generously
Using comments helps others understand the code clearly, and it won't affect performance. Changes to
database objects made by the developer should be logged with comments. The following listing shows
an example:
------------------------------------------------------------------------------------
--Object Name:
uspGetPatientRate
--Author:
MAK & Yan
--Created Dt:
--Function: Provide information for Rate info Screen
--**********************************************************
-- Modification Log
--***********************************************************
Search WWH ::
Custom Search