-1

I want to add a rowversion column to an existing table in my database so I can essentially order by the last time each record was modified successfully. My question is, how will adding the rowversion affect my existing data? Will the rowversion values be assigned like random on these records or by the last instance they were modified too? (Even though they existed before the column)

user1501171
  • 210
  • 1
  • 3
  • 17
  • 2
    Rows in a table are, by definition, unordered. But why do you need to ask? Just create a small test table and do this yourself. And are you expecting the assigned values to somehow match your idea of ? – SMor Mar 02 '20 at 23:12
  • I'm just wondering if there's metadata under the hood to represent the last modified time of each row that SQL Server would map to the rowversion column? Or is it possible to seed the rowversion column to assign historic values based on another column asc/desc? – user1501171 Mar 02 '20 at 23:17
  • One other challenge here - there's no way to add a nullable rowversion column. I tried and even though the syntax suggests that you can, it still does a size-of-data operation on the table. But if you want a `date_modified` or some column like that, add it yourself as one of the various datetime data types and put a trigger on the table to keep it correct. – Ben Thul Mar 02 '20 at 23:20
  • Short answer - no. The engine will assign values - you cannot control that assignment. – SMor Mar 02 '20 at 23:30
  • And the assignment will essentially be random for existing data? – user1501171 Mar 02 '20 at 23:32

1 Answers1

0

My question is, how will adding the rowversion affect my existing data?

You can test it.

eg

drop table if exists t
go
create table t(id int primary key)
insert into t(id) values (1),(2),(3)
go
alter table t add rv rowversion

go
insert into t(id) values (4)

insert into t(id) values (5),(6)

go
select * from t

outputs

(3 rows affected)

(1 row affected)

(2 rows affected)
id          rv
----------- ------------------
1           0x00000000000007DD
2           0x00000000000007DE
3           0x00000000000007DF
4           0x00000000000007E0
5           0x00000000000007E1
6           0x00000000000007E2

(6 rows affected)
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67