SQL Server trigger abuse

A trigger is used to perform certain actions when adding, modifying, or migrating data to a specified table. SQL Server 2005 also provides database-level triggers, but here I only describe table-level triggers. Because the trigger starts a hidden transaction, the trigger will impose additional costs on the server. Once the trigger is executed, a new hidden transaction starts, and any data retrieval in the transaction will lock the affected table.

What should you do? Use as few triggers as possible and make the trigger logic as simple as possible. You can simply replace some triggers with integrity reference constraints. Other triggers can be implemented in stored procedures.

 

Returns the number of affected rows.

 

By default, SQL Server Returns a friendly message to report the total number of rows affected by each query. This is a good setting for debugging applications or directly modifying data in query analyzer. However, your front-end application does not need to know the number of affected rows-it only needs data. Sending this message may introduce unnecessary network load.

 

What should you do? Use "set nocount on" in all your stored procedures to reduce network transmission.

 

Conditional statement execution

 

Generally, you need to apply different code logic based on different values of parameters passed to the stored procedure. For example, if my parameter value is 0, I can retrieve the value from a group of tables. If the parameter value is 1, I can retrieve it from another group of tables.

 

If @ parameter = 0 Begin Select column1, column2 From some_tables... End Else Begin Select column1, column2 From other_tables... End

 

This code forces the process to be re-compiled every time it is executed, because SQL server can only identify which of the two plans is useful at runtime.

 

What should you do? The preceding process is divided into two independent processes, each of which queries different table sets. Determine the parameter value in your intermediate layer code, and then call the appropriate stored procedure.