SQL Server Best Practices (T-SQL)

  • Always qualify objects by owner
  • Do not use * in SELECT statements, always specify columns
  • Query “with (nolock)” when you don’t require high transactional consistency
  • Do not use GOTO
  • Avoid cursor use. 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 existance
  • 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
  • Try to avoid “SELECT *”, except these two cases: “WHERE EXISTS (SELECT * …)” and “SELECT COUNT(*) …”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: