I've got a table with a few 30 posts, already utilized in the applying extensively. i.e Choose, Place increase procedures with this table written in several ways(in whatever ways the designers thought these were comfortable) in quantity of saved methods and UDFs. I am now handed having a task to increase the functionality that the table serves and I am in need of assistance to include additional detail towards the table(generally could be assumed being an additional column towards the table). Adding additional column towards the table is really a massive and inefficient task I'd rather not do thinking about the impact it'll cause elsewhere.

One other way i'm able to think about now's developing a new table with foreign answer to the primary table and looking after the records within the new table. I am skeptical of by doing this too. What's the efficient way additional kind of adjustments to the schema on the table?

Using SQL Server 2000 just in case it's needed.


Unfortuantely, column shouldn't accept NULL values. Skipped this crucial info indeed

Impacts i believe which could occur because of already implemented poor practices are,

1) "Choose *" and binding with a datagrid straight to front-end. (very really low probable)

2) using Column amounts to fetch from dataset or datatable rather than column names in front-end when utilizing "Choose *"

3) "Place into" with values given sequentially rather than with column names.

By a way, basically could make the column to simply accept "NULL" values(by fine-tuning needs a little) any impact because of the things mentioned above?

I am doubtful of analysisng existing code because quantity of SPs and processes by using this table can encounter 100s.

  1. Develop a new table with the posts you'll need, refer to it as anything you want.
  2. Produce a view, title it just like that old table, and also have it return all of the posts that old table accustomed to.
  3. ???
  4. $

(yes, I understand that this can be confusing for maintenance because lots of DBAs make use of a naming convention for sights: V_Viewname. I never experienced naming a SQL object after which kind of object it's and do not see the advantage of this type of convention)

Request yourself why adding a column might have an enormous impact. Possibly you've queries which use Choose *? Discover why the impact could be significant - then consider individuals to become bugs, and connect them.

More often than not, adding a column shouldn't break anything. Adding a NOT NULL column will affect something that does an Place, but otherwise, there must be little impact in case your database is correctly designed.

EDIT after NOT NULL update

The answer is apparent: add the column as NULL, update the information to incorporate non NULL values for each row, then affect the column to become NOT NULL.

either approach works, using the following caveats:

  • for those who have a Choose * ... somewhere, your brand-new posts will display in the result-set, which might be undesirable, e.g.

    place into #tmpTable choose * from sometable where blah-blah-blah

may cause a mistake unless of course the brand new colums are defined within the temp table

  • utilizing an 'extension' table is gloomier impact but less capable, however, it's the only method guaranteed to not disturb existing saved methods, sights, et al

If adding a brand new column within the existing table isn't acceptable, give a new table in a single-to-one relation using the old table. It will retain the primary key area as with that old table, and also the new column(s). This key area is primary key moreover the brand new table (to enforce one-to-(zero a treadmill) cardinality).

The disadvantage is the fact that:

  • to be able to find new data, you'll need to create a join (outer join really).
  • when placing/upgrading/removing records, you need to do it in 2 tables

I'd add the tables needed and add triggers towards the original one as i refactor the code and also the db.

You would need to assess the impact towards the existing codebase which could be the way to go. Whether it fits within timelines, i quickly usually suggest to really make it right. Whether it is lost of timelines then clearly you simply hack it and connect it another time.

We sometimes can't fix everything and also the only option would be to simply band-aid things.