Add Column to Existing SQL Server Table - Consequences

Question

I have an existing table on SQL Server with existing records (over 1 million).

This table is updated, added and selected on a regular basis using the front-end application. I need / need to add a datetime column, e.g. M_DateModified , which can be updated as follows:

 UPDATE Table SET M_DateModified = GETDATE() 

whenever a button is pressed on the leading edge and the called procedure is stored. This column will be added to the existing report upon request.

My problem, and the answer to that. Being one of the main tables of our application, will there be an ALTERING table and adding an additional column that breaks other existing queries? Obviously, you cannot insert into a table without specifying all values ​​for all columns, so any existing INSERT queries will be interrupted (WHICH is a serious problem).

Any help would be greatly appreciated regarding the best solution to this problem.

Answer 1

At first, as marc_s says, it should only affect SELECT * queries, and not even all of them will necessarily be affected.

Secondly, you just need to specify all non-Null fields in INSERT , so if you make it NULL-compatible, you don’t have to worry about that. In addition, for the Created_Date -type column, it is typical to add the DEFAULT =GetDate() parameter, which will fill it for you if it is not specified.

Third, if you are still worried about the impact of your existing code base, follow these steps:

• Rename the table to something like a “physical table”.
• Create a view with the same name as your table that makes SELECT .. FROM physicalTable , listing the columns explicitly and in the same order, but do not include the M_DateModified field in it.
• Leave your code unmodified, now referring to the view, instead of directly accessing the table.

Now your code can safely interact with the table without any changes (SQL DML code cannot determine the difference between a table and a recordable representation like this).

Finally, this “ModifiedDate” column is a common need and is most often handled by first making it NULL-capable, and then adding the Insert and Update trigger, which installs it automatically:

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

Therefore, the application does not have to support the field itself. As an added bonus, no application can install it incorrectly or falsely (this is the usual and acceptable use of triggers in SQL).

Answer 2

If the new column is not mandatory, you have nothing to worry about. Unless you have some knuckleheads that wrote select commands with "*" instead of a list of columns.

Answer 3

Well, if your SELECTs are not *, everything should be fine. For INSERT, if you give the default field GETDATE () and enable NULL, you can exclude it and it will still be filled.

Answer 4

Depends on how your other queries are configured. If they are SELECT [Item1], [Item2], ect .... Then you will not encounter any problems. If this is a SELECT * FROM , you may encounter unexpected results.

Keep in mind how you want to configure it, you will need to set it as nullable, which can give you the option along the way or set a default date, which can give you incorrect data for reports, searches, queries, ect ..