SQL Anti-Patterns • Hacker News Board

Re: SQL Anti-Patterns

#11

Post by readthenotes1 » Sat, Oct 18, 2025, 2:46 PM UTC

wmonk wrote: post #9

The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index. Unfortunately I learned this the hard way!

"Unfortunately I learned this the hard way!" ... Seems to be the motto of SQL developers.

Otoh, it seems a fairly stable language (family of dialects?) so finding the pitfalls has long leverage

Re: SQL Anti-Patterns

#12

Post by LikesPwsh » Sat, Oct 18, 2025, 2:46 PM UTC

jacknews wrote: post #4

"When handling large CASE WHEN statements, it is better to create a dimension table or view, ideally sourced from the landed table where the original status column is populated." Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.

"Dimension table" is the name for lookup tables in a star or snowflake schema.

Re: SQL Anti-Patterns

#13

Post by megaman821 » Sat, Oct 18, 2025, 2:48 PM UTC

chongli wrote: post #7

When working with larger enterprise software, it is common to have large CASE WHEN statements translating application status codes into plain English. For example, status code 1 could mean the item is out of stock. Why wouldn’t you store this information in a table and query it when you need it? What if you need to support other languages? With a table you can just add more columns for more languages!

I usually use generated columns for this. It still uses CASE WHEN but it is obvious to all consumers of the table that it exists.

Re: SQL Anti-Patterns

#14

Post by EvanAnderson » Sat, Oct 18, 2025, 2:51 PM UTC

> 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.

Re: SQL Anti-Patterns

#15

Post by Sesse__ » Sat, Oct 18, 2025, 2:54 PM UTC

EvanAnderson wrote: post #14

> 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 just doesn't know how to do semijoins in SQL, since they don't follow the same syntax as normal joins for whatever historical reason.

Re: SQL Anti-Patterns

#16

Post by readthenotes1 » Sat, Oct 18, 2025, 2:54 PM UTC

em500 wrote: post #5

JohnHaugeland wrote: post #3

these aren’t anti patterns. these are just things you shouldn’t do

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 one knows what it's referring to anymore.

(The problem: need a way to refer to concept(s) in a pithy way. The solution: make up or reuse an existing word/phrase to incorporate the concept(s) by reference so that it can can, unambiguously, be used as a replacement for the longer description. )

Re: SQL Anti-Patterns

#17

Post by [deleted] » Sat, Oct 18, 2025, 2:55 PM UTC

wmonk wrote: post #9

The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index. Unfortunately I learned this the hard way!

[deleted]

Re: SQL Anti-Patterns

#18

Post by jacknews » Sat, Oct 18, 2025, 2:55 PM UTC

LikesPwsh wrote: post #12

jacknews wrote: post #4

"When handling large CASE WHEN statements, it is better to create a dimension table or view, ideally sourced from the landed table where the original status column is populated." Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.

"Dimension table" is the name for lookup tables in a star or snowflake schema.

TIL, Thanks.

'Landed table'? Is that the 'fact table', the one that contains the codes that need to be looked-up?

Re: SQL Anti-Patterns

#19

Post by dgb23 » Sat, Oct 18, 2025, 2:56 PM UTC

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change.

Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

Re: SQL Anti-Patterns

#20

Post by parpfish » Sat, Oct 18, 2025, 2:56 PM UTC

jacknews wrote: post #4

"When handling large CASE WHEN statements, it is better to create a dimension table or view, ideally sourced from the landed table where the original status column is populated." Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.

but sometimes large case statements cant be turned into a simple dimension table/lookup table because it's not a simple key-value transformation.

if your case statement is just a series of straighahead "WHEN x=this THEN that", you're very lucky.

the nasty case statements are the ones were the when expression sometimes uses different pieces of data and/or the ordering of the statements is important.