71–80 of 168 posts
I ended up keeping the comments in my source code and removing them via a regex at runtime, but it took me quite a while to figure out why my Perl script failed with a query that ran perfectly when copied and pasted into SSMS.
One of the interesting side effects of using the parser was easier to read SQL.
Earlier quoted context omitted.
1. Why indent the joins? Are table2 and table3 less important than table1? Is table1 special? Is that why it enjoys privileged status in the from-clause? 2. Why place some predicates in the where-clause and others in the join-clauses? What's the thinking here? Why not put all predicates up in the join-clauses, nearer to the tables that they affect?
I think of joins as operators and from as the block similar to and/or in the where clause. He is being inconsistent with line breaks for select, from and where blocks. Personally I want to be able to visually pick out the blocks of the query and the easiest way to do that is with indention imo.
from
table1 t1 join
table2 t2 on t1.col2 = t2.col1 join
table3 t3 on
t1.col3 = t3.col1 and
t3.col2 = something_elseIs there a good sql autoformatter? For cleaning up ORM-generated queries so I can read them. I've used python's sqlparse but it produces output that's often still unreadable.
Am I the only one who puts commas on the next line? I think of them as an operator analogous to AND or ON: select t1.col1 as col1 , t2.col2 as col2 , t2.col3 as col3 It makes commenting out columns painless.
No it does not. If you have to comment out the first column you'll have to remove the comma from the second column. It's the same problem that happens if you put the commas at the end of the line, except that the offending line will be the last column. So putting commas at the beginning just makes it look weird because it differs from how we use commas in natural language.
Is there indent for SQL?
They just released a beta for SSMS 2016 that has a lot more flexible SQL formatting features. See http://www.ssmsboost.com/social/posts/m12279-SSMSBoost-v3-0-....
Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…
Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…
And, like you, be pragmatic. If it is a 1 liner, keep it a 1 liner.
Am I the only one who puts commas on the next line? I think of them as an operator analogous to AND or ON: select t1.col1 as col1 , t2.col2 as col2 , t2.col3 as col3 It makes commenting out columns painless.
> It makes commenting out columns painless. No it does not. If you have to comment out the first column you'll have to remove the comma from the second column. It's the same problem that happens if you put the commas at the end of the line, except that the offending line will be the last column. So putting commas at the beginning just makes it look weird because it differs from how we use commas in natural language.
>First, yes they can be an optimisation boundary. But they can also make your query much more read-able and prevent you from doing the wrong thing because you couldn’t reason about a query.
In my experience, CTEs may be simpler for the writer to reason about, but they make the queries complicated for the reader to understand. If you use a CTE because you find the problem too complicated, chances are that you are not yet understanding the problem well enough to describe it in a single plain SQL query.