24980

Is there an equivalent of rowversion in SQL Server, but for a table?

Question:

I am looking for something similar to <a href="https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql" rel="nofollow">rowversion</a> 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 <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql" rel="nofollow">HASHBYTES</a>, but unfortunately it only gives hash for a single column value not for the whole column.

Answer1:

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'

Recommend

  • Get the matrix after imagesc? [duplicate]
  • How to pass parameters to dll?
  • Sorting nested lists
  • Logging user actions on ASP.NET site?
  • Android Studio onClick button to display ImageView in another activity
  • Change the value of constraint for only iPhone 4 in IB
  • How can I know which element fire the event from js?
  • Upload multiple files at once using GWT
  • ASP Net Core - Mixing External Identity Provider with Individual User Accounts for Audit Tracking
  • Change color of row programmatically in WatchKit
  • How to get the index of element in the List in c#
  • command line of process by name
  • Stitching 2 images (OpenCV)
  • SQL - Select lowest values with group by and order by?
  • Cypher - matching two different possible paths and return both
  • Primefaces lazy datascroller calling load twice
  • Cannot save model when using ember render helper
  • Possible to get mouse events fired when cursor is outside page?
  • pyodbc doesn't report sql server error
  • Insert new calendar with SyncAdapter- Calendar API Android
  • Django simple Captcha “No module named fields” error
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Groovy: Unexpected token “:”
  • Replace value with Factor in r data.table
  • How to create a file in java without a extension
  • How to access EntityManager inside Entity class in EJB3
  • formatting the colorbar ticklabels with SymLogNorm normalization in matplotlib
  • Repeat a vertical line on every page in Report Builder / SSRS
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • PHP - How to update data to MySQL when click a radio button
  • C# - Serializing and deserializing static member
  • Counter field in MS Access, how to generate?
  • Bug in WPF DataGrid
  • Resize panoramic image to fixed size
  • Volusion's generic SQL folder, functionality
  • Incrementing object id automatically JS constructor (static method and variable)
  • MySQL WHERE-condition in procedure ignored
  • How do you join a server to an Active Directory (domain)?
  • coudnt use logback because of log4j
  • Linking SubReports Without LinkChild/LinkMaster