Instead of working, I blog.

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