This post will be about coding standards. There are countless articles and blog posts related to this topic, so I know this post will not be too revealing. Yet I would like to mention a few things I came across during my work with the T-SQL code.
Naming convention - there are many of them obviously. Too bad if all of them are used in the same database, and sometimes even in the same stored procedure. It is not uncommon to see something like
As you see, we have both CamelCase and 'underscore' notation here. Wouldn't it be nicer if we had only one? I am not saying which one, but you should agree upon naming convention before you start development.
Cursors - oh yeah, we all know that they are bad. But do we? I have seen code like this
So as you see, we select rows from Table1 and one by one (RBAR)
issue an update on Table2 using variables read from cursor as keys. How about this?
In these two lines you solve nicely clumsiness (and potentially performance issues) of the first code sample.
Default parameters - something I noticed recently, when I needed to figure out if a procedure is going to be called without parameters or not:
I am not sure if this is a typo or not - if I should fix the code to support wildcard search or change it to throw error if no rows are returned? Obviously '%' is not a name of any of items in the table. What is the reason of calling this proc in a way that will not process any rows?
If we remove default value from the parameter, we will at least know that something is wrong when someone implements parameterless call to this procedure. Otherwise higher code will work seamlesly, no exceptions, but nothing done too. Definitely not what customers wish. Having said that, parameters with default values are definitely useful and should be used where needed, but they shouldn't be treated as shortcuts to facilitate testing.
Even worse scenario is if we code the parameter to default to an existing ItemName. Then, if for some reason higher code will call procedure without parameters, procedure will process data for ItemName - another rather tricky bug to trace down.
Comments - I see tons of code that modifies data in very extensive ways, but there is no explanation whatsoever about what is the business meaning of particular transformations. I am sure that a DBA or a new developer would appreciate a lot if a series of update statements, 10 lines each would be accompanied by a line or two of commentary, about what the statements do and why. Would be nice, wouldn't it?
Prefixing columns in views - this is real pain.. If you have a view with 150 columns and 20 tables joined every possible way in FROM clause and no prefixes in columns in SELECT part... It takes so much time to dig down the tables and other views to find the origin of a particular column. Wouldn't it be nice to have a feature in SSMS that would either enforce usage of prefixes or at least issue a warning during object creation? After all, some rudimentary checks are performed and the engine knows which columns are from which table. I recommend that developers must prefix columns in multitable select statements and I would impose this practice during code reviews.
This list is definitely not finished. I am sure you came across many little annoying things, like indenation, spaces vs tabs, lowercase vs UPPERCASE etc. I will update this post if I find especially outstanding and annoying (bad) practice.