Best Practice to write SQL - Stored Procedure.

Hi Friends,

As every application has a Database, and some objects which are helping us to perform the actions on our Data, here I am going to tell Stored Procedure SQL Object related best practices.

• Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. That is, place it just after the AS keyword.

• Use schema names when creating or referencing database objects in the procedure. It will take less processing time for the Database Engine to resolve object names if it does not have to search multiple schemas.

• Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

• Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behaviour is like row-based processing and degrades performance. 

• Avoid the use of SELECT *. Instead, specify the required column names.

• Use explicit transactions by using BEGIN/END TRANSACTION and keep transactions as short as possible. Longer transactions mean longer record locking and a greater potential for deadlocking.

• Use the Transact- SQL TRY…CATCH feature for error handling inside a procedure.

• Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This will prevent passing NULL to columns that do not allow null values.

• Use NULL or NOT NULL for each column in a temporary table.

• Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

hope it will help to increase your application performance.

Write exceptions to System Event Log.

Hi Dear friends,

I was in need to use the Operating System Event Logs, to save the c# exceptions, where I got the following information from our coder's guru... (Google)

Namespace to be included : System.Diagnostics
Classes: All classes in above mentioned namespace allow us to communicate with the System Processes, Event Logs and Performance Counters.

Following is the code snippet where I have used EventLog class default constructor to log the event to the system event viewer

Output to the above Code snippet

Thanks,
Hope it helps.