Microsoft SQL Server/Best Practices

From Wikibooks, open books for an open world
Jump to navigation Jump to search
  • Always qualify objects by owner.=
  • Use query "with (nolock)" when you don't require high transactional consistency.
  • Do not use GOTO.
  • Avoid CURSOR use because it's significantly slower. If necessary, always declare the correct type of cursor (FAST_FORWARD).
  • Avoid SELECT INTO for populating temp tables. Create the table then use INSERT SELECT.
  • Always use ANSI join syntax.
  • Always check for object existence.
  • Use SCOPE_IDENTITY() instead of @@IDENTITY.
  • Always check @@TRANCOUNT and commit/rollback as necessary.
  • Order DML to avoid deadlocks.
  • Always check @@ERROR and @@ROWCOUNT by assigning to a variable.
  • Always check sp return values.
  • Do not create cross-database dependencies.
  • Avoid table value UDF – performance problems.
  • Avoid dynamic SQL – if necessary use sp_executesql over EXEC.
  • Avoid using NULL values.
  • When there are only two values, ISNULL is more efficient than COALESCE.
  • Always specify columns; try to avoid "SELECT *". Exceptions include these two cases: "WHERE EXISTS (SELECT * ...)" and aggregate functions.