Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Worst Practice - Adding a Column Without Thinking! Expand / Collapse
Author
Message
Posted Sunday, February 15, 2004 6:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 9:01 AM
Points: 6,705, Visits: 1,680
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/c

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #100416
Posted Wednesday, February 18, 2004 11:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 223, Visits: 128

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.

 




Andy.
Post #101095
Posted Thursday, February 19, 2004 12:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 07, 2007 3:19 AM
Points: 2, Visits: 1
Great article! This handy checklist will save me a lot of time - thanks!


Post #101098
Posted Thursday, February 19, 2004 2:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 2,865, Visits: 1,705

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #101109
Posted Thursday, February 19, 2004 6:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027

VERY NICE !

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
Post #101155
Posted Thursday, February 19, 2004 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 27, 2006 12:41 PM
Points: 29, Visits: 1

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?




Post #101201
Posted Thursday, February 19, 2004 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 2,865, Visits: 1,705

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



LinkedIn Profile
Newbie on www.simple-talk.com
Post #101204
Posted Thursday, February 19, 2004 10:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

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



*****************/
Post #101242
Posted Wednesday, March 24, 2004 2:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 03, 2013 8:20 PM
Points: 223, Visits: 83

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.


Cheers,

-m




Post #108077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse