SQLServerCentral Article

Worst Practice - Adding a Column Without Thinking!


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


  • 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."


  • 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?



4 (2)

You rated this post out of 5. Change rating




4 (2)

You rated this post out of 5. Change rating