1

I am using SQL Server 2012 and I want to create a "changes" table - it will be populated with data from other table when the second table columns values are changed.

I am adding to the "changes" table "datatime2", and "rowversion" columns in order to track when the changes are made.

Is it ok to use "rowversion" as primary key?

I have read here that it will be changed, if the current row is updated and that's why it is not a good candidate for "primary key" making foreign keys invalid.

Anyway, if it won't be used as a foreign key and the rows of "changes" table will never be updated (only new rows will be inserted) is it ok to use the "rowversion" as PK or I should use additional column?

Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • please could you specify what DB you are using. Could you also please explain the context of your logic above... What is this DB being used for. i.e. if this is for a data warehouse, then this is most definately the wrong way to go about things. – SQLGuru Feb 11 '13 at 08:42
  • The idea is when some date in a table is changed because of DML operations the old data will be inserted in my "Changes" table, so if I need to see how or when the change in the primary table is made. – gotqn Feb 11 '13 at 08:47
  • you are either going to have to implement some sort of Slowly Changing Dimension, or alternatively use Triggers. I prefer the first option of SCD's but this is an involved process to implement. Over and above this, why would you want to use the row version and not the physical primary key? – SQLGuru Feb 11 '13 at 08:55
  • Yes, i will use trigger. The similar tables in the system are created in such a way and I am following it. Anyway, I have read that this is not such a good idea, so I decided to check if it will be so big problem. And if it is, I will finish creating a physical one. – gotqn Feb 11 '13 at 09:13
  • What possible reason do you have for using Rowversion in this case if the table will never be updated? You'll have a table with a date and the number '1' as its' key. Whats the point? Are you trying to track multiple changes to a table over time or just trying to capture the latest change? Regardless I suggest you simply use an Identity as a PK. – Nick.Mc Feb 11 '13 at 10:28
  • 1
    @ElectricLlama In the "changes" table I will have all columns from the "primary" table + date and rowversion columns. The "changes" table will information of how the records in the "primary" table are changing over time using "after insert/delete/update" trigger. – gotqn Feb 11 '13 at 10:49
  • The rowversion adds no information. It remains at '1' because your changes table is never updated. Only inserted into. – Nick.Mc Feb 11 '13 at 11:13
  • @ElectricLlama The similar tables in the system are created with "timestamp" column, but it is said to use "rowversion" instaed, as "timestamp" is depreciated. Could you advice? http://msdn.microsoft.com/en-us/library/ms182776(v=sql.110).aspx – gotqn Feb 11 '13 at 12:08
  • @ElectricLlama I think you are wrong - it is not "1". It's like "0x0000000000445529" and unique for each row. – gotqn Feb 11 '13 at 12:09
  • My apologies, I shouldn't have commented without knowing about it! Regardless, what's the point of having a composite PK composed of a unique code (rowversion) and a mostly unique code (date)? Also what is the point of having a very large (rowversion or timestamp) column which keeps track of changes to a row when the design implies the row will never change? It doesn't make sense at all. If you have existing tables which use this then there must be a reason for it. It certainly doesn't make sense at this stage. – Nick.Mc Feb 11 '13 at 22:13
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24356/discussion-between-gotqn-and-electricllama) – gotqn Feb 12 '13 at 07:02

1 Answers1

0

Some good info here:

Careful reading of the MSDN page also shows that duplicate rowversion values are possible if SELECT INTO statements are used improperly. Something to watch out for there.

I would stick with an Identity field in the original data, carried over into the change tracking table that has its own Identity field.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28