INF: MIN and MAX Should Not Be Used on Timestamp Column

ID: Q64559

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2

MIN and MAX should not be used on timestamp columns to number rows because MIN and MAX incorrectly assume the rightmost byte is the most significant byte. Timestamps are actually incremented by assuming that the rightmost byte is the least significant byte.

After 255 updates, MIN and MAX assume the value has changed from the most positive value to the most negative value when the value really only increased by one. If a technique such as the following is used to number rows, all the rows with timestamps greater than the old timestamp of the row that crossed the 255 boundary will be skipped:


   select @current="",@n=0
   select @cnt=count(*) from t1
   while @n<@cnt begin

      select @n=@n+1
      select @current=min(timestamp) from t1 where key>@current
      update t1 set rowcnt=@n where key=@current

Additional query words: Windows NT


Keywords          : kbusage SSrvTran_SQL SSrvServer SSrvWinNT 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 9, 1999