November 25, 2015 at 11:47 am
I have been told my queries are hard to read...
Besides using #comments to explain blocks of code,
does anyone know of a primer that advises on good technique for
consistent indenting and spacing ie. visual presentation 'rules of thumb' for sql query syntax
to make it a pleasure to read
by others?
--Quote me
November 25, 2015 at 11:52 am
Why don't you show us an example of a query that has been labelled as hard to read?
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]
November 25, 2015 at 3:04 pm
The way in which queries are formatted is very subjective. Some people like this:
select col1, col2, col3
from mytable
inner join myothertable on col1 = myfk
where time < getdate()
others prefer more indentation.
select col1, col2, col3
from mytable
inner join myothertable on col1 = myfk
where time < getdate()
still others want better separation.
select
col1
, col2
, col3
from mytable
inner join myothertable
on col1 = myfk
where time < getdate()
I think I'd ask people what is hard to read in the company. Perhaps there are things you can do to adapt. The other thing is to use a tool like SQL Prompt from Redgate Software to allow reformatting. Then if someone doesn't like your format, they just change it to theirs.
And you can change it back.
Disclosure: I work for Redgate Software.
November 25, 2015 at 11:52 pm
Decent, consistent layout, meaningful aliases, useful variable names.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2015 at 11:02 pm
There is an older book available from Redgate called The Guide to SQL Server Team Development[/url]. It's free to download. It has a chapter on coding standards. The rest of the book is somewhat out of date.
The main point is clarity. Consistent aliases is one. For example, this should not be done:
SELECT ...
FROM SomeTable a
JOIN SomeOtherTable b
ON a.id=b.id
JOIN YetAnotherTable c
ON b.id = c.id
WHERE...;
SELECT ...
FROM DifferentTable a
JOIN NotTheSameTable b
ON a.id = b.id
WHERE...;
You're using the same aliases for different tables. In a really tiny query like this, no big deal. In a larger query it makes everything harder to read and understand. Clarity is key. Something like this:
SELECT ...
FROM SomeTable AS st
JOIN SomeOtherTable AS sot
ON st.id=sot.id
JOIN YetAnotherTable AS yat
ON sot.id = yat.id
WHERE...;
SELECT ...
FROM DifferentTable AS dt
JOIN NotTheSameTable AS ntst
ON dt.id = ntst.id
WHERE...;
Every time you reference a table, use the same alias. That consistency will make all your code more readable. I also break they queries vertically as you can see. I use the AS keyword to mark aliases because it makes the code more clear. I always terminate each statement with a semi-colon since more and more that's a requirement within SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2015 at 2:43 am
For formatting, instead of buying a tool, you can also use the following web site: http://www.dpriver.com/pp/sqlformat.htm
November 30, 2015 at 3:07 am
Other free formatting websites are available - http://www.format-sql.com (from Red Gate), or http://www.sql-format.com (from DevArt)
Take your pick. But be consistent - they all have their own rules. Unlike SQLPrompt, or other client-side tools, though, you can't change the formatter settings in RedGate's tool, but you can make format rule changes with the other.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 30, 2015 at 9:38 am
The links are very appreciated. I'll take a look
In my case the complaints have been about indentation and spacing akin to what one might expect from an outline for an essay. Main topic intro at top, supporting arguments each having own paragraph (indented!), and each point supporting main idea of paragraph indented further.....that sort of thing.
Main idea
----->1.Supporting Argument 1
---------->a.
---------->b.
---------->c.
----->1.Supporting Argument 2
---------->a.
---------->b.
---------->c.
----->1.Supporting Argument 3
---------->a.
---------->b.
Conclusion
So that when someone looks, the organization just looks appealing. it would be great to have rules like, subqueries should always be indented 15 spaces, JOINS for outer queries 5 space, JOINS for inner queries 10......etc.
I will review the materials you all suggested and thanks for taking it seriously!
--Quote me
November 30, 2015 at 9:41 am
Those rules should be agreed with your team. There's no hard and fast rules there, I would indent differently to how Grant did in the queries above. Neither of us is wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2015 at 12:37 pm
GilaMonster (11/30/2015)
Those rules should be agreed with your team. There's no hard and fast rules there, I would indent differently to how Grant did in the queries above. Neither of us is wrong.
I didn't indent those queries...
Oh... never mind.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2015 at 4:08 pm
GilaMonster (11/30/2015)
Those rules should be agreed with your team.
What Gail wrote above is the most important. I'll also [font="Arial Black"]STRONGLY [/font]recommend that the "Team" determine a "standard" and write it into a document or a WIKI that all must follow and enforce. I have to tell you that it's absolutely worth doing and agreeing upon and it'll save a ton of time during troubleshooting and, once people get used to it, during Development.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2015 at 4:58 am
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.
So, that's why I posted.
With the good feedback I got here I could develop a great personal style and make a proactive effort to develop the standard.
--Quote me
December 1, 2015 at 5:14 am
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".
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2015 at 7:13 am
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".
Piling on.
Absolutely they do. You want to arrive at a place where you're improving and smoothing communication. Sure, there are little details like commas in front or trailing (trailing of course) that don't really matter. But you have to agree on lots of stuff, abbreviations, key words, indents & line breaks and all the other methodologies. Yes, the team should talk about this and then refresh it occasionally to ensure things are working well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2015 at 7:20 am
I'd say that plenty of teams argue about names and formats. Whether it's formal or informal, each of us has preferences.
Agreeing on some is good, but also have tools for reformatting code helps. That way individuals can review code without worrying about the way the last person laid it out.
Naming, however, is a different issue. We all deal with naming, and with aliases, so I would standardize those. If we encounter a new situation, say for a Service Broker Activation Procedure, call a 5 minute meeting and decide on a name.
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply