Thursday, October 16, 2008

Select *

In my day job I am bumping my head into a giant frustration. It involves databases and good programming practice. Most of you will probably want to ignore this because I am going to address it with a certain level of knowledge assumed, a level probably higher than most casual readers.

When writing a program (not ad-hoc queries) do you use default field ordering in your SQL? That is do write code such as: select * into field1, field2, field3 from table_name or insert into table tableX (select * from tableX1); or similar? I never do, and when asked to do otherwise I make at least a certain level of protest.

I recently had to deal with the consequences of such style and am getting complaints about the need to write explicit field specifications. I have written programs for IMS and the slightest change to that database and you are rewriting any application dependent on the changed node, it's children, or even nodes on the same level. Why would anyone want to perpetuate such code brittleness?

One of the big advantages of SQL and relational databases is one can delink code from the database structure, depending on default sort orders (another coder-sin I have seen in this shop all too frequently) and on default field selection order is throwing a huge benefit of relational databases out of the window and increasing maintenance work.

I can understand the temptation, it is a less work to code select * than to code select fld1, fld2, fld3, fld4,...,fld121... but that is why GOD made various methods of copy and paste and if you are using ISPF then you have even less excuse. Still, given the huge overhead involved with documentation and review the idea of five extra minutes of cutting, pasting, and substituting is a lot less effort than days of documenting and reviewing.

It all reminds of a The Jetson's episode. George and Henry get called up into the army reserves. They go to camp and foul up and get assigned to KP. They are ordered to peel potatoes, wow did they gripe about that work. Under much sourness George walks over to the potato mashing station, pushes a button and the potatoes are peeled & mashed. No matter how simple it is do something sooner or later it will become too much effort to do correct.