patternsqlMinor
Any reason not to add 'rowversion' column?
Viewed 0 times
rowversioncolumnanyreasonnotadd
Problem
The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access. From the Supporting Concurrency Checks section:
Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.
The nice thing is that every UPDATE/DELETE statement and bound form will benefit from this addition without having to make any changes within MS Access (aside from re-linking the tables).
I was unaware of this feature until recently. I am considering adding a named rowversion column to every table in SQL Server that I link to from MS Access. Are there any downsides I should be aware of before I do this?
Obviously there will be storage requirements and performance impacts, but I assume these will be negligible. Also, several of these tables are articles in a merge replication scenario; does that make a difference?
Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.
The nice thing is that every UPDATE/DELETE statement and bound form will benefit from this addition without having to make any changes within MS Access (aside from re-linking the tables).
I was unaware of this feature until recently. I am considering adding a named rowversion column to every table in SQL Server that I link to from MS Access. Are there any downsides I should be aware of before I do this?
Obviously there will be storage requirements and performance impacts, but I assume these will be negligible. Also, several of these tables are articles in a merge replication scenario; does that make a difference?
Solution
Other than the minor things you've already picked up on (additional storage requirements, and potential performance differences as a result of requiring more I/O for the same number of rows), no, I can't think of any real gotchas with adding a
One potential issue, though, with merge replication specifically, is if Access grabs a row from one of the servers, then tries to grab a copy of the same row from a different subscriber (not sure if that is possible in your architecture). The reason is that the
You might also want to review the merge replication section in this document about schema changes in general.
ROWVERSION column to these tables. One potential issue, though, with merge replication specifically, is if Access grabs a row from one of the servers, then tries to grab a copy of the same row from a different subscriber (not sure if that is possible in your architecture). The reason is that the
ROWVERSION values are re-generated at each subscriber, so they won't match the source - this may make it look like the row has changed, when it hasn't. Maybe not possible, or maybe it's ok that such a row always be considered "dirty," but something to keep in mind (and probably test thoroughly).You might also want to review the merge replication section in this document about schema changes in general.
Context
StackExchange Database Administrators Q#105596, answer score: 6
Revisions (0)
No revisions yet.