SQL Anti-Patterns
datamethods.substack.com
SQL Anti-Patterns
1–10 of 222 posts
Re: SQL Anti-Patterns
#2That’s my rap sheet…
Re: SQL Anti-Patterns
#3these aren’t anti patterns. these are just things you shouldn’t do
Re: SQL Anti-Patterns
#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.
Re: SQL Anti-Patterns
#5these 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.
Re: SQL Anti-Patterns
#6We did the views on view thing once when triggers, at least how we implemented them failed. This became a huge regret that we lived with for years and not-so affectionately called "view mountain". We finally slayed viewed mountain over the last 2 years and it feels so good.
Re: SQL Anti-Patterns
#7When 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!
Re: SQL Anti-Patterns
#8"Instead you should:
query WHERE name = ‘abc’
create an indexed UPPER(name) column"
Should there be an "or" between these 2 points, or am I missing something? Why create an UPPER index column and not use it?
Re: SQL Anti-Patterns
#9The 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!
Re: SQL Anti-Patterns
#10The 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!
Some well known docs on the topic- https://use-the-index-luke.com/sql/where-clause/obfuscation