CODEWITHSUNDEEP
c#asp.netsqlsql-server
frans
frans

Reputation: 181

Add column to existing SQL Server table - Implications

I have an existing table in SQL Server with existing entries (over 1 million in fact).

This table gets updated, inserted and selected from on a regular basis by a front-end application. I want/need to add a datetime column e.g. M_DateModified that can be updated like so:

UPDATE Table SET M_DateModified = GETDATE()

whenever a button gets pressed on the front-end and a stored procedure gets called. This column will be added to an existing report as requested.

My problem, and answer is this. Being one of the core tables of our app, will ALTERING the table and adding an additional column break other existing queries? Obviously, you can't insert into a table without specifying all values for all columns so any existing INSERT queries will break (WHICH is a massive problem).

Any help would be much appreciated on the best solution regarding this problem.

Upvotes: 6

Views: 3601

Answers (4)

RBarryYoung
RBarryYoung

Reputation: 56725

First, as marc_s says, It should only affect SELECT * queries, and not even all of them would necessarily be affected.

Secondly, you only need to specify all non-Null fields on an INSERT, so if you make it NULL-able, you don't have to worry about that. Further, for a Created_Date-type column, it is typical to add a DEFAULT setting of =GetDate(), which will fill it in for you if it is not specified.

Thirdly, if you are still worried about impacting your existing code-base, then do the following:

  1. Rename your table to something like "physicalTable".
  2. Create a View with the same name that your table formely had, that does a SELECT .. FROM physicalTable, listing the columns explicitly and in the same order, but do not include the M_DateModified field in it.
  3. Leave your code unmodified, now referencing the View, instead of directly accessing the table.

Now your code can safely interact with the table without any changes (SQL DML code cannot tell the difference between a Table and a writeable View like this).

Finally, this kind of "ModifiedDate" column is a common need and is most often handled, first by making it NULL-able, then by adding an Insert & Update trigger that sets it automatically:

UPDATE t
SET    M_DateModified = GetDate()
FROM   (SELECT * FROM physicalTable y JOIN inserted i ON y.PkId = i.PkId) As t

This way the application does not have to maintain the field itself. As an added bonus, neither can the application set it incorrectly or falsely (this is a common and acceptable use of triggers in SQL).

Upvotes: 7

Kevin DeVoe
Kevin DeVoe

Reputation: 602

Depends on how your other queries are set up. If they are SELECT [Item1], [Item2], ect.... Then you won't face any issues. If it's a SELECT * FROM then you may experience some unexpected results.

Keep in mind how you want to set it up, you'll either have to set it to be nullable which could give you fits down the road, or set a default date, which could give you incorrect data for reporting, retrieval, queries, ect..

Upvotes: 1

Jeff Cuscutis
Jeff Cuscutis

Reputation: 11637

Well, as long as your SELECTs are not *, those should be fine. For the INSERTs, if you give the field a default of GETDATE() and allow NULLs, you can exclude it and it will still be filled.

Upvotes: 1

BlueChameleon
BlueChameleon

Reputation: 934

If the new column is not mandantory you have nothing to worry about. Unless you have some knuckleheads who wrote select statements with a "*" instead of column list.

Upvotes: 1

Related Questions