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

DeleteUID varchar(30) NULL,
DeleteTS smalldatetime NULL,
PRIMARY KEY NONCLUSTERED (BillingID))
In this table, the CreateUID, and CreateTS columns have default values defined. Note, however,
that those defaults are actually scalar functions. This is actually a handy way to create auditing columns
in your tables, which can be useful for a variety of reasons. Note that the additional audit columns
UpdateUID, UpdateTS, DeleteUID, DeleteTS in this table definition will be discussed in the following
section covering triggers.
In Practice
With all this discussion of constraints you may be asking if defining constraints has a negative impact
on performance. I have to painfully acknowledge that it does. However, removing or not including
constraints for performance purposes should be done as the very last resort !Onceyou'veexhausted every
technique and recommendation in this topic, as well as the technical resources of Microsoft, removing
constraints might be a consideration. However, sacrificing the protection constraints provide for data
integrity requires significant consideration. Please do not remove constraints unless there is absolutely no
other way to improve performance.
StoredProcedures
Stored procedures should be familiar to you. Therefore, extensive background description won't be
given. For anyone needing in-depth education concerning stored procedures, start with BOL. That being
said, there is some material to be discussed here. First, know that there are two types of stored
procedures. Those are Transact-SQL and CLR.
There are some definite advantages for using stored procedures. First, a stored procedure is syntax
checked and compiled only the first time it is executed. SQL Server will store the compiled version of
the stored procedure in its memory cache. Subsequent executions will then use that. Another benefit is
a reduction in network traffic. The call for a stored procedure will simply include the procedure name
and any parameter values. Compare that to sending an entire T-SQL batch. Also, the procedure can limit
output returned so that only data that really needs to be sent back to the caller will be sent. Yet another
advantage of procedures deals with security administration. Applying the appropriate security settings
on a procedure is much easier than trying to apply those same settings on all the underlying SQL objects,
for any given user or group. Finally, another advantage is maintainability. Because the stored procedure
resides within a database it can be updated easily and independent of any calling code. Thus, once the
procedure is updated, all calling programs will get the change.
There are a couple of disadvantages of stored procedures that should also be mentioned. The maintain-
ability aspect mentioned above is a dual-edged sword. A change in a stored procedure will be felt by
every bit of code that calls the procedure. If the change turns out to be undesirable, then the scope would
be much wider than if the change were limited to specific code instances. Another disadvantage has to
do with network traffic. Stored procedures return messages to the calling program. Most of the time, this
isn't a problem. However, in some instances those messages become overwhelmingly numerous. Unless
the procedure is modified to curb those messages (which is described in the ''NO COUNT'' section later),
they can cause performance problems.