1.
Try to use stored procedures instead
of bulky queries.
2.
Try to avoid using temporary tables inside your stored
procedures.
3.
Try to avoid using DDL statements inside your stored
procedure.
4.
Try to use “schema name” with “object
name”.
For examples as,
n
SELECT
* FROM [dbo].[Users] -- Preferred this method.
Instead of below
n
SELECT
* FROM Users – Try to avoid this
method.
n
--For calling stored procedure with name
like,
n
EXEC [dbo].[uspGetUser] -- Preferred this method.
n
--Instead of
n
EXEC uspGetUser – Try to Avoid this method.
5.
Try to call stored procedures using
their fully qualified name.
For
examples as,
n
Use
IF EXISTS (SELECT 1) instead of (SELECT *)
n
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Users' AND type = 'U')
6.
Try to use SQL Server “Profiler” to determine which stored
procedures have been recompiled too often.
7.
Try to Include the SET NOCOUNT ON statement in your stored
procedures to stop the message indicating the number of rows affected by SQL
statement.
8.
Try to consider returning the integer
value as a RETURN statement instead
of returning an integer value as part of a RecordSet.
9.
The “sp_” prefix stands for System Procedure, and it should not be used
as prefix for regular procedures.
Do
not use the prefix “sp_” prefix in
the stored procedure name, if you need to create a stored procedure to run in a
database other than the master database.
n Few
examples could be,
i.
spInsertUser
ii.
uspInsertUser
iii.
usp_InsertUser
iv.
InsertUser
n Application
prefix_ operation prefix_ description of database objects
involved.
n Operation
prefixes we use -
i.
“get”
– Returns a RecordSet
ii.
“ins”
– Inserts data
iii.
“upd”
– Updates data
iv.
“del”
– Deletes data
10. Try
to use the “sp_executesql” stored
procedure instead of the EXECUTE
statement.
For example as,
n
DECLARE
@Query VARCHAR(55)
n
DECLARE
@Age INT
n
SET
@Age = 32
n
SET
@Query = 'SELECT ID,Name,Age,CellNo FROM [dbo].[Users] WHERE Age = '
+ CONVERT(VARCHAR(3),@Age)
n
EXEC (@Query)
11. Use
the “sp_executesql” stored procedure
instead of temporary stored
procedures.
12. If
you have a heavy stored procedure,
you will need to break down into the several sub-procedures and call them from
a controlling stored procedure.
13. Keep
the Transaction as short as possible.
14. Try
to use TRY-Catch for error handling.
For Example as,
BEGIN
TRY
--TODO: Your T-SQL code.
END
TRY
BEGIN
CATCH
--TODO: Your error handling code.
END
CATCH
Stayed Informed - Error Handling [TRY CATCH] with Example
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|