Add column to existing SQL Server table - Implications

Question

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.

Answer 1

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).