NO AI was used to generate this content. Grammarly was used to check and correct language and sentences in parts.
I just managed to get a post in for this landmark T-SQL Tuesday, hosted by Brent Ozar. Brent was kind enough to keep the submission window open for two weeks instead of the usual one, and I was able to sneak a post in last – minute.
His invitation is to write about the things that immediately stand out as “bad signs” when reviewing a SQL query.
The list below covers a few things that catch my attention. It is far from comprehensive and they’re in no particular order, and with one exception, almost all of them have legitimate use cases. The problem is that they’re often used when there isn’t a legitimate reason, and that’s when they become warnings.
1. SELECT * on a regular table
In most cases I’ve seen, this means retrieving far more data than the query actually needs. There are valid scenarios—small lookup tables, temporary tables, or table variables, for example—but those aren’t the situations where I usually encounter it. Pulling unnecessary columns increases I/O, can lead to less efficient execution plans, and creates avoidable performance overhead.
2. SELECT DISTINCT
Besides the additional work that DISTINCT introduces, it’s often used to hide underlying design or query problems. Instead of fixing incorrect joins or returning the correct rows in the first place, it’s common to slap a DISTINCT on to a poorly written query to remove duplicates after the fact.
3. CTEs and table variables
My first thought is always, what happens when the data grows? Both CTEs and table variables have legitimate uses, especially when working with smaller datasets. However, they aren’t always the best choice as data volume increases.
4. CASE expressions without an ELSE
It’s entirely possible that the data is tightly controlled and every possible condition is accounted for. But omitting an ELSE means unexpected values silently return NULL. That may also be acceptable in some cases, but it’s always something I want to verify rather than assume.
5. Unqualified table names
Referencing tables without explicitly specifying the schema (for example, using Orders instead of dbo.Orders) may work because dbo is the default schema, but I still consider it sloppy coding. It can also lead to unexpected behavior if objects exist under multiple schemas or if the default schema isn’t what the developer assumed.
6. The NOLOCK hint
Enough has already been written about NOLOCK. More often than not, it’s been added as a quick fix without fully understanding the consequences of reading uncommitted data.
7. LIKE predicates with a leading %
Patterns such as LIKE '%Jane' prevent index seeks in many cases and can result in expensive scans.
8. LEFT JOIN followed by a filter that turns it into an INNER JOIN
For example:
SELECT p.ProductID, c.CustomerNameFROM ProductTable AS pLEFT JOIN Customer AS c ON p.CustomerID = c.CustomerIDWHERE c.CustomerID IS NOT NULL;
This query is effectively an INNER JOIN. The WHERE clause filters out all rows where the left join would have returned NULL, eliminating the very behavior that makes it a left join. It’s a simple mistake, but one that shows up surprisingly often in production code.
T-SQL2sday was a genius of an idea started by Adam Machanic and has helped many , many people make significant progress with blogging and learning. I am one of those people. I want to use this opportunity to say thank you to every one of those who participated and keep this going.
