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?