Thursday, December 29, 2011

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.

No comments:

Post a Comment