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?

回答1:

I don't think you can. And a table can only have one timestamp column.

You also cannot update a timestamp column, so the old "copy/drop/update" trick won't work.

You are likely stuck with what you have.

create table #tmp (id int, timestamp null)
insert into #tmp (id) values (1)
select * from #tmp


create table #tmp2 (id int)
insert into #tmp2 (id) values (1)
alter table #tmp2 add timestamp null
select * from #tmp2

I'm testing NULL TIMESTAMP columns, and it wont actually go NULL -- I can't find any documents stating it, but I don't think a NULL TIMESTAMP is possible (even if declared null, its never null in the data).

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL)...

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.110).aspx

Also...

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...

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx

回答2:

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.

回答3:

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

回答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