Tag Archives: Coding Standards

Uncategorized

10yrs Experience

1 Comment

I’ve interviewed many DBAs and one thing holds true more often than not.  Almost everyone has 10+yrs experience, and almost none of them can show the most basic knowledge of SQL.  So I have to ask myself again and again how it is that such experienced people can manage to not have any knowledge about their field.  I think the answer is that these people don’t really have 10yrs of experience.  What they have is 1yr 10 times.  They never bother learning anything new or pushing their skills so they never get any advanced knowledge.  This is why the big conference speakers can give the same sessions year after year and always pack the big rooms.  Because there are more people out there who need the basics and they don’t even study the session material.  So they’re able to come back year after year and still learn something from the content.  And that’s not to say that the session isn’t fabulous.  It’s just to say that people should be getting tired of it and they’re not.

So you’ve got the bulk of our profession out there doing the bare minimum to survive.  It’s honestly like they’re potty training.  When you’re a kid and you start potty training you have a hard time at first, but once you get it, that’s it.  Once you’re potty trained there are no extra levels.  You either pee in your pants or you don’t.  And that’s how so many DBAs treat their jobs.  They’re learned a very small core of DBs and they think that’s it.  They can stop learning because they’ve potty trained in SQL.  Come on guys… that’s now how it works.  Learning SQL is more like chess.  There’s much more to it than just the basic moves of the pieces.  You eventually have to push yourself and learn to think in different ways. 

So all I wanted to tell all you guys is now that you’ve learned to not pee in your pants, start pushing yourself for more and actually get 10yrs experience, and stop repeating the same basic level of knowledge again and again.

 

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

Uncategorized

Fairy Repellent

One of my big peeves is devs writing code to protect themselves against things that never happen.

You really see it all the time don’t you?  And it always sounds so reasonable at the time, but it really isn’t if you put any thought into it at all.  Here are a couple examples.

We had a dev a while back to introduced a redesign for a job where he put everything pretty much into the same step.  He had a long SP written that combined xp_cmdshell steps and reindexing steps, etc.  It was all just one long strip of code.  And of course his logic sounded logical on the surface, but not once I started asking the right kinds of questions.  See, his reasoning was that since there’s no way in SQL to run a job step by itself and not the steps that come after it, he needed the ability to be able to run any section of that SP he needed.  Ok, that sounds alright because he’s right.  While you can start a job at any step, you can’t tell it to only run a single step unless you play with the workflow in the steps. 

But one simple question killed the whole thing… when’s the last time you needed to run just a single step in this job?  I assume that the steps that follow are there for a reason, right?  So give me a scenario that this would cover.  And of course it’s always the ethereal… I can’t think of anything right now, but I’m trying to cover all the contingencies.  I get that dude, but you’re coding for issues you can’t even state. 

What about when the job fails?  How often does the job fail? 

He said, oh it probably fails a couple times a week for one reason or another. 

Ok, now how easy is it going to be to troubleshoot the failure if everything’s in just in one huge pile?  Shouldn’t you be coding for the case that happens the most?  And of those times it fails say at step 2, do you ever need to run step 2 and nothing after it? 

He said, No. 

, then why are you coding for it? 

He says, well there might be a time when I need to run a single piece of the code for some reason say redoing something in the middle of the day or something.  It hasn’t happened yet, but it could. 

Yeah sure, that could happen.  And if it ever does what’s stopping you from copying the code from the job step you need to run and just running it manually in SSMS? 

To which he replied… Ummmm….

So ok, this is getting long so I’ll leave this at one example, but you get the point.  It’s a lot like buying fairy repellent for your house, and setting fairy traps everywhere.  When’s the last time you had a fairy problem?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

 

Technorati Tags: ,
Uncategorized

No More Select *

Ok, not ‘no more’, but you guys should seriously limit your usage of select * for everything.  I realize that it’s easier to support when you change the data requirement, but it pushes a lot more data than you need and that could really impact network and server performance. 

Let’s say you’re running a web app and you need 3 cols on your page.  And you pull all 37 because it’s just easier to type a * than each column.  That’s fine from your end, but you could seriously impact the server and the network because if one of those cols is really wide, say varchar(200) (or even 400, right…) then you’re taking up that much extra bandwidth and server memory.  Sure it probably won’t effect your session that much now, but when you’ve got 500 people on your site at the same time you’ll start to feel the pain then for sure.

So just code for performance and stop being so lazy about having to type a few chars.  And if you really don’t like typing that much then get yourself a nice code completer like the one from Red-Gate and you won’t have to type nearly as much.  But I’m getting sick of laziness being an excuse for bad coding.

There are some exceptions though.  It is ok to use select * for some things but you have to choose those individually and judiciously.  Let’s say that you’ve got an SP that pulls 65 cols from a complicated set of logic that you don’t wanna have to re-create or maintain separately.  And let’s also say that you only need to query it every now and then, or maybe just 2-3 times a day.  In a case like that, it’s probably ok to go ahead and use the SP even though you’re only using a handful of the resultset.  However, in that same scenario, if you were using that data several times a minute, or even a second, then you’re really better off from a performance perspective to go ahead and create your own SP that returns less data.

Another excuse that gets used is people often tell me that they used select * to make it easier to make changes to the app.  And that is logical to a degree.  But people protect themselves all the time from issues that aren’t issues.  For instance, I had this just a while back where someone gave me that excuse and when I probed, the app had been up for 2yrs and had only ever had one minor change.  So what are you protecting yourself from then?  If the app is fairly static, then grow up and do the right thing.

OK, that’s actually stepping on the toes of another post so I’ll stop here.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

 

Technorati Tags: ,