Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Worst Practice - Adding a Column Without Thinking!

By Andy Warren,

It's funny how the smallest things can trip you up. Stop and think for a minute - if one of your developers requests a column be added to a table, how much effort do you (or should you) expend to make sure it won't break anything? Some things you might consider:

  • Is the table replicated? If so, should the new column be added to the article and pushed to the subscribers, or just added to the underlying table?
  • Are you auditing the table with triggers? If you're inserting the entire row into some type of history table you'll need to modify the history table first, then the "real" table, then modify the triggers.
  • Should the column be included in any views that currently reference that table? If they use 'select *' you'll have to run sp_refreshview to get the change to appear. If columns are explicitly stated you'll have to alter the views to include it.
  • Does the new column match your naming convention?
  • Does it have a default value and should the existing records be initialized to that default?
  • Are nulls allowed?
  • Will adding the column exceed the maximum row size?
  • If you're replicating the table, will it exceed the 256 column limit for transactional articles? Or the 246 column/6000 character limit for merge?
  • Is it a bit column? A feature of some versions of Access (see 280730) requires that all bit columns be fully populated - no nulls - or you get the "This record has been changed by another user since you started editing it." error.
  • Should it have a unique constraint? Be indexed?
  • Using the appropriate data type? In particular using a float can lead to issues with updates made via DAO/ADO recordsets using optimistic locking because the rounding error makes it look like the record has changed.
  • Is the same column name being using elsewhere in the same database? This is pretty common when you're adding a foreign key. Unfortunately if someone has referenced both tables in a select but not qualified the column with a table name or alias, you'll get the "ambiguous column name" error.
  • Should a foreign key constraint be defined? If so, should cascading updates/deletes be enabled?

Are there other things you check? Or that have tripped you up?


Total article views: 9868 | Views in the last 30 days: 5
Related Articles

Identity column value should not be increased if there's pbm in insertion of record.

Identity column value should not be increased if there's pbm in insertion of record.


5 Things Every DBA Should NOT Do

Earlier in the month I shared with you 10 Things That Every DBA Should Do and so it only seems natur...


Presentation Files: 10 Things All BI Administrators Should Know

Presentation Files: 10 Things All BI Administrators Should Know I sooooo remember this sign! ...


Validating all the columns in a record

Validating all the columns in a record


unique records based on one column

distinct records based on one column, but records need all columns


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones