6

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.

Improve this question
5
  • 4
    It will break badly designed queries - things like SELECT * FROM .... or INSERT queries that do not explicitly specify a list of columns to insert into ..... Commented Aug 12, 2013 at 13:46
  • 4
    You say "Obviously, you can't insert into a table without specifying all values for all columns". This is not true. Commented Aug 12, 2013 at 13:47
  • 1
    Oops, @RBarryYoung excuse me. I meant with simple inserts you'd get the message "Column name or number of supplied values does not match table definition." If for example you'd only supply 5 values where the table expects 7? Excuse my noobness, I'm new at this :) Commented Aug 12, 2013 at 13:54
  • Ah yes, the default INSERT. I had forgotten about it, but as it's the equivalent of using "*" for an INSERT, you shouldn't do it like that (and now you know why). Specify your columns explicitly. Commented Aug 12, 2013 at 14:08
  • Thanks, will do in future :) Commented Aug 12, 2013 at 14:12

4 Answers 4

Reset to default
7

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

2 Comments

Might very well be that such queries exist. The app is enormous.
or other knuckleheads who write INSERT INTO dbo.Table VALUES(.....) and don't specify the list of columns to use - these inserts will all break if you add a new column - mandatory or not....
1

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.

Improve this answer

Comments

1

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

Improve this answer

1 Comment

That's definetely also something to consider. Considering this will be for reporting. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.