The Ten Rules of Formatting in SQL Server for Maximum Maintainability Part 1
Once upon a time I was a junior developer that just got hired into his first real job out of college. I remember being asked a very pointed question in the interview for the job:
“What do you know about SQL?”
I asked the very pointed question in return:
Well, I got the job still and the rest is history but I tell that story to set the stage for this blog post. Everything I am writing about here was drilled into me by some of the best DBA’s in the industry that trained me. This post is for developing DBA’s who want to hone their craft and make their SQL code as readable and maintainable as possible.
Don’t use the TAB Key
White space between SQL code elements is important for visually organizing your code. The first inclination developers have is to use a combination of tabs and spaces to achieve this. I understand this because it seems to be more efficient than using the space bar, but there is an issue with using tabs.
Tabs look different depending on what program you are using to view the code and when they are mixed with spaces which they always are because you usually can’t get things aligned with just tabs alone. I am sure that if you are working exclusively in SSMS it will look the same every time, but depending on who is viewing your script and what application (think Notepad) they are viewing it in the results can vary. This might not matter to you as a developer, but the guy pushing your scripts out during a change window might not have access to SSMS. So, if something goes wrong and we must take a peek at the scripts we want them to be readable.
If you can’t break your tab habit (Tabit anyone? No? I thought it was funny), then may I at least suggest that you change the setting in SSMS to insert spaces when you press tab:
Put column names on their own line with any commas in front
When you are writing a select statement or even specifying a list of columns to group by or order by, put them on their own lines or else they look like this example from Microsoft’s AdventureWorks database:
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly FROM [Production].[BillOfMaterials] b INNER JOIN [Production].[Product] p ON b.[ComponentID] = p.[ProductID] WHERE b.[ProductAssemblyID] = @StartProductID AND @CheckDate >= b.[StartDate] AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) When formatted, it should look like this: SELECT b.[ProductAssemblyID] ,b.[ComponentID] ,p.[Name] --,b.[PerAssemblyQty] ,p.[StandardCost] ,p.[ListPrice] ,b.[BOMLevel] ,0 -- Get the initial list of components for the bike assembly FROM [Production].[BillOfMaterials] b INNER JOIN [Production].[Product] p ON b.[ComponentID] = p.[ProductID] WHERE b.[ProductAssemblyID] = @p_StartProductID AND @p_CheckDate >= b.[StartDate] AND @p_CheckDate <= ISNULL(b.[EndDate], @p_CheckDate)
Notice how the commas are in front of the column names so it makes them easy to comment out when you’re writing and testing your query without throwing errors in Query Analyzer. The columns each on their own line makes the query so much easier to read which is a great start toward being able to maintain the code you’ve written.
Put ALL database object names in square brackets
Surprisingly, a space is not a delimiter when it comes to naming objects in SQL Server. This is why whenever you generate SQL Server code through SSMS, it will automatically generate the object names wrapped in square brackets:
SELECT [Column Name] ,[Another Column Name] FROM [AdventureWorks2014].[dbo].[Table Name]
Most developers don’t like keeping up with the practice of using square brackets when referring to SQL Server objects, but it is best practice to always do it to avoid confusion and errors. I’ve personally got into the habit of doing this too with SQL Server data types (e.g [INT] instead of int), but that is more of a preference.
Write SQL Server keywords in all CAPS
I think that this goes a long way to make your code more readable as it makes it easy for your eye to differentiate between database objects and the keywords as the database objects are for the most part Pascal case.
Line up keywords and database objects in a consistent manner
I like to think of an axis being created when I write a query that has the SQL Server keywords on the left of the axis and database objects on the right. I’ve added a “pipe” character (|) to the query below to illustrated the axis concept:
SELECT| b.[ProductAssemblyID] | ,b.[ComponentID] | ,b.[ComponentDesc] | ,SUM(b.[PerAssemblyQty]) AS [TotalQuantity] | ,b.[StandardCost] | ,b.[ListPrice] | ,b.[BOMLevel] | ,b.[RecursionLevel] FROM| [BOM_cte] b GROUP| BY b.[ComponentID] | ,b.[ComponentDesc] | ,b.[ProductAssemblyID] | ,b.[BOMLevel] | ,b.[RecursionLevel] | ,b.[StandardCost] | ,b.[ListPrice] ORDER| BY b.[BOMLevel] | ,b.[ProductAssemblyID] | ,b.[ComponentID]
I find this method the cleanest and the easiest to maintain consistency with, but I’ve seen it executed in a couple different ways. Whatever you do, make sure it is consistent which means you do it every time you write a query.
(Stay tuned for the exciting conclusion)