T-SQL Tuesday 151: T-SQL Coding Standards

It’s that time of the month again, the blog party, woohoo! This time Mala Mahadevan (b | t) has invited us to blog about our T-SQL coding standards. I think standards are pretty important but really hard to enforce.  Sometimes you start with a company, and they have standards that um, really don’t look like standards at all, and you left like scratching your head and trying to get them to see the importance of them.  I have basically categorizations of what I like to see in standards and not necessarily an exact why they have to be done.  I have a preferred away and like Mala, I like to use tools like SQL Prompt to make it easier to do.  So, this blog post will be pretty short.  I’m just going to write about making code readable, because that applies to any language that you code in.  If its’ not readable it’s nearly impossible to maintain.

Let’s start with readability.

1. I like to read the code.  This comes in many forms.  One is indenting so part of the code stays together and can see when the next part starts.  So, we will use a SELECT statement as an example.  Start with indenting between each part of the statement like this:

   SELECT 
   emp.EmployeeID
   , emp.EmployeeName
   , dpt.DepartmentName
   FROM dbo.Employee emp 
   INNER JOIN dbo.Department dpt ON dpt.DepartmentID = emp.DepartmentID 
   WHERE 
   ... 
   AND ... 
   GROUP BY 
   , ... 
   ... 
   ORDER BY 
   ... 
   , ...
   

2. Also, as you can see I like to capitalize the key words of my programing language if that is to make those words stick out.
3. You can also see I prefer to capitalize the first letter each word in the column names the same goes for tables names.
4. If you are going to have to join tables using alias rather than spelling out the tables is preferred.  Even specifying the table on each column when it’s not required is preferred.  And an alias that means something like t1 or t2.
5. Another thing you can see I do is put everything on a separate line, including the columns in the select at the beginning of the line.  I do this so it’s easy to comment out the line for troubleshooting purposes.
6. Now, if this ends up in stored procedure or source control, you should have some comments that example why you are doing, what is the purpose.

Beyond this standards get more particular into how make sure code performs well and I look forward to reading what everyone has to say on that.  Making sure your WHERE clause is SARGable, watching out for IMPLICIT conversions, usage of temp tables, etc.

The post T-SQL Tuesday 151: T-SQL Coding Standards first appeared on Tracy Boggiano's Blog.