Introduction
Today, I will be talking about some of the common and high impact SQL anti-patterns I have seen from experience that can make queries and pipelines difficult to maintain, or have slower than expected performance. These issues can compound, causing erosion in trust in data, and slower query development in general.
Mishandling Excessive Case When Statements
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. Now imagine there are hundreds of status codes that need to be categorized after talking to a stakeholder who requested a dashboard to track item statuses in your warehouse.
Because you need to get this dashboard developed quickly, you decide to place this large CASE WHEN logic only within a view for that specific dashboard. This is an anti-pattern because now other developers will either have to copy and paste your logic or skip it entirely and use the raw status codes. Over time, this creates a huge mess. I know because I have seen it happen.
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. This way, the logic is accessible and consistent for everyone.
Using Functions on Indexed Columns
In SQL server especially, it’s pretty easy to work against the index if you are not careful.
For example: WHERE UPPER(name) = ‘ABC’
Instead you should:
... continue reading