Readers Say Size Really Matters
Based on the response from the editorial Size Matters it seems this is a common concern. Today, many of our readers have provided some real world solutions I believe you will find helpful.
John writes:
We work with thousands of stored procs and T-SQL code bits between several systems and hundreds of databases. I am fairly fanatical about having the code being consistently readable and formatted, especially when I am on call and it is 2:00 AM. Slogging through tons of poorly formatted stored procedure text just makes it more difficult. However, in these days of limited budgets, I also have not had the ability to purchase an expensive tool that will work for all the database versions we work with.
Since my main duties are in SQL Server (2000, 2005, 2008R2 & now 2012), I have looked for something that works for the majority of what I do at a basic level cost effectively. I would suggest giving Mladen Prajdic’s SSMS Tools Pack a try (http://www.ssmstoolspack.com/). It is very reasonably priced with a variety of licensing options, none of which are very expensive. This product has many things to offer, but I primarily use the Format and Snippet features. The format primarily takes care of keyword capitalization (slightly better than the add-in I also use listed next) and it has my favorite snippet integration feature ever: I can set up code in the snippets library and in a T-SQL window just type the keyword I have assigned to bring up a huge chunk of code if I want. Mladen has included a bunch of predefined snippets, but you can add more as you need them. I have not yet found a limit on size or number and to just be able to type ‘dbug’, hit Enter and have everything I need to debug a session up in much faster than I could pull it up with SSMS’s code templates is a God-send. There are lots of other features as well, such as an Execution Plan Analyzer, window connection coloring, expanded search capabilities, CRUD generation, auto-generate insert statements and lots more.
I have been using the SSMS Tools Pack for the last couple of years in combination with another free SSMS add in called the "Poor Man’s T-Sql Formatter Addin" from Tao Klerks (http://architectshack.com/PoorMansTSqlFormatter.ashx). Tao’s Formatter actually reformats code pretty well, though it sometimes misses keywords, which is what I use the SSMS Tools Pack’s to cover quickly with a right click and pick.
Both products work with all of the above versions in my day to day use (NOTE: I use my SSMS 2008 to access 2012 db’s – I don’t know if that makes a difference or not).
If I am dealing with code that is quite long but may need to be dynamically ran at a given time, then I get the code working in a T-SQL window first, add in variables and the like and format those to work on single pass first, then I will double up any single quote that may already be there and use the following to make it dynamically runnable:
DECLARE @t_sql NVARCHAR(4000)
SET @t_sql =REPLACE('
-- DYNAMICALLY BUILT CODE GOES HERE
' ,' ','')-- replace two spaces with none
/* -- debug
PRINT @t_sql
RETURN
--*/
EXEC sp_executesql@t_sql
This allows you to keep your code nicely formatted while you work on it, but if it is really long, it removes the additional spaces that are not needed at runtime. I would not use this if setting up a stored procedure though as it will create the stored proc with no formatting, which is what we are trying to avoid in the first place. The first two tools will help with this though.
Thomas Writes:
First I would say that there are a lot of different ways to format your code and everyone seems to have their own way that works for them. Rather than argue I would suggest that you find a code formatter that works for you (and make sure that it doesn’t inadvertently modify the function of the code, I’ve tried some that did). Write your own if you can’t find what you want.
One advantage of having a formatter is that you can run the formatter against the original and your changed versions and check the differences so your real changes don’t get lost in the formatting changes.
I agree with your new line list but, would add a these clauses (mostly so that I can quickly evaluate the tables involved in the query and how they are joined and filtered):
- INNER
- LEFT
- RIGHT
- CROSS
- FULL
- ON
- AND
- OR
- CASE (very simple single when and else clause queries can be all on one line break on the WHEN if multiple clauses and break on the THEN if the clause goes off screen
- WHEN (except when immediately following a CASE)
- ELSE (except very simple CASE)
- END
- WITH (as a CTE but not when used as a hint although I avoid hints whenever possible)
- BEGIN
I also put line breaks after:
- Each Column or the code to produce that column (more lines if it’s really complex like a large case statement.)
- Each Set Operation in an UPDATE
In most editors It is a whole lot easier to scroll up and down than side to side, and if you can’t see the FROM & JOIN clauses in one screen you are joining too many tables in one query.
Yes, it’s nice to see the whole query at one time, but vertical code can be read much more quickly.
Also, a lot of 23 inch monitors can be turned so you have 23 vertical inches, making shorter width lines a very good idea. (If you can get everyone in your shop writing narrow code or have a decent code formatter.)
William Writes:
In addition to writing SQL commands with with no more than one (or, in the case of sub-selects, two) clause per line, I long ago discovered that putting white space around punctuation marks and being downright pedantic about including optional keywords makes it much easier to walk the code and identify errors both in syntax and logic.
William provides a lengthy example of the same query where the main difference is indenting Sub-queries in order to improve readability. The indentation clearly makes the intention much easier to follow.
Indenting is a great technique for demonstrating control Keywords and queries impacted by them such as IF and WHILE. For this reason, it is important to keep your code easy to read with the least width and height.
Thank you all for sending valuable input to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
SSWUGtv
With Stephen Wynkoop
Did you miss Steve’s interview with Laura Rose asking, "How can you tell if your employees are engaged and motivated? How would you motivate someone who’s driven by "Identity and Purpose?" Laura says she knows the answer. Find out at SSWUGtv.
Watch the Show
Featured Article(s)
Learning SSAS-Part II: Data sources, Data source views
The second article in the series talks about data sources, data source views and diagrams.
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)
Featured Script
dba3_UsingStrFunctionToDisplayMantissaAndFloor
Using str(@value,@TotalCharacters, @MantissaCharacters)To Display Mantissa And Floor Values… (read more)