On formatting SQL code

Question

On formatting SQL code

Phil Factor

SSC-Insane

Points: 20244
More actions
March 25, 2016 at 8:35 am

#312264

Comments posted to this topic are about the item On formatting SQL code
Best wishes,
Phil Factor

Viewing 15 posts - 1 through 15 (of 56 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

I'm one of those folks that will spend a little time doing vertical and horizontal alignment in my code. Since I'm very used to it, it takes me almost no extra time at all especially when compared to someone having to read the code to troubleshoot or modify the code or just to see what it's doing so they can use a technique somewhere else.

I think it especially important in articles and on forums because the formatting allows people being taught to spend them time on learning the code rather than trying to read the code.

All that being said, I strongly disagree with the "all this formatting slows productivity significantly" statement in the article. In fact, I'll state that it actually makes you more productive because you're more likely to catch a logic mistake as you format and you also save a huge amount of time when troubleshooting or modifying code.

As for an "enforced standard", yes we have a bit of that where I work... because I'm the one doing the peer reviews. If I can't read your code quickly and quickly understand what it is you're trying to do, your code fails and you get sent back to rework it.

Readability matters nearly as much as accuracy and performance. Get used to it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hfreeman Mr or Mrs. 500 Points: 517 More actions · Answer 2

For sustainability and maintenance, I demand it of programmer. IF NOT then the code get rejected at Code review. NO EXCEPTIONS. Either do the job right or get out of the way.

Hank Freeman

Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)

diamondgm SSCrazy Points: 2824 More actions · Answer 3

code formatting and structure is a must it's very difficult getting in to someone's code and having to try see the world as they see it so standardisation is important in an organisation the use of capitalisation and indentation go a long way to helping a develop who is unfamiliar with the code see what matters quickly and as jeff said helps the writer spot mistakes sooner

JBrosch SSC Enthusiast Points: 179 More actions · Answer 4

How about a simple SurveyMonkey survey on this? ... I would curious to see the results 5 = heavily structured and enforced to 1 = free form - one above "qualifies for obfuscation"

diamondgm SSCrazy Points: 2824 More actions · Answer 5

JBrosch (3/26/2016)
How about a simple SurveyMonkey survey on this? ... I would curious to see the results 5 = heavily structured and enforced to 1 = free form - one above "qualifies for obfuscation"

Take the survey here -

http://1drv.ms/1oddgB5

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

diamondgm (3/26/2016)
JBrosch (3/26/2016)
How about a simple SurveyMonkey survey on this? ... I would curious to see the results 5 = heavily structured and enforced to 1 = free form - one above "qualifies for obfuscation"
Take the survey here -
http://1drv.ms/1oddgB5

That's nice and I responded but where do we get to see the results?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

diamondgm SSCrazy Points: 2824 More actions · Answer 7

The results should be visible here

If the BI gods do their thing, the report should update hourly

You should also be able to download the data by clicking on the ellipsis of any of the cards and electing to export data.

The downloaded format will be csv

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

Are you trying to incite a riot, Phil?

In my experience (and I think IBM did some work around this that support it) a consistently formatted codebase has less bugs than one in which each module is formatted per each developer's preferences. I work towards keeping a tidy codebase and look to build consensus across the team with the help of tools like SQL Prompt.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Conrad Muller SSC Veteran Points: 294 More actions · Answer 9

Formatting for readability is important for building large or complex queries, and for maintenance programming. I also use extendedproperties to describe all database objects.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Jeff Moden (3/26/2016)
diamondgm (3/26/2016)
JBrosch (3/26/2016)
How about a simple SurveyMonkey survey on this? ... I would curious to see the results 5 = heavily structured and enforced to 1 = free form - one above "qualifies for obfuscation"
Take the survey here -
http://1drv.ms/1oddgB5
That's nice and I responded but where do we get to see the results?

I took the survey as well. Please post the results after a week or so - I'm really curious about the results.

I think formatting code is essential for maintainability. Haphazard formatting is a type of technical debt and interest must be paid on that debt every time someone has to go in and try to figure out what the code is doing. I'd just rather avoid the debt completely. I find it takes no extra time to write code well, but I've been doing it a while.

This is a topic that some people are very passionate about. I hold myself to a strict standard and others find it simple to read, but not everyone finds the same things easy and straightforward. I'm also a firm believer in the use of comments, which some people have a deep loathing for, but I write them for myself as much as others. If you have to spend time figuring out what a block of code does, that's another payment on technical debt.

In the end, it's about efficiency, maintainability and error-free code.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

diamondgm SSCrazy Points: 2824 More actions · Answer 11

diamondgm (3/26/2016)
The results should be visible here
If the BI gods do their thing, the report should update hourly
You should also be able to download the data by clicking on the ellipsis of any of the cards and electing to export data.
The downloaded format will be csv

Click on "here" - it will take you to a Power BI dashboard.

You can also download the data by click on an items ellipsis.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

I have my SQL Prompt 'Format' settings configured to align to my company's standard. Whenever I work on any code, CTRL-K/CTRL-Y gets pressed first. I want to write and read code, not waste time formatting it. If someone else's code did not conform to the standard, I just fixed it for them.

This also has the side effect of adding in any missing semicolons and removing any unnecessary square brackets.

--Edit: fixed typo

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jsrapp Grasshopper Points: 13 More actions · Answer 13

In our shop, rigorous code reviews are required. By putting everything on separate lines, a simple update to one field in a SELECT is trivial to code review rather than having to hunt through and figure out what was changed on a massive line with 20 fields and multiple other clauses. As you said, SQL Server doesn't care, but the code reviews for minor changes by someone otherwise unfamiliar with what you're doing are very quick.

Toni-256719 SSCertifiable Points: 5899 More actions · Answer 14

Going to that link for the results requires me to create a login.

You'll probably want to post the results.