'Update a TIMESTAMP column to be nullable

I have a table that exists in two databases.

In one database there is a table with a column called ROW_VERSION that is of type TIMESTAMP NOT NULL.

In the second database the same table has the same column of type TIMESTAMP but it is of type TIMESTAMP NULL.

I would like to change the column in the first database to be nullable. This will allow me to synchronize between the two databases easier.

But when I run this:

ALTER TABLE [MyTable]
ALTER COLUMN ROW_VERSION TIMESTAMP NULL

I get the error:

Cannot alter column 'ROW_VERSION' to be data type timestamp.

It is already a timestamp. I just need to make it nullable. Is there anyway to do this?

sql-servertsqlsql-server-2012


Solution 1:[1]

You can't alter a TIMESTAMP column (http://msdn.microsoft.com/en-us/library/ms190273.aspx) You'll have to do the roundabout way of renaming the old table, creating a new table with the desired schema, inserting the data from the renamed table, then dropping that old table. SSMS will probably script this for you if you change the column in the GUI.

ALTER COLUMN
Specifies that the named column is to be changed or altered.

The modified column cannot be any one of the following:

•A column with a timestamp data type.

Solution 2:[2]

Yeah, you can't do this. It almost sounds like you'd want to use a date or datetime datatype for that column if you want it to be nullable. I think of TIMESTAMP being analogous to the IDENTITY datatype in that they are both self-filling, auto-incrememting columns which don't really make sense to be null. Also note that if you try the following:

if object_id('tempdb..#timestamptable') is not null 
    drop table #timestamptable
create table #timestamptable (id int, ts timestamp null)
insert into #timestamptable (id, ts)
values(1, null),
(2, null)
select *
from #timestamptable

Your timestamp column will still have data:

    id      ts
    1       0x000000004C8BED2B
    2       0x000000004C8BED2C

Solution 3:[3]

I'm running Postgres SQL and the following works for me

`ALTER TABLE MYTABLE COLUMN MYROW DROP NOT NULL`

Solution 4:[4]

FORCE THE TYPE ALTER TABLE [MyTable] ALTER COLUMN ROW_VERSION TIMESTAMP

remove indexes if exists

ALTER TABLE SOMETABLE DROP CONSTRAINT DF__SOME__index

maybe add with DEFAULT VALUE

ALTER TABLE [MyTable] ADD DEFAULT 0 FOR ROW_VERSION

I will just drop the table and recreate if allow it

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mark Sowul
Solution 2 How 'bout a Fresca
Solution 3 Jonathan Chow
Solution 4 Valentin Petkov

Related Questions