Alter column of type timestamp

Question

I want to update an empty table , which has a column of type timestamp to varbinary(8)

I used the following command

ALTER TABLE Notification  ALTER COLUMN RowRevisionID varbinary(8) 

and I get and an error

Cannot alter column 'RowRevisionID' because it is 'timestamp'.

How can I change a timestamp column type?

I do not wish to drop the column an add a new one , because that will create a column at the end , and I wish to preserve column order to use this table in an INSERT INTO

Answer 1

You unfortunately cannot make a change to a timestamp column, as the error implies; you are stuck with what you have. Also, each table can only have one timestamp column, so you cannot duplicate the column in any solution.

Your best bet (depending on the size of the table) might be to copy the data into a staging table (using SELECT * INTO MyTempTable FROM OriginalTable syntax to preserve the timestamp values), then drop and recreate the original table with the desired columns in the desired order and reinsert the data, or you could add a new VARBINARY(8) column to the existing table and drop the timestamp column, preserving the original table. There are probably other solutions along the same lines as these, but all will require a new column, rather than an ALTER COLUMN script.