Timestamp column - Can it be queried?

Question

Can a Timestamp (rowversion) column in a SQL Server table be queried? If so, how?

I'd like to do something like this:

Select * From MyTable Where MyTimestampColumn = "???"

But am not sure what to put in for "???"

Thanks - Randy

Answer 1

A quick test says that yes it can. e.g this gets the row I most recently modified.

SELECT * 
  FROM table
where [ts] = (select max([ts]) from table)

Answer 2

Yes. It's effectively binary(8).

Why use it otherwise? It's only use is to detect other changes to a row on UPDATE. Otherwise, you have to test all columns in the row for changes. MS Access especially relies on it for concurrency

Answer 3

A SQL Server timestamp column is for row versioning, and doesn't relate to time (http://msdn.microsoft.com/en-us/library/ms182776.aspx)

However, it is often queried (either explicitly or implicitly) in concurrency checking code for multi user applications.

Answer 4

The timestamp datatype is equivalent to binary(8). You can query against it:

create table #t (somecolumn varchar(64), mytimestampcolumn timestamp)

insert into #t (somecolumn) select 'hello world'
insert into #t (somecolumn) select 'foo'
select * from #t where mytimestampcolumn = 0x0000000000000978 -- this value will vary 

drop table #t

Note that timestamp has been deprecated in Sql Server 2008. In previous versions of SQL Server (2005, 2000), rowversion is a synonym for timestamp to help with future migration.

Answer 5

I guess is not correct, that what are you trying to do. SQL Server Books Online: "The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified"