August 21, 2015 at 8:48 am
I'm working on archiving data from some tables. I've duplicated the data structure, with the exception of not including the IDENTITY specifier on INT columns, so that the archive table will keep the value that was generated in the original table. This is all going well, until I tried to copy the data over where the column is specified as a timestamp data type. I've looked this up and found a couple of things. First, documentation for SQL 2000 says,
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
And then documentation for the soon to be released SQL 2016 on the rowversion data type says,
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.
and
Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.
OK, I've read the descriptions, but I'm sorry, I don't get it. Why have a timestamp/rowversion data type? What does it get you?
Kindest Regards, Rod Connect with me on LinkedIn.
August 21, 2015 at 12:18 pm
What it does is acts as a marker for if a given row has been updated. So you can read the data, get the current rowversion, then when you go to update, make sure that your rowversion matches what's there. It prevents "last in wins" type of data problems where you can overwrite other people's work without knowing it. I don't know very many people who use it though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2015 at 8:53 am
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.
Kindest Regards, Rod Connect with me on LinkedIn.
August 25, 2015 at 1:04 pm
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.
If push came to shove, and you had to use the archived data as the source to restore some number of records, you'd be without that timestamp/rowversion value as it was at the time the data was archived, which might lead to some kind of inconsistency. Chances are, the average user isn't going to know beans about that field, and perhaps at best, the BA will know what it's there for, but if you're going to archive, it just seems out of place to not preserve the row as is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2015 at 4:57 pm
sgmunson (8/25/2015)
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.If push came to shove, and you had to use the archived data as the source to restore some number of records, you'd be without that timestamp/rowversion value as it was at the time the data was archived, which might lead to some kind of inconsistency. Chances are, the average user isn't going to know beans about that field, and perhaps at best, the BA will know what it's there for, but if you're going to archive, it just seems out of place to not preserve the row as is.
Where would you put it? You cannot insert into a TIMESTAMP/ROWVERSION column. You could convert it to a VARBINARY or BINARY for the ARCHIVE table but you won't be able to "restore" it to the original table.
CREATE TABLE dbo.TimeStampDemo
(SomeInt INT,SomeTimeStamp TIMESTAMP)
;
GO
--===== This will work just fine
INSERT INTO dbo.TimeStampDemo
(SomeInt)
SELECT 1
;
GO
--===== This will produce an error.
-- You cannot insert into a TIMESTAMP or ROWVERSION column.
INSERT INTO dbo.TimeStampDemo
(SomeInt,SomeTimeStamp)
SELECT 2, @@DBTS
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2015 at 5:32 pm
Jeff Moden (8/25/2015)
sgmunson (8/25/2015)
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.If push came to shove, and you had to use the archived data as the source to restore some number of records, you'd be without that timestamp/rowversion value as it was at the time the data was archived, which might lead to some kind of inconsistency. Chances are, the average user isn't going to know beans about that field, and perhaps at best, the BA will know what it's there for, but if you're going to archive, it just seems out of place to not preserve the row as is.
Where would you put it? You cannot insert into a TIMESTAMP/ROWVERSION column. You could convert it to a VARBINARY or BINARY for the ARCHIVE table but you won't be able to "restore" it to the original table.
CREATE TABLE dbo.TimeStampDemo
(SomeInt INT,SomeTimeStamp TIMESTAMP)
;
GO
--===== This will work just fine
INSERT INTO dbo.TimeStampDemo
(SomeInt)
SELECT 1
;
GO
--===== This will produce an error.
-- You cannot insert into a TIMESTAMP or ROWVERSION column.
INSERT INTO dbo.TimeStampDemo
(SomeInt,SomeTimeStamp)
SELECT 2, @@DBTS
;
I learn something new every day. I didn't know you couldn't insert that data type -- I've yet to actually encounter someone using it. How about UPDATE ? As in, could one convert from the existing value to varbinary and then somehow convert back? Time for me to do some reading, it seems...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2015 at 7:52 pm
sgmunson (8/25/2015)
Jeff Moden (8/25/2015)
sgmunson (8/25/2015)
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.If push came to shove, and you had to use the archived data as the source to restore some number of records, you'd be without that timestamp/rowversion value as it was at the time the data was archived, which might lead to some kind of inconsistency. Chances are, the average user isn't going to know beans about that field, and perhaps at best, the BA will know what it's there for, but if you're going to archive, it just seems out of place to not preserve the row as is.
Where would you put it? You cannot insert into a TIMESTAMP/ROWVERSION column. You could convert it to a VARBINARY or BINARY for the ARCHIVE table but you won't be able to "restore" it to the original table.
CREATE TABLE dbo.TimeStampDemo
(SomeInt INT,SomeTimeStamp TIMESTAMP)
;
GO
--===== This will work just fine
INSERT INTO dbo.TimeStampDemo
(SomeInt)
SELECT 1
;
GO
--===== This will produce an error.
-- You cannot insert into a TIMESTAMP or ROWVERSION column.
INSERT INTO dbo.TimeStampDemo
(SomeInt,SomeTimeStamp)
SELECT 2, @@DBTS
;
I learn something new every day. I didn't know you couldn't insert that data type -- I've yet to actually encounter someone using it. How about UPDATE ? As in, could one convert from the existing value to varbinary and then somehow convert back? Time for me to do some reading, it seems...
Nope. No UPDATE either. It updates when the row updates. You only reference it for reads and comparisons. It really is just for solving the last in wins problem. It tells you if the row has been modified. Heck, it's not even unique in any way. You can have the same data across the database, even within the same table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 25, 2015 at 8:40 pm
Grant Fritchey (8/25/2015)
sgmunson (8/25/2015)
Jeff Moden (8/25/2015)
sgmunson (8/25/2015)
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.If push came to shove, and you had to use the archived data as the source to restore some number of records, you'd be without that timestamp/rowversion value as it was at the time the data was archived, which might lead to some kind of inconsistency. Chances are, the average user isn't going to know beans about that field, and perhaps at best, the BA will know what it's there for, but if you're going to archive, it just seems out of place to not preserve the row as is.
Where would you put it? You cannot insert into a TIMESTAMP/ROWVERSION column. You could convert it to a VARBINARY or BINARY for the ARCHIVE table but you won't be able to "restore" it to the original table.
CREATE TABLE dbo.TimeStampDemo
(SomeInt INT,SomeTimeStamp TIMESTAMP)
;
GO
--===== This will work just fine
INSERT INTO dbo.TimeStampDemo
(SomeInt)
SELECT 1
;
GO
--===== This will produce an error.
-- You cannot insert into a TIMESTAMP or ROWVERSION column.
INSERT INTO dbo.TimeStampDemo
(SomeInt,SomeTimeStamp)
SELECT 2, @@DBTS
;
I learn something new every day. I didn't know you couldn't insert that data type -- I've yet to actually encounter someone using it. How about UPDATE ? As in, could one convert from the existing value to varbinary and then somehow convert back? Time for me to do some reading, it seems...
Nope. No UPDATE either. It updates when the row updates. You only reference it for reads and comparisons. It really is just for solving the last in wins problem. It tells you if the row has been modified. Heck, it's not even unique in any way. You can have the same data across the database, even within the same table.
Then I guess the only full preservation method is backup and restore... or ... doing a full table SELECT * INTO new.TABLE_NAME FROM old.TABLE_NAME on archive, and then deleting the original table and reversing the table names to do the restoration.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 26, 2015 at 8:25 am
Jeff Moden (8/25/2015)
sgmunson (8/25/2015)
Rod at work (8/24/2015)
Hmmm. Thank you Grant, I really appreciate the straight forward explanation for what the rowversion/timestamp is all about. I'm working on archiving the data, but leaving it in the database so users can access it. Given your explanation I really don't see what trying to save the timestamp/rowversion values gains us. I'll talk to the users and see what they've got to say.If push came to shove, and you had to use the archived data as the source to restore some number of records, you'd be without that timestamp/rowversion value as it was at the time the data was archived, which might lead to some kind of inconsistency. Chances are, the average user isn't going to know beans about that field, and perhaps at best, the BA will know what it's there for, but if you're going to archive, it just seems out of place to not preserve the row as is.
Where would you put it? You cannot insert into a TIMESTAMP/ROWVERSION column. You could convert it to a VARBINARY or BINARY for the ARCHIVE table but you won't be able to "restore" it to the original table.
CREATE TABLE dbo.TimeStampDemo
(SomeInt INT,SomeTimeStamp TIMESTAMP)
;
GO
--===== This will work just fine
INSERT INTO dbo.TimeStampDemo
(SomeInt)
SELECT 1
;
GO
--===== This will produce an error.
-- You cannot insert into a TIMESTAMP or ROWVERSION column.
INSERT INTO dbo.TimeStampDemo
(SomeInt,SomeTimeStamp)
SELECT 2, @@DBTS
;
I do intend to archive the rowversion value as a VARBINARY(8). BTW, I talked to the BA about the field; he isn't aware of what meaning it had in the past. As it turns out, both he and I are relatively new to the project. The people who designed and wrote it originally, and who would know best why a rowversion column was added to 3 tables, are long since gone. But at least I'll capture the value of the rowversion, just in case there's something in the application that depends upon it.
Kindest Regards, Rod Connect with me on LinkedIn.
August 26, 2015 at 10:49 am
Rod, if your database's front end is Microsoft Access, or if people are using Access against it, you might want to keep it. Access likes rowversion to see if the contents of a row have changed, I think it might have been required if the table contains bit fields.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 27, 2015 at 8:31 am
Wayne West (8/26/2015)
Rod, if your database's front end is Microsoft Access, or if people are using Access against it, you might want to keep it. Access likes rowversion to see if the contents of a row have changed, I think it might have been required if the table contains bit fields.
Ah, so it might be MS Access that put those rowversion columns in there! (It used timestamp, but as I understand it timestamp and rowversion are the same thing.) At this point in time Access is the front-end to the database. I've just checked and all three tables that have rowversion columns, also have at least one column that's a bit field. What I did, in the archive table, is kept the name of the rowversion column but changed its data type to VARBINARY(8). (The original column was defined as a nullable timestamp column, which I understand a VARBINARY(8) is semantically equivalent to.) So hopefully MS Access can still work with it, when the Access developer writes new forms to display (in read-only mode) the value in the archive tables.
Thank you, Wayne!
Kindest Regards, Rod Connect with me on LinkedIn.
August 27, 2015 at 10:41 am
Happy to help, Rod! Either your developer knew to apply them or the upsizing wizard might have added them. You might want to keep the new fields rowversion rather than varbinary as the first has SQL Server managing the value, the second has the user program managing the value.
I ran in to it while debugging an error after I added a bit field and Access became unhappy, so I have recent experience with rowversion.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply