Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

I'm just altering the COMPUTE expression of a table with existing data. (This is V12.0.0.2601.)

As documented, this re-computes not only the particular column addressed in the ALTER TABLE ... SET COMPUTE statement but does re-compute all computed columns in this table (and in this case, this is absolutely wanted behaviour:).

Since the table has the usual DEFAULT TIMESTAMP column, I wasn't sure if this column is changed, too - however, it isn't.

Is this expected behaviour for DEFAULT TIMESTAMP, i.e. will those values only change by UPDATE statements, but not by those data modifications as result of an ALTER TABLE statement (e.g. altering computed values, adding columns with defaults and the like)?

I'm just asking for a better understanding...

asked 05 Jan '11, 15:20

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 06 Jan '11, 09:08

Being no iAnywhere staff member, obviously I can't claim whether this is intended behaviour or not.

That being said, I suggest that the current behaviour should be documented, as changing rows by ALTERing the table seems quite similar to changing rows by updates. Therefore I guess my question is not too uncommon.

However, I bet in most cases DEFAULT TIMESTAMP is used to track changes by ordinary DML statements - in the sense of "when was that row modified at last". It's usually meant for changes by users / application programs.

When doing clean-up or other administrative tasks in such tables, I often want the particular DEFAULT TIMESTAMP column to stay unchanged to "hide" my adminstrative changes. That's easily done with an UPDATE statement that sets the DEFAULT TIMESTAMP column to its current value. And as usually, if the UPDATE statement specifies a value for this column, the default value isn't applied.

So, with UPDATE, I'm quite free to choose whether I want DEFAULT TIMESTAMP to be updated or not.

However, if ALTER TABLE would change the DEFAULT TIMESTAMP, there wouldn't be a way to prevent this change (at least without an enhanced syntax). Therefore the current behaviour seems reasonable as

  1. table alterations (as truly administrative tasks) might usually not be meant to change the timestamp and

  2. in case the timestamp should be changed, one can still achieve this by adding an according UPDATE STATEMENT afterwards.

  3. Furthermore an ALTER TABLE with impact on DEFAULT TIMESTAMP could raise problems in replication/synchronization setups: In such setups, ALTER TABLE is usually done at different times at different locations for the same (distributed) rows, and that would lead to multiple changes of the timestamp column for the same row. That might be difficult to handle. At least, such a behaviour doesn't seem senseful to me.

permanent link

answered 07 Jan '11, 22:28

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 08 Jan '11, 15:37

I think ALTER TABLE should not affect DEFAULT TIMESTAMP columns for the very reason yo've described ;).

(09 Jan '11, 17:15) Dmitri

@Dmitri: That's exactly what I have tried to express: Yes, the current behaviour seems senseful - it should just be documented...

(09 Jan '11, 20:56) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 05 Jan '11, 15:20

question was seen: 3,125 times

last updated: 08 Jan '11, 15:37