91–100 of 222 posts
The biggest SQL antipattern is failing to recognize that SQL is actually a programming language. Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wise…
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)
> three or four layers of subqueries, each one filtering or aggregating the results of the previous one, totaling over 5000 lines of code In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?
This is not a pipeline in the control flow sense; the full query is compiled into a single processing statement, and the query compiler is free to remove and/or reorder any of the subqueries as it sees fit. The intermediate results during query execution (e.g. temp table spools) do not follow the structure of the original query, as CTEs and subqueries are not execution boundaries. It's more accurate to compare this t…
In some languages, a series of assignments and a large expression will often compile to the same thing, but if written as assignments, it will make it easier to set breakpoints.
User Defined Functions (UDFs) are another option to consolidate the logic in one place.
> Using Functions on Indexed Columns
In other words, the query is not sargable [0]
> Overusing DISTINCT to “Fix” Duplicates
Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in order to conform the output to the table grain:
ROW_NUMBER() OVER (PARTITION BY ORDER BY ) = 1
[0] https://en.wikipedia.org/wiki/Sargable
[1] https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_cla...
The biggest SQL antipattern is failing to recognize that SQL is actually a programming language. Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wise…
Style opinions are borderline irrelevant without appropriate linters.
How that auto-formatter indents is borderly almost a hate crime. A thousand times better to indent manually.
Earlier quoted context omitted.
Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
I am surprised at common it is for software engineers to not treat booleans properly. I can’t tell you how many times if seen ‘if(IsFoo(X) != false)’ It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.
`if(X&IsFooMask != 0)`
:)
Earlier quoted context omitted.
Still waiting for the definitive article on how using the term anti-pattern is an anti-pattern.
If a pattern is a common problem (e.g., becoming accustomed to a spectacular view) and generally-useful solution to that problem (blocking the view so that effort is required to obtain it), then an anti-pattern is what? I think most people think an anti-pattern is an aberration in the "solution" section that creates more problems. So here, the anti-pattern is that people use a term so casually (e.g., DevOps) that no…
it isn't, is the thing.
if you read the book design patterns, they spell out what a pattern is.
if you read the book anti-patterns, he spells out what an anti-pattern is.
people have gotten the wrong idea by learning the phrases from casual usage.