How to update sql server timestamp column without changing record data

Believe it or not, this updates a RowVersion/Timestamp column without setting a column. Notice @foo=@foo is the only clause after set. I'm using Sql Server 2017.

declare @foo nvarchar(50);

update dbo.MyTable
set @foo = @foo
where Id = @id

Ronnie,

First of all the timestamp syntax is being deprecated by Microsoft, so you need to use rowversion, second the rowversion is always related to the row.

From the documentation:

rowversion

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion.

However, because the way it's implemented in SQL server, you need to use triggers to achieve what you want.

Something like this:

CREATE TRIGGER tgUpdateParentRowVersion ON ChildTable FOR INSERT, DELETE, UPDATE
AS
BEGIN
   /*
    * The updates below force the update of the parent table rowversion
    */
   UPDATE ParentTable
      SET ChildUpdate = ChildUpdate + 1
     FROM ParentTable a 
     JOIN inserted    i on a.pkParentTable = i.fkParentTable

   UPDATE ParentTable
      SET ChildUpdate = ChildUpdate - 1
     FROM ParentTable a 
     JOIN deleted     d on a.pkParentTable = d.fkParentTable
END

Have you looked into using triggers? It seems a more natural fit for what you're after.

I worked on an application that had timestamps across the board once upon a time; exactly what we were supposed to do with them proved to be kind of elusive so we audited them out of the schema. You could read timestamp in to objects you create based off of the data and check to see if it's changed when you go to update and rollback if you see that the data's dirty, but that's a lot of boilerplate for not a lot of gain (at least in the domain I was working in).

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.