This blog is the result of a recent discussion I had with my fellow colleagues.
One of my colleagues asked me why rowversion is not recognized as a valid datatype with SQLServer. I was surprised at this statement and told that I don't believe that is correct. He replied that he can illustrate it and showed the below on his system.
He launched an instance of SQLServer Management Studio (SSMS) which belonged to SQL 2014 Express Edition and tried creating a table using the table designer. The moment he tried to add a column of rowversion datatype and save it he got the below error message
I checked the datatype listing within the designer and found that its not having rowversion included as a type within which was a bit surprising!
This was the reason why it errored out.So the only solution seems to be to select timestamp as the datatype. But the irony is that timestamp is deprecated from SQL 2008 onwards. I've check this in 2012 and 2008 R2 versions of SSMS and there also the behavior is the same.
I told him that this is a limitation of the designer and you can very well create rowversion field if you use T-SQL CREATE TABLE statement. He tried it out and confirmed that it indeed worked. But here also if you try to script out the table from SSMS it will still show column as of timestamp type only as it seems systypes still does not have the change implemented as per the connect item below
Apparently rowversion and timestamp are synonyms of one and the same thing. However ANSI recommends using rowversion and timestamp has been deprecated for use in all fresh development activities.
I've never been a fan of the table designer in SSMS. I prefer using T-SQL scripts for all DDL activities. This just adds one more reason for my preference.
Hope this blog will give a word of caution to anyone trying to create tables using the designer and want to declare a rowversion type field. I would suggest strongly considering T-SQL scripts for activities like table creation,constraint addition etc.
No comments:
Post a Comment