Imagine you've just gone into a new work environment and you've inherited years of somebody else's work... and they've been a bit of a lone wolf (lets be polite). You take a bit of a look around at some databases, a few stored procs, some functions, a script or two and to your horror you discover... Whilst on the surface this seems like a bit of levity, it could be compiled into a really useful checklist to help you BEFORE you get to the point of handing over your SQL work to somebody else. We'll all have to do it one day. We can take the approach "it's not my problem anymore - worked fine for me, mostly" or we can see ourselves as custodians rather than owners and make it a goal to leave it in better shape than we found it in. To get the ball rolling here are a few of my pet hates: - Heaps of commenting... but only the commenting out of code that should have been removed before going into production. No comments that actually explain what you're doing, why you're doing it, when you did it and who you are. - Column names like field1 field1a field2 - No test or dev environment, just verbal instructions that "you need to be really, really careful when you do this". - A work environment where nobody talks to each other, or shares code, ideas and so on. - All those 400-line long case statements that should really be lookup tables. - Cursors. Or more to the point, being told to keep using them because "the code's easier to read" than a join to a tally table (this happened quite recently). - varchar(1) - space characters and keywords in db object names
Fields whose usage (and, indeed, valid data types & ranges) depend upon the value held in another field. Seriously - sometimes it was a phone number, sometimes a reference to another record, sometimes a string, a line of an address, some random jottings. That table was a complete frickin' nightmare. And the worst thing about it was that it was absolutely key to the whole damned system.
I'll second all of the ones you've listed and add a couple: 1. Starting user stored procedures with 'sp_'. 2. Using dynamic SQL where there is no reason for it. 3. Writing long creation sequences for a string of dynamic sql, and then not executing it.
Using Linq to SQL or any ORM for that matter and putting all your queries in source code. 1. Query logic existing at a layer on top of T-SQL makes them almost impossible to optimize. 2. Make a change and you now have to recompile the app and redeploy instead of just modifying a stored procedure. I will firmly say, if you make the decision to use Linq to SQL or another ORM, *you* have no right to complain about performance or resource contention issues
I hate too large datatypes, like decimal(25,5) to store the cost for a single phonecall. I recently decreased the size of a database to half the size (from 1TB) by using smaller datatypes ( and I still think they are too large, but... )
Mine is 'future proof design' that actually means 'loosely typed schema'. I have to say, I have been guilty of this in the past. However, I am glad to say I've learnt that particular lesson the hard way. Another is when people over-use XML columns, because they really haven't got a clue what will be stored. The other day I asked the question 'So, can we just run a quick query to find out which devices have property X set?'. Came the answer, 'No, it's all in the XML'. I didn't really start about XPath queries or XML document schemas, I just sat there quietly and remembered that I'm only a contractor... Then I enjoyed going for a wee and working out how much I had earned.
- Poor Database Design (Too much normalization, or Too much demineralization) - Improper Data types and Lengths (Causes Performance degrade because of increased data pages results more logical/physical reads - Poor Index design hurts write operation - improper file placement of log file and the data files - Poor naming conventions for database objects - failure in clearing old jobs - Including '*' in the queries - Improper use of cursors and Temp tables - Not maintaining error log tables for the SP's - Avoiding transactions for multiple update/insert/Delete operations - Not maintaining documentations for the DB objects - Not set based approach - Setting the Auto shrink, auto growth on for the DB files - Not having any maintenance jobs for indexes, Statistics - Not archiving the old data - Not having any backup plans and backup testing plans - Enabling XP_CMDSHELL
I agree that the stuff you guys list are more important than this but(!) I really hate badly laid out SQL. When it's a big gnarly proc and the code looks like a two-year old randomly battered the arrow and tab keys. (Don't get me started on people who indent with spaces.) Sure it takes several minutes you haven't got to reformat it afterwards, but it really doesn't take any extra time to keep it organised as you go. Oh, and 'advanced' people who would rather read up on operator precedence than just put some %ing parentheses in to make it clear: `IF A + B / C * 1.175 > D AND E = F OR E = G`. Lovely!