I’m going to be doing a series to help make your code easier to read, understand through a series of techniques and ideas I’ve had over the years. This post is the first in that series.
A number of years ago I learned the value of carefully naming, formatting and commenting code. I was working with an expert in PL/SQL on a project who, over a lifetime of working in all sorts of languages, had learned that rigorously following standards brought order to difficult code. His views were similar to my own but much better defined at that time. Some of the inspirations for this post are thanks to Ted (you know who you are!).
Comment header blocks give me a place to tell YOU why I wrote this thing. What it does, what is assumed or ignored and most of all – why it matters!
-- //$Revision: 1 $
-- //$Date: <LastEdited, SYSNAME, LastEdited> 8:49a $
-- //$Modtime: <LastEdited, SYSNAME, LastEdited> 8:44a $
-- //$Workfile: <ScriptFileName, SYSNAME, ScriptFileName>.sql $
-- // Modification history:
-- // Rev# Date Author Description
-- // ----- -------- --------- --------------------------------------------
-- // 1.0 <LastEdited, SYSNAME, LastEdited> R Gosling Checked into SVN
This one is set up for replaceable parameters: By pressing CTRL+M in SQL Server Management Studio (starting in 2005) it will prompt for the various fields to be filled in – in this case LastEdited, and ScriptFileName. This is an often overlooked feature too as most don’t know it exists.
You might notice the $Revision, $Date, $Modtime and $Workfile values. These are automatically substituted when the code is checked into your favorite source control product. Mine is still Subversion despite the religious wars going on over Git. TFS, CVS and just about all version control systems do it the same way. Git, however, doesn’t support this idea – at least not in this way.
Here are a list from the Microsoft Visual Source Safe Documentation. These are also supported by most of the common source control products as mentioned:
- $Archive: $
- Specifies the Visual SourceSafe archive file location.
- $Author: $
- Specifies the user who last changed the file.
- $Date: $
- Specifies the date and time of the last check-in.
- $Header: $
- Specifies a header in the form: Logfile, Revision, Date, Author.
- $History: $
- Specifies file history in Visual SourceSafe format.
- $JustDate: $
- Specifies a simple date, without a time.
- $Log: $
- Specifies a file history in RCS format.
- $Logfile: $
- Same as Archive keyword.
- $Modtime: $
- Specifies the date and time of the last modification.
- $Revision: $
- Specifies a Visual SourceSafe version number.
- $Workfile: $
- Specifies the file name.
- $NoKeywords: $
- Specifies that there is no keyword expansion for all keywords that follow.
Also be aware of the limits of command line interfaces (CLIs). They often only support 79 characters before wrapping around to the next line. So if you needed to ‘cat’ a piece of code to the screen having it wrap made an unreadable mess.
A common approach in SQL Server in some shops is to prefix tables with tbl_ , views with vw_, stored procedures with sp_ and so forth. In the case of stored procedures, Microsoft (and Sybase before it) were already using sp_. Some time ago it would be discovered that the internal search SQL Server does on the default path includes anything named ‘sp_’ which can lead to a performance hit.
As for naming tables tbl_* I never saw the wisdom of that but you could make a case that naming views with vw_* helps identify them as opposed to tables since they can behave similarly.
Functions are a little different as well. It’s not necessary to identify dbo.fn_myfunction by way of context: ‘SELECT * FROM dbo.fn_myfunction(id);’ you can plainly see that it is. If anything, there is more than one kind of function (scalar vs. table-valued functions). So perhaps your standard would be like fn_myfunction for a scalar and tvf_myfunction for a table-valued function.
Whatever standard you chose, be sure to be consistent. That way if there is a need to make some wholesale change (like naming) it will be possible to automate it. Also, it makes your code look professional, well organized and most of all, understandable.
One thing you don’t see enough of in the SQL Server world is the intelligent use of Schemas. These can help organize code by area. Sure, they were originally designed for use as a way to provide ownership of a group of things, but they really come into their own in SQL Server as a way to easily name things that belong together – like Sales.Orders.
A really great example of the smart use of Schemas is in the AdventureWorks database. You can just look at the names and see what the designer(s) intended. You don’t need a document to know what’s what. Isn’t that really the point of all this?
Then comes code layout. I’m picky about having my code look consistent, organized and easy to read.:
- Commas to the front
- Lists are Aligned
- Keywords are ALL CAPS
- Camel Case for User Defined objects
There are tools to help you achieve this such as the SSMS Tool Pack (maybe free – depends), Red-Gate’s SQL Prompt($), Apex SQLRefactor(free!) and Poor Man’s SQL (free!). I’ve used all with success. The main thing is these can help get your code in beautifully readable shape! They reformat the code to a predefined set of rules – rules that you decide on.
Take time at the beginning of your next project and think about organizing and naming objects.