Thursday, May 14, 2009

Last Update For a Set Of Rows In SQL Server

I am displaying a gridview (table) of rows from the SQL Server and want to refresh the page when anything in any of the rows changes. One thing I always do when I create a new table is add a non-null timestamp column to the table called LastUpdate. I can use this column to figure out if any of the rows has changed with this Transact-SQL: SELECT MAX(LastUpdate) FROM T1 Since the timestamp column automatically gets larger (across the whole database) when there is an UPDATE or INSERT the MAX(LastUpdate) will always be bigger if there is a row added or updated on T1. But what about Deletes? Imagine we have this table:
Id                                     LastUpdate
------------------------------------   ------------------
FEB5F896-4F2E-47C9-AA63-0001C63CD3DC   0x00000000018F4590
6692BBC4-E3C7-4B9A-B8DC-00035CAD4D00   0x0000000001913542
018483F7-7EA1-4F0D-8CD2-0008CF4851F9   0x000000000193D0E0
45CC45AD-A6EB-4E54-B6B5-000AE2DF2E9E   0x00000000018F9161
B770834E-331E-416F-BBA0-000AE722C6D2   0x00000000018FE96D
SELECT MAX(LastUpdate) FROM T1 Would Give Us: 0x000000000193D0E0 If We executed DELETE T1 WHERE Id = '45CC45AD-A6EB-4E54-B6B5-000AE2DF2E9E' SELECT MAX(LastUpdate) FROM T1 Would still give us: 0x000000000193D0E0, even though the table has changed. The solution is to add the COUNT(1) of the query to the SELECT. Like This: SELECT CONVERT(varchar(max),CONVERT(bigint,MAX(LastUpdate))) + '-' + CONVERT(varchar(max),COUNT(1)) FROM T1 Notice that I have to convert LastUpdate to a bigint, even though LastUpdate displays nicely in the SQL Server Management Studio, it really is a Byte array and concatenating to a Byte array is rather hard. Then I convert to a varchar(max) so that I can add the count on the end. I stored the string in javascript on my web page and make an Ajax call back to the web server every so often to see if the string has changed. It it does I know that my rows have changed and can refresh the page. {6230289B-5BEE-409e-932A-2F01FA407A92}

No comments:

Post a Comment