asked
SQL practises that iterate... er irritate you.
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.
This resonates with me! I should have prefaced the discussion with the caveat that I'm a mere student of this dark art SQL. I found exactly what you describe in a DB I'm inheriting at the moment. I kept my mouth shut because I wanted to look into it further before passing judgment, just in case it was some uber-advanced technique. Seems to me at the moment, that the GUI designer also put him or herself in charge of DB design and wanted everything to be "flexible". They've even got a table named extra_columns for those times when the core design of the app won't do. So whenever the user decides to collect data on some new concept, it goes into the extra_columns table. Now it's become central to the whole operation.
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 agree, Linq to SQL or any ORM can be extremely abusive. When I first looked (about 8 years ago) at the update script the Command Builder in Framework 1.0 generated, it taught me a lesson I will remember for the rest of my life. The necessity to recompile the binaries is pretty annoying as well. A few weeks ago I submitted the app and the script (create tables, procs, insert seeds etc) to QA. My manager came back to me with request to change some table names. I loaded the script in SSMS, pressed Ctrl-> H, typed what to find and what to replace it with, clicked Replace All, clicked Parse, Execute, saved the script while she was still watching and reported that this was done. She asked me how long will it now take me to make the changes to my code, and I could see that she looked at me in misbelief when I said that I did not have to change anything, because I was done. She knew of course that apps with no embedded SQL is the way to go, she just could not believe that I **actually** had none of it in the app.
I have to partially agree. I have very mixed feelings about ORMs. They can be useful in some limited circumstances, but they come at a performance price. With that said, I fully expect them to evolve into something much more useful than they are now.
Hyphens in object names & database names. And, let's be brutally honest, I'm not just looking at the in-house developers here, but 3rd party consultancies who should know better.
Scheduled tasks & jobs whose name is a guid.
@WilliamD At least you can reduce the aggravation (and number of page splits) by defaulting values for inserts to newsequentialid() and prohibiting front ends from feeding the values. Want a slow system? Check this one out: the clustered PK is meant to be GUID, has no default (so front end must feed it), but is actually a **varchar(36)**, yep, a **var-bloody-char(36)**. No, I am not joking. This is the actual daymare I am trying to redesign now. For example, a table like this with 10 mln records and 5 non-clustered indexes generates 220 MB of extra table space 1.1 GB of extra index space when compared to GUID, totalling 1.32 GB of waste for no good reason :( :(
Ah, that's where it came from. And I was mentally blaming a combination of Dynamics, BizTalk, Sharepoint and a certain implementation services consultancy...
Doesn't the 2005 installer do that to install remote cluster nodes? lol
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... )
I think they are trying to overcompensate for something else. :) Size does matter, but larger is not what you should prefere. How often does the size depend on columns sizes and/or the number of columns? Each record in the largest table that i mentioned earlier was 1KB. ONLY 7-8 records / page!!!
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.
Heh. I've had similar, except in reverse. "Can you just see if you can run reports off on this? Your two predecessors said it couldn't be done". Well, I like a challenge. An hour later, I emailed the boss with a couple of sample reports. OK, the SQL wasn't pretty (the joys of storing XML in a TEXT field leading to lots of conversions, and with a mix of XML formats leading to a few REPLACE statements being required...), but it worked.
- 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 auto shrink for DB files is very unwise, but autogrowth is very often a good idea. Also, whil I agree that xp_cmdshell should be treated with great caution and enabled only if necessary, I have seen cases where it is tremendously useful.
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!
I have to say I am a spaces and not tabs kind of guy. Not least because using Alt+ selections doesn't really work if you're using tabs, and Alt+ selections totally rock my world. Although I did meet a guy once (a C coder) who used 'Alice in Wonderland' style - a tab width of 1 space... That sucked!
I got 2 votes! That's why the web rocks - however weird you are, you can see you're not the only one.
@David Wimbush - I am *so* with you on the operator precedence thing.
Procedures / Views / Functions with no formatting (The entire query on one line).
+1 i agree, but that problem is easy to fix with Red-gate refactor
