This has been a good chuckle, Andy. Thanks for this article. Your story is, hands down, far worse than anything I have experienced.
I am sure you all have seen these examples, but some of the things I have had to deal with are:
- No foreign keys on half the tables.
- Tables with over 20 varchar(1000) columns.
- Over use of cursors.
- Nearly all application logic in the stored procedures.
- And Jeff Moden’s [/url]favorite: RBAR[/url]. Where the code that Jeff talks about is a standard practice.
That is just to name a few. I also find some of the naming conventions used very humorous, too.
Like FK_foreigntable. Or FK_thistable. Yes, literally "thistable".
And the most humorous one was where a cursor was used to loop through rows on the left table to find matching rows on the right table. The comments in the stored procedure actually used the words "left table " and "right table". LOL. Obviously, the creator was new to the game and did know about joins.
On a serious note, I think you said it, Andy:
Andy Warren (6/11/2010)
JBailey, I don't know if it's as simple as that, but I think in general we tend to see a focus on just storing the data and not on using all the other things that can insure both data integrity and performance. Not necessarily fair to expect a developer to know all those things, but if they don't, then it's a mistake not to engage a DBA to assist. The good news in your example is that those fixes are fairly cheap compared to changing table design.
I too see a "focus on just storing the data and not on using all the other things that can insure both data integrity and performance." I don't expect developers to know everything, but I do expect the basics, like putting a foreign key on a table. I think it benefits us DBAs in the long run to educate and help developers use “all the other things that can insure both data integrity and performance”. After all, the better the code that goes into production, the easier it is to administer.
We have a DBA review process in our SDLC where I can provide some guidance, if needed. And if there are any table additions or modifications, the review is required. We also have code review where the DBA can review the final SQL code.