T-SQL "timestamp" overwrites "rowversion" data type column

Question

I am using Miscrosoft SQL Server 2012 and because in this article is said:

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

I have decided to use "rowversion" column instead "timestamp" column. So, I have created the following table:

CREATE TABLE [dbo].[TestTable]
(
     [Col001] [INT] NOT NULL
    ,[Col002] [CHAR](2) NOT NULL
     ...
    ,[Col00N]  [DATETIME2] NOT NULL
    ,[RowVersionID] [ROWVERSION] NOT NULL
    ,CONSTRAINT [PK_ProtoSurveyGlobalizationChanges_RowVersionID] PRIMARY KEY CLUSTERED 
     (
        [RowVersionID] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY]

And everything was looking all right, until I have pressed "ALT+F1" combination that gives me information about my table (like columns details) and I have seen that the "RowVersionID" column is mark with type "timestamp".

The next think that I have done, was to "drop and create my table" using SQL Server Management Studio 2012 and the "generated SQL" changes my original query like this:

[RowVersionID] [timestamp] NOT NULL

Why the server is not using the type that I have set initially and why in the article I am advised not to use "timestamp" but somehow the server is choosing it?

rowversion is a timestamp. It's been renamed because timestamp didn't conform to the ISO standard. Leave it as rowversion and stop worrying about it. — Mitch Wheat, Feb 21 '13 at 08:26
So, there is not difference after all, if I am specifying "rowversion" or "timestamp"? The server is using the correct one for me? — gotqn, Feb 21 '13 at 08:27
Only one of conforming. timnestamp is deprecated and will probably be removed at a later date. — Mitch Wheat, Feb 21 '13 at 08:27
This is clearly a bug in Smo. It should not generate deprecated data types. Please file a bug using Connect. The Principal Group Program Manager for SQL Server Developer Tooling used to be Gert Drapers, although LinkedIn says he now works on Xbox Live. — John Zabroski, Mar 12 '14 at 14:38
@JohnZabroski Using `Connect`? I am not going to waste time with it :-) You are free to fill the bug reprot by yourself and let us know when/if it is fixed. — gotqn, Mar 13 '14 at 07:17
@gotqn I anticipated you saying no, but didn't want to do it until you turned down my very generous offer ;-P — John Zabroski, Mar 14 '14 at 15:03

score 10 · Accepted Answer · answered Feb 21 '13 at 08:27

10

They're synonyms, with no way for the server to tell (once the table is created) which word you actually used to create the table.

Obviously, somewhere deep in the depths, the news that timestamp is deprecated hasn't reached whoever is responsible for writing the scripting component of SQL Server - so the scripts still say timestamp.

answered Feb 21 '13 at 08:27

Damien_The_Unbeliever

220,246
21
302
402

3

+1 esp. for "somewhere deep in the depths, the news that ..." :) – Mitch Wheat Feb 21 '13 at 08:29

Mitch Wheat · Answer 2 · 2013-02-21T23:58:37.110

6

rowversion is a timestamp. They are the same thing.

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.

Ref.

It's been renamed because timestamp didn't conform to the ISO standard. And because there is less chance of people mistakenly thinking that timestamp can represent an actual datetime point in time.

Simply leave it declared as rowversion. timestamp is deprecated and will probably be removed at a later date.

edited Feb 21 '13 at 23:58

answered Feb 21 '13 at 08:28

Mitch Wheat

280,588
41
444
526

1

Yes, I am aware of that, but my concern is that I am using "rowversion" but it seems that the SQL Server does not understand or indicated that, so what is going to happened when "timestamp" is removed. Something more, the article advised even to rewrite the statements that are using "timestamp". – gotqn Feb 21 '13 at 08:32

T-SQL "timestamp" overwrites "rowversion" data type column

2 Answers2