• Use Stored Procedures to select, update, delete and insert records.
  
  This method is by far the most efficient way to access an SQLServer database.
  
  
• If dynamic SQL is used (IE. a SQL statement built in a string variable and then executed) then consider using SP_EXECUTESQL to submit the SQL statement. (See SQL Books Online for implementation details of SP_EXECUTESQL)
  
  
• Only return the columns required.
  
  
• Only return the rows required.
  
  Use a selective “where” clause in the Select statement and ensure a supporting index is created. Avoid executing a Select * from  <table>  - if the table has many rows.
  
  
• Use appropriate search arguments in the Select statement’s WHERE clause.
  
  Eg. = , between, >=, >, <, =<, Like “<string>%”
  
  This allows the optimiser to use indexes for data retrieval.
  
  Search arguments that can prevent the optimiser from using an index include :-
  
  IS NULL, OR, <>, !=, !>, !<, NOT, NOT EXISTS, NOT IN, NOT LIKE,  and
  LIKE “%<string>", expressions that include a function on a column.
  
  Note: the Query Execution Plan should be reviewed for each SQL statement to determine if the an efficient access path has been selected by  the optimiser.
  
  
• Avoid using joins of more than 4 tables.
  
  
• Avoid or at least minimise the use of Group By and Distinct statements.
  
  
• Use Exist instead of IN.
  
  
• Avoid nesting views – IE views that access other views.

July 2001