Microsoft SQL Server/Best Practices
  • 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.

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.


Microsoft_SQL_Server/Best_Practices
 

Manage research, learning and skills at IT1me. Create an account using LinkedIn to manage and organize your IT knowledge. IT1me works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us  |  About IT1me.com |  IT Training & References |  IT Careers |  IT Hardware |  IT Software |  IT Books