Its been a long time since I’ve written anything here. I’ve been extremely busy with my family move to the bay area. I still can’t believe the amount of paperwork required. I’ve filled virtually hundreds of forms and it’s not over yet. But, after a month here, I can say that we’ve finally settled down. Kids go to school, house is almost fully organized, my wife and I got our iPhones 3G 🙂

House picture Garden

Anyway, back to the subject of this entry – weird statements you see coming from applications when monitoring databases.

  • I’m still amazed to see the number of statements doing things like ‘where 1=1’ just out of sheer laziness of the programmer to check if the condition to append to a dynamic query is the first or the second. It’s not like this really hurts performance on mature databases because the optimizer will strip such predicates away when evaluating the execution plan, but those statements can really throw off a security solution trying to alert on SQL injection. Seeing such statements from applications written by database vendors (you know who you are) can really get me frustrated!
  • Another oddity I mostly see on MS SQL Server databases is the tendency to dynamically create stored procedures on the fly, and then call them to do simple things like updates and inserts. Does anyone really think that this is more secure or provides better performance than simply running the statement?
  • An anti-design pattern I’ve seen many times is choosing the ID of the next row by selecting max(id) + 1 from the table. It really made me laugh when I’ve seen this code in one particular instance responsible for adding rows to the audit table! For example, in a highly transactional environment, two sessions can perform select max(id) + 1 in the same time receiving the same number. Trying to use this as a new id will succeed in one session and fail in the other one thus omitting records from the log.
  • Enough was written about the “When others then null” exception handling…

How about you guys out there? What is the weirdest statement you’ve seen applications perform?