71–80 of 168 posts
I remember doing the "WHERE 1=1" trick in my last job and it causing a... let's say "unproductive", discussion in the pull-request.
What about `WHERE true`?
Earlier quoted context omitted.
Relevant for applications as well, when a table only has a few thousand entries, a scan is not the end of the world and not even an outage in waiting. I agree with you that one should seek when possible as part of normal query optimization, but depending on your data, it could also just easily be something you can live with forever.
You can’t control the growth rate of your tables, you can only estimate it. When we design for reliability we want to remove single cause failures and make a best effort to reduce dual cause failures. We definitely don’t want two failures from a single cause. What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables migh…
Increased storage and slower inserts?
Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
What is a dynamic, adhoc query? Why does adding 1=1 support that?
The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.
Who splits column per line in the SELECT block and still leave 150 character wide lines? This is a fucked up definition of legibility. I can’t even get started on the commas. NOBODY CHECKS LONG LINES IN CODE REVIEWS. That was the biggest problem with AngularJS. People mishandling merges and breaking everything because the eyes start to glaze over at column 90. I’ve been on more than half a dozen teams with CRs and it…
SQL, unfortunately, is very verbose and has a strange mix of super-high and very low abstraction. There is also no SQL formatter out there that does a decent job, and no real consensus about how good SQL is supposed to look.
If I look at the 'indent' guideline, it contains e.g.:
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
NULL) AS C28_fta_share
Earlier quoted context omitted.
What about `WHERE true`?
I don't understand the point at all. If you need to add some condition later on, why not just add it then? What benefit is there to just marking out the spot where you might add the condition at some point in the future?
Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
Leading comma is nice in SELECT statements because you can comment toggle individual lines. Indenting your code to make it more readable is basically what anyone with room temperature IQ does automatically. A lot of these other tips look like they're designed to deal with SQL design flaws, like how handling nulls isn't well defined in the spec so it's up to each implementation to do whatever it wants.