Tuesday, January 22, 2013

SQL Server Compact Code Snippet of the Week #3 : locate the rowversion column in a table

The third data type that is only allowed to appear in a single column per table is the rowversion data type, also known as timestamp (no relation to datetime). Rowversion is a data type that exposes automatically generated, unique binary numbers in a database, and contains unique values across the entire database. The size is 8 bytes. You can use the @@DBTS function to get the most recent rowversion value in the database.

Here is the code to determine if a table has a rowversion column, and get the column ordinal (position in the table):

 public Int32 GetRowVersionOrdinal(string tableName)
object value = ExecuteScalar("SELECT ordinal_position FROM information_schema.columns WHERE TABLE_NAME = '" + tableName + "' AND data_type = 'rowversion'");
if (value != null)
return (int)value - 1;
return -1;

No comments: