SQL Server Updating a Time Stamp Column

SQL Server updating a time stamp column

You don't

The timestamp column is updated automatically. Perhaps you are under the impression that timestamp contains a value relating to the time? It doesn't, but simply is a number which is updated whenever a value in that record is. Think of it like a row version number.

From MSDN:

The timestamp data type is just an
incrementing number and does not
preserve a date or a time.

How to update sql server timestamp column without changing record data

I don't want to use an integer, because I don't want to have to read the value to increment it.

UPDATE Table SET
    IntColumn = IntColumn + 1

While that does technically require a read, I don't see any problems with it.

You could always just update to the same value:

UPDATE Table SET
    SomeColumn = SomeColumn

which will trigger rowversion update as well.

ADDITION: You could do a view with the max rowversion of the children:

SELECT Parent.*, MaxChildRowVersion as ChildVersion
FROM Parent
JOIN (
    SELECT ParentId, MAX(RowVersion) as MaxChildRowVersion
    FROM Child
    GROUP BY ParentId
) as Child ON
     Parent.ParentId = Child.ParentId

But, no, you can't directly update a rowversion column (though you could implement your own updatable with @@DBTS, binary(8) and INSTEAD OF triggers...)

Could you give examples of your last point? It sounds promising.

No, really it doesn't. ;) It's too much work when you could just update to the same value instead or use a view. Those are the 2 easiest options.

But, to be complete, a binary(8) column with a default of @@DBTS (which returns the database version number), and an AFTER UPDATE trigger that also updates the binary column to the new @@DBTS will give you a psuedo-rowversion type that you can then update manually. It'd be no faster or better than just updating some other column to it's same value though.

SQL Server : on update set current timestamp

And if you really need a timestamp - then make a trigger on insert and update that updates the column with the current timestmap.

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE dbo.YourTable
  SET last_changed = GETDATE()
  FROM Inserted i

To update a single row (which has been edited or inserted) you should use

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE f set LastUpdate=GETDATE() 
  FROM 
  dbo.[YourTable] AS f 
  INNER JOIN inserted 
  AS i 
  ON f.rowID = i.rowID;

These should be all you need. GETUTCDATE() if you want it in UTC (which I prefer)

SQL Server absolutely knows the rows it processes

update myTable set last_time =CURRENT_TIMESTAMP ; worked, but it
updated all the rows instead of the current.

Yeah, guess what - because that is exactly what you tell SQL Server: Update all rows in the table.

Doesn't Sql Server know which is the actual record it is processing?

Sets have no current row ;) That is where the problem starts.

The only way to do that exactly as you want is up in my answer on the beginning: a timestamp. Due to the misconceptions, though, I add an advice: get a book about SQL basics.

How can I update the time in a column without affecting the date?

We can try truncating all timestamps to midnight, then adding 9 hours:

UPDATE note
SET entered = DATE_TRUNC('day', entered) + interval '9' hour;

---

Related Topics