This is part 2 in an ongoing series to put some tips out there to make your T-SQL code cleaner (and hopefully) better.
Using Comments
In his book “Clean Code” Robert Martin has this to say about comments “Nothing can be quite so damaging as an old crufty comment that propagates lies and misinformation”.
What exactly does he mean here? Let pull in an example from a stored procedure I had laying around:
[sql]
/*************************************************************
* Move Database Script *
* by T-SQL Programmer *
* *
* Created: 11/11/2011 *
* *
* Changelog: added single user mode – 04/11/2011 *
* *
*************************************************************/
— Put the database in single user mode
use master;
exec sp_dboption MyDatabase, ‘single user’, true
go
— Detach the database
exec sp_detach_db ‘MyDatabase’, ‘true’
go
— After this, you will need to move the files to their new locations using Windows
— Reattach the database, specifying where the files are now located
exec sp_attach_db ‘MyDatabase’,’C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF’,’C:\Program Files\Microsoft SQL Server\MSSQL\data_log\MyDatabase_Log.LDF’
go
[/sql]
Comments are with dashes and you have a huge decorative comments box at the top that’s almost as big as the code. But why?
My question is if we have source control (and we all should) why is this comments section needed? A source control system would track all the changes as well as allow for comment with each change.
Something else you might notice is the dashed comments are redundant. If we all know T-SQL, and those who might look at the code know T-SQL, then why do we need them?
Lastly comments add another level of maintainability. When we add or change functionality the comments need to change with it. Many times the comments are not updated and propagate misinformation.
Of course we can rewrite a bit of the code to be more descriptive, but it’s pretty descriptive on it’s own already.
So really to clean this up we would just remove the fluff and insure we put this into source control (*see future post).
[sql]
— Moves Database to Another Location
use master;
exec sp_dboption MyDatabase, ‘single user’, true
go
exec sp_detach_db ‘MyDatabase’, ‘true’
go
exec sp_attach_db ‘MyDatabase’,’C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF’,’C:\Program Files\Microsoft SQL Server\MSSQL\data_log\MyDatabase_Log.LDF’
go
[/sql]
Pretty explicit to someone that knows T-SQL that this is using the master database, setting single user mode, detaching and reattaching to a given location.
This broke down the code from 25 lines to 12, readability is increased, and only the code needs maintained (not code and comments).
