writing queries that easily readable
-
I agree on having a team standard for code formatting. However, if you want examples, here's one that I created some time ago. One image is described and the other is the "clean" version.
Some might like this option, some might not. This is just an example on a personal preference.
-
polkadot (12/1/2015)
Honestly, many more pressing issues and teams are not bothered to spend time on this topic, yet it is expected that queries are readable. Seriously, does any team really have discussions about these things? The answer is No.No.... the answer is a resounding "YES". We have a "Standards" WIKI at work and we do 100% peer reviews not to mention a final DBA review. The entire Dev Team is keenly aware that substandard code will be kicked back before it even makes it to QA. The standard includes not only formatting standards, but commenting standards, tips and snippets on how to avoid performance problems, "forbidden code" (joined updates where the target table isn't included can cause huge problems and you won't find a single example of such a thing in BOL, for example), standardized error handling for CATCH, standardized headers, standardized in-process reporting including optional parameters to set code to a debug/performance reporting mode, etc, etc.
And it all has worked very well in making it quicker to research code for modifications, find and fix problems, and has reduced QA and UAT failures to near zero. That's important because releasing bad code not only means that you have to discover the problem in production and the figure out what's wrong and where, but such production problems will frequently damage data that also needs to be repaired and, hopefully, be fixed before it reaches the end users of the data.
If you have to read code to figure out which section does what, then you have one of the many forms of crap code.
The key, though, is getting people into good habits and that includes the managers that "want it real bad". I remind them that if they "want it real bad", that's the way they get it and I won't allow it. I'm fortunate to be a DBA that has been empowered to say "NO" and have enough knowledge to teach folks better and quicker ways to resolve their code problems. I also use code reviews as an opportunity to mentor the Developers and, although slow at first, has been incredibly successful. Most performance problems have dropped to zero as has rework.
Perhaps the best of all is that the Developers and I have developed an extreme esprit de corp (I sit right in the middle of the Developer Group) and they take great pride in writing some of the best code I've ever seen out of a Dev group as well as pride when they find a performance problem (even if it's in their own code) and fix it. "Bragginng" in the form of what and how they did something is strongly encouraged so the rest of the group can also learn. And I thank them in every code review.
--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) -
Luis Cazares (12/1/2015)
I agree on having a team standard for code formatting. However, if you want examples, here's one that I created some time ago. One image is described and the other is the "clean" version.Some might like this option, some might not. This is just an example on a personal preference.
I have a very similar style. "Squared up" code makes it incredibly quick and easy to read and do "mid screen block copies" when needed. I've not had to include "Squared Up" code in the standards I wrote for the Developers because most of them have seen my code, love the readability, and have adopted it as their own style without me saying a word about it. All I've ever enforced in that area is proper indentation especially in conditional code.
--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) -
As I read this thread, I can't help but think about the question of the day, Fun with qualifiers.
That's a good example of something to avoid. 🙂
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
This is what I like that everyone despises.
(Note: I tossed random aliases and tables as fillers.)
And this one...
-
xsevensinzx (12/1/2015)
This is what I like that everyone despises.Are your referring to the square brackets or the unrelated table aliases?
-
Luis Cazares (12/1/2015)
xsevensinzx (12/1/2015)
This is what I like that everyone despises.Are your referring to the square brackets or the unrelated table aliases?
Refresh. Added another page. But yes, square brackets and tabbing.
Ignore the table names and aliases, just through in random placeholders that are unrelated .
-
xsevensinzx (12/1/2015)
This is what I like that everyone despises.(Note: I tossed random aliases and tables as fillers.)
And this one...
You're right ... I don't care for all the bracketing BUT, when it's "Squared Up" like you have it, it's not so bad to read.
It does have some "search" advantages when using "Find" or "Replace", as well.
--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) -
Jeff Moden (12/1/2015)
xsevensinzx (12/1/2015)
This is what I like that everyone despises.You're right ... I don't care for all the bracketing BUT, when it's "Squared Up" like you have it, it's not so bad to read.
It does have some "search" advantages when using "Find" or "Replace", as well.
Yeah, I actually like to bracket because it contains the field names within a visual container. I also got into the habit of using brackets due to the field names that required it in previous systems.
With the help of SQL Prompt, I rarely have to type anything because it will auto bracket for me or whatever.
Regardless, I am a developer at heart and I always tab space my code, caps what I can and keep it organized where I feel others can read it nicely. I really dislike lower caps field names with no visual separations such as:
Select
a.id,
b.product,
c.color
From dbo.mytable a
Join dbo.myothertable b ON a.productid = b.productid
Join dbo.mythirdtable c ON a.colorid = c.colorid
Where a.Time >= '2015-10-01'
And a.Time <= '2015-10-05'
Order by a.id ASC;
Like in C or C++ when I write it, I do the same with SQL:
-
GilaMonster (12/1/2015)
polkadot (12/1/2015)
Seriously, does any team really have discussions about these things?Yes, good teams do, because they know that making time to establish standards and practices saves time later on.
If the team doesn't agree on formatting standards and acceptable layouts, then everyone's going to code their own way and the team members will be wasting time changing formats again and again and struggling to read code, and so they'll be busier than they need to be.
It's the old "I don't have time to sharpen the axe, I have too much wood that needs chopping".
I agree. Doesn't matter what you are coding/scripting in. You should agree on a style guide that you either make as a team or you adopt someone elses.
For example, I couldn't agree on one so I adopted Google's style guide for Python. The end result was something that looked like this for my Python code.
Ref: Google Style Guide
-
Hi Grant, the red-gate SQL Server Team Based Development book I downloaded for free, Chapter 1, is very relevant to this topic....and very funny.
'Vowel Movement' referring to the practice of abbreviating object names by leaving out all vowels. So in stead of calling a table Subscriptions....it's Sbscrptn. "Abrvtng mdnss" . So funny.
All of chapter one is worth reading just because it is so funny.
xsevensixzx, I find brackets looks nice but is hard for me to do with proper typing technique (hard on pinky) and so many extra characters. But looks nice.
--Quote me
-
polkadot (12/1/2015)
xsevensixzx, I find brackets looks nice but is hard for me to do with proper typing technique (hard on pinky) and so many extra characters. But looks nice.Yeppers, that's why so many dislike it. But, I still find it worth the effort for my own personal style guide. That's why it's important to talk with the team and come to a consensus that makes sense for both the team and the business.
I like to reference Python here simply because they have some pretty neat ones. Simple recommendations that do include extra characters, but for the sacrifice of readable code such as.
My_Array = [1,2,3,4]
versus
MyArray = [1, 2, 3, 4]
-
I'm to the formatting part of the Redgate Manual.
EG. 'we need to decide what comprises a change in the nesting level that things get difficult'
So, thanks Luis and xsevensinzx.... for the examples on indenting queries and subqueries. It's exactly the kind of help I was asking for.
And for anyone else who may read this whole thread, here are some additional concrete helpful ideas for formatting READABLE CODE, taken from the Redgate Manual co-authored and suggested as a read by Grant Fritchey.
• Keep your database case-insensitive, even if your data has to be case-sensitive, unless
you are developing in a language for which this is inappropriate.
• Capitalize all the Scalars and Schema object names (e.g. Invoice, Basket, Customer,
CustomerBase, Ledger).
• Uppercase all reserved words (such as SELECT, WITH, PIVOT, FROM, WHERE), including
functions and data types.
• Put a line-break between list items only when each list item averages more than thirty
or so characters.
• Put block delimiters (such as BEGIN and END) on a new line by themselves, correctly
indented.
• Put line breaks within SQL statements before the clause (FROM, ON, WHERE, HAVING,
GROUP BY) only where it aids clarity in long statements, but not in every case.
• Use the semicolon to aid the reading of code, even where SQL syntax states that it is
only optional.
• Use an increased indent for subordinate clauses if the ON, INTO, and HAVING
Oh, and I can't wait to have that sit down with my team where we have THE TALK about formatting.:hehe:
--Quote me
-
Glad the book is proving useful. If you are interested in the aspects of the book not covered in the first chapter, database deployment, automation, source control, etc., we have a new book we're working on: Database Lifecycle Management. We're using a lean, iterative approach to creating a book on using lean, iterative, approaches for database development. Should be fun.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (12/2/2015)
Glad the book is proving useful. If you are interested in the aspects of the book not covered in the first chapter, database deployment, automation, source control, etc., we have a new book we're working on: Database Lifecycle Management. We're using a lean, iterative approach to creating a book on using lean, iterative, approaches for database development. Should be fun.Waiting for this one. I have found it challenging to design and maintain database especially when more and more team started favoring agile.
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply