“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.
- 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
- 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.