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

Last reviewed: April 25, 1997
Article ID: Q64559

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/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 SSrvServer SSrvTran_SQL SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 Windows
Issue type : kbtshoot

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.