Tips for SQL Programing

In this blog you will learn few Important Tips for SQL Programmer.

There are lots of articles on Query Optimization, here I am going to highlight few general points those can make a minor to major difference in performance or quality of code with very little efforts. These are general points, but few things and examples are specifically for SQL Server.

• Always try to end your SQL statement with semicolon (;). 
• It is preferable to use object name with schema for your tables and other objects. So use dbo.MyTable instead of MyTable. 
• Try to avoid * in Select query, instead use column names. It is better to use Select Coloumn1, Column2, Column3 From dbo.MyTable;
• It is better to use alias with table name and related columns. So it is better to use with respect to above example: Select tbl.Coloumn1, tbl.Column2, tbl.Column3 From dbo.MyTable as tbl;.
• Always try to involve as few columns in query as possible. Rule of thumb is not to add columns in query which are not required. 
• Avoid unnecessary data casting in query.
• Although SQL Query Optimizer rearrange conditions execution sequence, yet, it is better to add required conditions in Join Statement if applicable.
• If possible, then consider proper usage of Indexes. If you use an Indexed column with expression (some function, or operator like +, -) then, indexing will not give any benefit as it is ignored in a such case.
• Avoid Dynamic SQL, in general, stored procedure give better performance. There are few cases where dynamic queries are faster like dynamic sorting, dynamic filters, but dynamic queries may have some other challenges.
• Try to be specific in database transaction.
• It should be better to have both operands of same type in a condition.
• There are many situations, where cursors can be avoided with CTE and loops with temp/memory Table.
• If you have small data to hold temporary then memory table is better choice over temp table in general.
• If you are going to fetch data in pages then, fetch and offset is a good option introduced in SQL Server 2012. If you are using SQL Server 2005/2008 then CTE is better approach.
• Surprisingly, SQL Server do not have short circuit And, OR. It may or may not short circuit your conditions depending on Query Plan.
• Union All is faster than Union operator. So precisely, use what is required. 
• Operators OR, Like, In are very expensive operators. There can be alternatives, for example you can use Union All instead of OR.
• In general if you use Distinct or Group by with out any aggregate function, they will provide same performance. Practically, it will be rare to have both in same query.
• Please be aware of operator precedence in SQL.
• It will be better to be aware of latest technologies and features. In general, I may prefer out of box features instead using old custom methodologies until unless there is a reason.
• If you are going to write a complex sp, and using same data or table again and again, then consider CTE or Temp/Memory Table.