SQL Coding Standard - Things On The Right End Of The Line

I hate the practice of putting all the column names in, commented out. That's a huge waste of SQL proc buffer space, making the entire instance run more slowly. Just stop that, it's horribly inefficient.

...not to mention. if the schemas change, who changes the commented column names? No one, I'll bet.

In my case, the schema changes always involves a new table and the old one falling into disuse. The vendor needs to maintain the code base for previous clients.

In SSMS you can drag the [COLUMNS] label in the object tree onto the Query Window to get a list of columns. Don't think you have any control over the format though ...

We have an Sproc, with a short easy-to-type name (KLC - Kristen's List Columns, natch), which displays a table and its columns in various suitable formats - both single line command delimited and one-column-per-line. We have meta data which provides descriptive names for columns which are include as comments. The Sproc will take partial table, or column, names and if specific to a single table will display that table, otherwise a list of possible matches - it also handles VIEWs and if no match on any of those will display Sprocs with matching names and, again, if a single SProc matched then display parameter list.

We tend to retain a comment in the code adjacent to the FROM statement such as

SELECT ...
-- KLC MyTable2
FROM MyTable1
    JOIN MyTable2
        ON T2_ID = T1_ID
...

it is easy enough to double-click to select a different table and then double-click and paste to replace the parameter in the KLC line and highlight and execute that snippet to get a column list. We deliberately don't include quotes around the parameter - except when we want additional parameters - which makes it easier to fiddle with - our table and columns names are A-Z, o-9 and "_" only, so no word-break stuff to have to deal with :100: We have a additional @CMD parameter that delivers the column list in different, less frequently used, ways such as

@ColName1 = ColName1,
@ColName1 = @ColName1,
@ColName1, @ColName2,

and "INSERT" which generates an

INSERT INTO
(
    ColList
)
SELECT
    [Col1]='xxx20',
    [Col2]=99,

etc. which indicates the size of the target column and datatype making it easy to cut & paste actual columns, or CONST data.

Code is proprietary to the Metadata tables we use, otherwise I'd publish the SProc for anyone interested. Perhaps better, if you like the idea, to knock up your own as it can evolve to meet your needs.

We pass ALL our SQL code through a compaction tool before putting it into production as I consider every space and every comment a waste of buffer space. I've never done any performance tests, but it surely has to be slower to parse verbose code than compact code?, plus I don't intend to make it easy and for anyone snooping on our code to help themselves; Perhaps WITH ENCRYPTION is more robust now .. it used to be trivially easy to reverse engineer.

On the contrary, we see it as hugely efficient as it reduces the number of bugs in the code during DEV

Correct, its usually only relevant when the code is written, or significant changes are undertaken (at which time the list would be refreshed), so it is valid (and maintained) during a code development cycle (6-months-ish) only.

As I mentioned this is not something we do every time, only when the majority of the columns are selected as we think the the exceptions as useful to see - i.e. we see it as Defensive Code that may highlight a bug.

We version the table - so the original table becomes MyTable_V2 and a view is created called MyTable which provides backwards compatibility. We usually locate all INSERT/UPDATE code and fix that to reference the new table, but SELECTs referencing the old table will often only get updated when that Sproc is changed for some other reason.

Love that idea! Home-grown?

Yeah, VBScript I think ... its got a bug because we have 3 or 4 sproc that have a big comment at the top saying "Don't compact this one" !!

We wrote it as I couldn't find anything out there, but I'm pretty sure I've seen some stuff out there quite recently - the last 6 months or so

Let me have a go at Googling that for you :wink:

Darn! It would be a tool I have no access to!

"sql source code minifier" seems to be the right sort of search phrase for Google.

Anything along the lines of "SQL Compaction" seems to wind up with Data Compression which is no use.

And "SQL Compressor" got me nowhere useful either

http://poorsql.com/ seems to do a reasonable job. However, it splits lines at close-to a specific point. Mine retains all the original line break positions - which gives me a chance of debugging it in that state if I have to

Original 3,036 bytes - not lavishly commented
My compaction tool 1,704 bytes
PoorSQL 1,678 bytes

http://codebeautify.org/sqlformat]http://codebeautify.org/sqlformat Puts the code all on one line, including "GO", which obviously doesn't work ... (Result = 1,754 bytes)

unless I'm doing something wrong?? this put the result all on one line, comments and all, so the first comment would inactivate the rest of the line ...

http://www.webtoolkitonline.com/sql-minifier.html

You still using Query Analyser? That does it too (but you have to open the COLUMN tree arm before it will work - you can close it and then drag, so I guess it just has to have cached the column metadata.

Otherwise you'll just have to knock up a column listing SProc :smile:

In case of interest we had an interesting discussion on SQL Source Code compression / compaction / minify in the old forum:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138560

No, since I am working remotely, they have given me sharply defined permissions. I ran profiler one time on a large result set and crashed their server (low memory and no TempDB ceiling). Their server is the production server with the 'test' environment packed alongside. I no longer have access to even profiler.

In my defense, the stored proc I was building was required to return the entire history of every patient since the purchase of the hospital two years ago, and even with temp tables retrieving only 25 columns out of 180, there were still several million rows to report. The attempt at using the profiler was to reduce the run time and memory footprint.

What do you use to write SQL? Maybe just an editor of some sort?

Sounds like it might be worth having a copy database locally, even if no data (assuming there is sensitivity over that being off-site), so that column names and the like are available to you.

I could do with some suggestions for how to test performance of a Minified Sproc.

I am thinking just to change every white space to a much larger one, and add a long comment to each line.

Would that do, or is there something better?

How to actually test? I can get a Parse Time from SET STATISTICS - is that my best option?

Maybe I need to

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

between each test, otherwise maybe the query plan is cached and there will be no parse time?

I can also use SET STATISTICS for the runtime, and I can also use a DATETIME2 to calculate the elapsed time.

Are those my best options?

Interesting conversation - just one note:

I always change the tab settings in SSMS to replace tabs with spaces - and modify the size to 8 characters. 8 characters will line things up nicely for SQL whereas 4 just doesn't work (for me).

Converting to spaces helps insure that when someone views my code - they view it the way I meant it to be viewed and not all over the place because they have different tab settings.

Of course, if they have changed the font it might not quite line up...but still better than tabs that don't line up.

So your files will be bigger than mine (retained TABs) ... will be interesting to see if there is any discernible performance difference once the whitespace has been removed.

My gut feeling (with absolutely no basis in fact - pure speculation) is that, it will show no discernible difference in performance. My experience has been that the biggest bang for the buck comes from optimizing the queries to minimize data retrieval/sorting and that everything else has very little (orders of magnitude less) impact.

So when I write code, I write it the way that seems best and easiest to maintain/read, with absolutely no regard to how much space the code is taking. Then again, I work with puny databases - my biggest database is only 40GB - and I am the only database guy. So there is no one to point a finger at me and say "you are screwing up here".

If the Procedure is taking seconds to run then for sure any parse will be trivial, by comparison, but parse is mostly pure CPU. Every system I have ever worked on has had improve compile/parse times from compressed source code because the parser doesn't have to step-over the whitespace and comments; unless computer science has moved on there is no easy way to "know" where the next keyword is in the file, it is found by walking the characters in the line sequentially, ignoring any spaces etc.. I agree that it is not a reason to work with poorly formatted code, but if it turns out to make even a modest saving it would be a reason to consider compressing the source code prior to deployment. For some, such as me, some obfuscation is welcome too.

That said, right now I'd just like to make a performance test, and I'd appreciate any ideas folk have as to the best way of doing that.

The compile time savings from compressed code would be hard to measure, I think. I'd expect the diffs to be very, very small. In any case it's only an O(1) difference, so not really significant. However gobs of comments will take up cache space. That might turn out to be significant in a busy system with lots of heavily-commented procs. Again, it would be hard to measure effectively.

I could stick a MB of rubbish into each of our most commonly called Sprocs on our busiest production system for a week or two ... and then measure the volume of abuse from the users :smile:

For those folk that think that code formatting is important here's an interesting article by Aaron Bertrand that I have just read. There is virtually not one single point where I disagree with him.

For those folk that thing that code formatting is NOT important here's an interesting article by Aaron Bertrand that I think you should read :smile:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx