SQL Programming Guide

SQL Performance Tips

• Don’t start with your stored procedure name with “sp_ “.
• Don’t put SELECT * from in query. List the required columns.
• Try to avoid Cursor as much as possible. We can apply WHILE loop and smart SELECT instead of Cursor in some cases.
• Try to avoid creating Temporary table and use views, variant table instead of temp table.
• Give Comments in SP and batch query for complicated code which makes easy to understand it.
• Try to avoid wildcard characters at the beginning of a word and not equals operators while searching.
• Use SET NOCOUNT ON at the beginning of your SQL batch queries.
• Use ANSI-Standard Join which gives more readable and fast retrieval.
• Don’t write any Query in front end application whole query should be in SP.
• Try to avoid Dynamic query as much as possible in batch query, SP and Triggers.
• Handle NULL values carefully use ISNULL and COALESCE functions to handle NULL.
• Only use Unicode data type (NCHAR, NVARCHAR) when you are storing non English characters because it takes twice as much space as non-Unicode data types.
• When you insert data in a table you should mention column names it will avoid an issue if the table is altered in future.
• Use Normalize I/P and O/P of SP in front end like string manipulations, concatenations, row numbering, case conversions and type conversions because these operations are going to consume more CPU & RAM cycles on the database server.
• Avoid unnecessary network roundtrips between front end application and DB.
• Put status parameter for SP which defines status of success or failure.
• Keep error handling properly in SQL batch queries and use rollback the transaction in case of an error by default, SQL Server will not rollback. Use @@ERROR, @@ROWCOUNT server property and try, catch to handle errors.

Tips to Avoid Deadloack

• Make transactions as much as simple
• Handle only required data while transaction
• Should not get any I/P from user at middle of the transaction
• Don’t not use high level locking methods unless it required.
• Use table in same order in SP and all batch queries consistently.