Worst Practice - Adding a Column Without Thinking!

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/c

  • Adding a column in a hurry without doing checks!

    Been there! Done that! and will NEVER DO IT AGAIN! It can cause total destruction and cost you more time in the end.



  • Great article! This handy checklist will save me a lot of time - thanks!

  • How about checking to see if the new column is actually needed?

    Look at the design of what is there already and ask if it can encompass the need without the adding the extra column.

    A good example of this is the need to add BIT columns.  As you can't index them anyway you may as well have a generic Flags field as an integer and use a bit pattern.  This way your existing design allows for additional flags (bits) at a later date.

    I'm just auditing an external application and it appears that there are at least 4 ways of marking a record as being restricted from public viewing. 

    It looks like the database was built by 4 natural antagonists with a dislike for computers.


    I wish my boss would read it every time I am asked to add one and "it shouldn't take me more than a couple of seconds" comes after that

    * Noel

  • I end up scripting just the CREATE component of all views, pocs, functions and then replacint CREATE with ALTER and runing the script in QA to incorporate all column references because it seems like the columns in a view are stored as a positional reference to the column order in the related table, rather than to a table_name.field_name reference.  Does the sp_refreshviews do the same thing?

  • sp_refreshview recompiles the view which achieves the same thing as running an ALTER statement

  • How about "Does the column make sense logically?"  i.e. will the column take my BCNF table(s) and turn them into an unnormalized mess of redundancy and inconsistency? 

    This, of course, assumes that the database was designed properly in the first place.


    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek


  • What about DTS packages?  I've had developers add columns and forget to tell me, then they wonder why things aren't working correctly when we push to production.  Maddening.



Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply