0

I am looking for something similar to rowversion but for the whole table : a unique number automatically generated that can be used to know if table has changed.

Eg : 

SELECT TABLE_VERSION('FOO')
0x00000000000007D2

INSERT INTO FOO (...) VALUES (1, 'Hello world')

SELECT TABLE_VERSION('FOO')
0x00000000000007D5

Does it exists in SQL Server ?

If not, is there any alternative ? Somethings that allow to track changes on a column would be great too. I have been thinking of using HASHBYTES, but unfortunately it only gives hash for a single column value not for the whole column.

tigrou
  • 4,236
  • 5
  • 33
  • 59
  • Try this link: https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql – Nishant Gupta Feb 22 '18 at 11:43
  • 1
    Did you checked "Change Data Capture". – PSK Feb 22 '18 at 11:43
  • If you want to use Hashbytes, you can concat all columns in 1 derived column and hash it : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/43c243aa-3eca-4b55-9d74-e976994341ba/unique-row-id-based-on-all-columns-hashbyte-or-checksum?forum=sqlintegrationservices – Prabhat G Feb 22 '18 at 11:49
  • Maybe getting last update time helps? You can get the last updated date time like: SELECT last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id=DB_ID() AND object_id=OBJECT_ID('myTable'); – Cetin Basoz Feb 22 '18 at 12:09

1 Answers1

2

There is not such a thing in SQL Server, however you can implement it easly with triggers. I have used it to assist cache invalidation for web apps.

first create the table etags:

CREATE TABLE etags
(
   etag_name varchar(128) NOT NULL PRIMARY KEY,
   etag_value uniqueidentifier NOT NULL
)

Insert a value for the Foo table:

INSERT INTO etags VALUES ('foo', NEWID())

Then create a trigger for the foo table that updates the etag value when table changes

CREATE TRIGGER foo_etag
ON foo FOR INSERT, UPDATE, DELETE
AS
  SET NOCOUNT ON
  UPDATE etags SET etag_value = NEWID()
  WHERE etag_name = 'Foo'
Jesús López
  • 8,338
  • 7
  • 40
  • 66
  • "I have used it to assist cache invalidation for web apps." That is exactly the reason why I need this. – tigrou Feb 22 '18 at 14:57
  • I'm glad to hear that :-). I usually cache at server and browser side. ETag and If-None-Match headers help on that. – Jesús López Feb 22 '18 at 16:11