Saturday, October 21, 2006 2:03 AM dbottjer

SQL Formatting for Easier Debugging 

1
2
3
4
5
6
 
SELECT [IssueHistoryID]
      ,[StafferID]
      ,[IssueID]
      ,[Comment]
      ,[DateCreated]
  FROM [IssueVision].[dbo].[IssueHistory]

This Simple query was created from a demo Microsoft Smart Client Application called IssueVision.  To create it I right clicked on IssueHistory table in SQL Server Management Studio 2005.  Notice the commas are all to the left of the column names.  Commonly, I see queries written like this instead:

1
2
3
4
5
6
 
SELECT [IssueHistoryID],
       [StafferID],
       [IssueID],
       [Comment],
       [DateCreated]
  FROM [IssueVision].[dbo].[IssueHistory]

The first example with the commas on the left is faster to debug and modify. Notice the only modification I need  to make to comment out a column/line is to place "--" in front of the comma as shown below.

1
2
3
4
5
6
 
SELECT [IssueHistoryID]
      ,[StafferID]
      ,[IssueID]
      ,[Comment]
    --,[DateCreated]
  FROM [IssueVision].[dbo].[IssueHistory]

I realize this tip is a subtle point.  Seems a little strange that I'm actually analizing the placement of a comma.  However, I have become quite fond of this style b/c it saves time and helps me to reduce simple syntax mistakes while testing/debugging large sprocs.  Try it with some more complex SQL and see what you think.

Filed under:

Comments

# re: SQL Formatting for Easier Debugging

Sunday, October 22, 2006 10:19 PM by royashbrook

i've utilized this a lot and it works quite well for mssql stuff. also, putting joins on their own lines helps for troubleshooting sometimes too.