Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 217

However, this procedure can be rewritten as follows:
CREATE PROCEDURE saveBilling2 (@BillTS AS SMALLDATETIME,
@Amount AS SMALLMONEY,
@BillingID AS INT NULL) AS
SET NOCOUNT ON
UPDATE Billing
SET BillTS = @BillTS,
BillAmount = @Amount
WHERE BillingID = @BillingID
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Billing(BillTS, BillAmount) VALUES (@BillTS, @Amount)
END
By using the @@ROWCOUNT system variable you've eliminated the SELECT COUNT(*)... query from the
first procedure. Some argue that saveBilling2 will run an UPDATE statement, which won't update
any rows, thus causing the procedure to run two queries. This is true, however, if the UPDATE
does update rows then the INSERT statement will be skipped altogether. In short, there's a chance
that saveBilling2 will only run one SQL statement, however, saveBilling will always run two
SQL statements.
Temp Tables versus Table Variables
Another technique to minimize database access is using table variables instead of temporary tables.
Temporary tables, which are preceded by # or ## are stored in tempDB. They are physically created
and stored. Table variables are also created in tempDB. However their creation is not part of a user
transaction. They also require fewer locks and recompilations. Thus, they can perform much better than
temporary tables.
There are a few issues with table variables which would make them less optimized than temporary
tables. First, if there is sufficient memory pressure, the table variable will be stored on disk. Also, other
than defining a primary key, table variables can't be indexed. Temporary tables can have all the same
features and options that a regular table can have. A temporary table can have multiple indexes.
Minimizing Cursor Usage
Another technique for stored procedures is one that minimizes the use of cursors. Cursors are fully
explained in BOL. SQL Server is geared to operate on sets of data (rows). However, sometimes you may
have occasion to operate on a single row at a time. In that instance a cursor can be used. Note, however,
that operating on one row at a time is inefficient. So, whenever possible use SQL Server's natural ability
to operate on sets of data, instead of a single row at a time.
Naming Convention for Stored Procedures
Finally, a technique for enhancing stored procedures has to do with their names. A common convention
for naming stored procedures is one that includes a prefix of sp_. This should be avoided for stored
procedures you create. The reason is that this prefix is treated specially. Whenever SQL Server receives a
command to execute a stored procedure that begins with sp_ , it will first look in the master database for
the procedure. If it's not found, the engine will then search in the connected database for the procedure.