CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Iternity
Iternity

Reputation: 892

change timestamp datatype to rowversion

I'm not sure if it is necessary. I have a sql server 2008 database where all the tables have a timestamp column. is it best to change the timestamp datatype to rowversion so that it can be easily upgradeable to future sql server versions? if so,what is the best way to change all timestamp columns to rowversion without recreating the tables?

Upvotes: 3

Views: 2957

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

Feel free to change your scripts to show ROWVERSION instead of TIMESTAMP, but there is no point in trying to make any changes to the table. Even in SQL Server 2012, while they have vowed long ago to eliminate the confusing name, even if you create a table like this:

USE tempdb;
GO
CREATE TABLE dbo.foo
(
  bar INT,
  rv ROWVERSION
);

The catalog views and everything else internal still sees that as TIMESTAMP:

SELECT c.name, [type] = TYPE_NAME(c.system_type_id)
  FROM sys.columns AS c
  WHERE [object_id] = OBJECT_ID('dbo.foo');

Results:

name type
---- ---------
foo  int
rv   timestamp

So I don't really think there is anything you (or anyone) can do to better prepare yourself for future versions - Microsoft is going to have a LOT of customers in the same boat as you, which is probably why you haven't actually seen a rapid expulsion of the type. They are probably still coming up with the plan to really eliminate it (and they are getting much better about choosing names, with the exception of DATETIME2 IMHO).

My best advice is just to ensure that your scripts and objects all use ROWVERSION so that you aren't caught by surprise. This may mean manually editing scripts that you generate from Management Studio, the catalog views or even 3rd party tools.

Upvotes: 3

Related Questions