61935

Consequences of Indexing the rowversion/timestamp column on SQL Server

Related to my prior question about having a sequence without interim holes (a guarantee that the numbers that are visible to readers are always incrementing) enter link description here I'd like to ask if a solution I devised makes sense.

I created a table with a rowversion column. If I understand this correctly, SQL Server guarantees that the values will be always incrementing. Because this is just a bunch of bytes, queries like WHERE RowVer > 1567 would requires a cast and hence would cause table scan.

So I created an index view that would do the cast and I am querying the view. On the surface level it works (the query plan shows index seek) but I am not sure if the <strong>always incrementing</strong> guarantee still holds true if I go through the index. Please help.

Edit It seems to work fine when debugging but inserting to my table block any selects against it. Need to investigate what kind of locks are being hold.

Answer1:

No, it doesn't make sense

rowversion/timestamp is <strong>database</strong> unique, not table unique. And it will be changed by UPDATEs to row(s), not just INSERTs. Therefore, this is not strictly monotonically increasing as you want.

Note that the number is not guaranteed to start at any particular value

Edit, what is "database unique"?

MSDN says for rowversion (timestamp)

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the <strong>database rowversion</strong>

The current value is in @@DBTS

Recommend

  • Choose one key arbitrarily in a dictionary without iteration [duplicate]
  • OpenCL bytecode running on another card
  • Is there a greater chance to collide when comparing GUIDs based on a hash vs \"Guid.NewGuid()?
  • TSQL Rolling Average of Time Groupings
  • Cypher - matching two different possible paths and return both
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • MySQL performance when updating row with FK
  • Can I read an iPhone beacon with Windows.Devices.Bluetooth.Advertisement.BluetoothLEManufacturerData
  • Android app gives error “BatteryStatsImpl: reading network stats”
  • Looking for good analogy/examples for monitor verses semaphore
  • Security issues with PHP's Readfile method
  • Web.config system.webserver errors
  • Redux Form - Not able to type anything in input
  • How can I sort a a table with VBA with given text condition?
  • Converting a WriteableBitmap image ToArray in UWP
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • PHP buffered output depending on server setting?
  • Custom validator control occupying space even though display set to dynamic
  • copying resource to sdcard gives a damaged file in android
  • Spark fat jar to run multiple versions on YARN
  • ImageMagick, replace semi-transparent white with opaque white
  • All Classes Conforming to Protocol Inherit Default Implementation
  • Using jQuery closest() method with class selector
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Why is an OPTIONS request sent to the server?
  • Is there any way to access browser form field suggestions from JavaScript?
  • Array.prototype.includes - not transformed with babel
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Resize panoramic image to fixed size
  • Volusion's generic SQL folder, functionality
  • Convert array of 8 bytes to signed long in C++
  • How do I rollback to a specific git commit
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • How do you join a server to an Active Directory (domain)?
  • Understanding cpu registers
  • coudnt use logback because of log4j
  • Is it possible to post an object from jquery to bottle.py?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices