Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 336
gotqn Avatar asked Feb 21 '13 08:02

gotqn


2 Answers

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.

like image 53
Damien_The_Unbeliever Avatar answered Sep 27 '22 18:09

Damien_The_Unbeliever


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.

like image 25
Mitch Wheat Avatar answered Sep 27 '22 18:09

Mitch Wheat