在SQL Server上为rowversion/timestamp列建立索引的后果 [英] 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.

我用rowversion列创建了一个表.如果我正确理解这一点，SQL Server保证这些值将始终递增.因为这只是一堆字节，所以像WHERE RowVer > 1567这样的查询将需要强制类型转换，从而导致表扫描.

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 always incrementing 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.

推荐答案

不，这没有道理

rowversion/timestamp是数据库唯一的，而不是表唯一的.并且它将被UPDATE更改为行，而不仅仅是INSERT.因此，这并不是严格按照您的意愿单调增加.

rowversion/timestamp is database 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表示行版本(时间戳)

每个数据库都有一个计数器，该计数器针对在数据库中包含rowversion列的表上执行的每个插入或更新操作递增.此计数器是数据库行版本

当前值位于 @@DBTS

The current value is in @@DBTS

这篇关于在SQL Server上为rowversion/timestamp列建立索引的后果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！