I’ve landed myself a really big one. I’m trying to sort out a database and application which have things like this in them:-
1. DDL designed (and executed) by the user in the application, not temporary tables, not worktables, not permanent tables in tempdb, not table variables. Permanent user tables in the database that are actually crucially important to the application. None of them have any more indexes on them than a clustered primary key IDENTITY field. Yeah.
2. Use of the ‘One True Lookup Table’. Well two actually! One of these is particularly heinous. Free text data, or optionally, images (ugh) are stored in an image data type field and are then converted & looked up by some really WTF-style UDFs. Urgh, urgh, urgh.
3. Use of an ORM tool to abstract interaction with the databases for the programmers. In a word, yuk. have you ever seen LINQ queries? ‘nuff said. This isn’t even LINQ.
4. Error handling. WTF? The only error the application can ever return is “invalid data”. This covers every single SQL Server error that may get raised. Glorious.
5. Cursors in recursive scalar functions. Yum!
6. Views of views of views of views. Love them, I really do.
7. Use of DISTINCT – just in case!
8. Duplicate data. Duplicate columns! Duplicate duplicates!
9. Cartesian product from a join eliminated by using DISTINCT. Performant!
I think I need a lie down. Wish me luck!