Do’s and Don’ts in T-SQL


“What are Do’s and Don’ts while writing the T-SQL?” I heard this many time from developers or new learners or even some of the interviewers. You may get a lot many articles on Google if you search for T-SQL Best practices. In this article I have summarized Do’s & Don’t for your quick reference. I hope this will help you next time you write the T-SQL.

Do’s

  • Finalize or decide upon the database naming convention across the environment.
  • SELECT <required fields>. This will result in less I/O and network traffic and thus better performance.
  • Frequently consider advance SQL or views or table variables. But excessive use of table variable can cause memory overhead.
  • Writing explanatory comments in Code. This helps others to understand the code plainly. Writing or maintain lengthy comments won’t impact the code performance
  • Opt for set based approach instead of procedural approach. In exceptional cases, CURSOR can be avoided using WHILE loops.
  • If your database contains heavy text based search opt for FULL text search instead of LIKE operator.
  • Using SET NOCOUNT hint at the beginning of the code this improves the performance by reducing the network traffic.
  • Avoid Dynamic SQL. Dynamic SQL likely to be slower than the static SQL, as it generates the execution plan every time at runtime.
  • Using Error handling in stored procedures. @@ERROR or TRY CATCH.
  • Use the Column list in INSERT statement.
  • Using Transaction Handling (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) if transaction involves more than one data manipulation (INSERT/UPDATE/DELETE) operation.
  • Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally

Don’ts

  • Use SELECT *.
  • Use temporary tables in processing data. It creates a more disk IO which could hit the performance.
  • Use the server side cursor
  • Use Sub queries in JOIN. In Sub-query Inner query is executed for each and every row in Outer query. This can be avoided using JOINS.
  • Use prefix for object “sp_”. The prefix “sp_” is reserved for System procedures.
  • Use the Index Name in Query.
  • Use wildcard character at the beginning of the word while searching using LIKE keyword.i causes index scan instead of index seek
  • Use locking Hints in SQL statement as SQL server automatically handles the locking in a way to minimize the level of locking.

For more Do’s and Don’t please navigate through the MSDN.

Advertisements

One thought on “Do’s and Don’ts in T-SQL

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