SSMA timestamp. What's it for, how is it used?

Question

I rencently used the SQL Server Migration Assistant to import a database into SQL Server 2005. I noticed that a number of tables that were imported have been ammended with a new column called SSMA_timestamp.

Can anyone tell me what this is for and how it would be used?

Answer 1

The added SSMA_timestamp columns are not only used during migration. They actually help avoid errors when Access updates records in tables linked to SQL Server. So if you are still using an Access front end linked to the migrated SQL Server database, it would be best to not drop the SSMA_timestamp columns.

From the MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server:

Supporting Concurrency Checks

Probably the leading cause of updatability problems in Office Access–linked tables is that Office Access is unable to verify whether data on the server matches what was last retrieved by the dynaset being updated. If Office Access cannot perform this verification, it assumes that the server row has been modified or deleted by another user and it aborts the update.

There are several types of data that Office Access is unable to check reliably for matching values. These include large object types, such as text, ntext, image, and the varchar(max), nvarchar(max), and varbinary(max) types introduced in SQL Server 2005. In addition, floating-point numeric types, such as real and float, are subject to rounding issues that can make comparisons imprecise, resulting in cancelled updates when the values haven't really changed. Office Access also has trouble updating tables containing bit columns that do not have a default value and that contain null values.

A quick and easy way to remedy these problems is to add a timestamp column to the table on SQL Server. The data in a timestamp column is completely unrelated to the date or time. Instead, it is a binary value that is guaranteed to be unique across the database and to increase automatically every time a new value is assigned to any column in the table. The ANSI standard term for this type of column is rowversion. This term is supported in SQL Server.

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 SQL Server Migration Assistant for Office Access automatically adds a column named SSMA_TimeStamp to any tables containing data types that could affect updatability.