在SQL Server上索引行版本/时间戳列的结果

时间:2021-08-07 02:49:20

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.

我创建了一个包含rowversion列的表。如果我理解正确,SQL Server保证值总是递增的。因为这只是一堆字节,所以查询比如RowVer > 1567需要一个类型转换,因此会导致表扫描。

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.

在调试时,它似乎工作得很好,但是在我的表中插入任何针对它的选择。需要调查是什么类型的锁被持有。

1 个解决方案

#1


5  

No, it doesn't make sense

不,没有道理

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.

行版本/时间戳是数据库唯一的,而不是表唯一的。它将通过对行(s)的更新来更改,而不仅仅是插入。因此,这并不是完全单调递增的。

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

注意,该数字不能保证从任何特定值开始

Edit, what is "database unique"?

什么是“数据库唯一”?

MSDN says for rowversion (timestamp)

MSDN对rowversion(时间戳)说

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 database rowversion

每个数据库都有一个计数器,用于在包含数据库中包含一个rowversion列的表上执行的每个插入或更新操作。这个计数器是数据库行版本

The current value is in @@DBTS

当前值在@@DBTS中

#1


5  

No, it doesn't make sense

不,没有道理

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.

行版本/时间戳是数据库唯一的,而不是表唯一的。它将通过对行(s)的更新来更改,而不仅仅是插入。因此,这并不是完全单调递增的。

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

注意,该数字不能保证从任何特定值开始

Edit, what is "database unique"?

什么是“数据库唯一”?

MSDN says for rowversion (timestamp)

MSDN对rowversion(时间戳)说

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 database rowversion

每个数据库都有一个计数器,用于在包含数据库中包含一个rowversion列的表上执行的每个插入或更新操作。这个计数器是数据库行版本

The current value is in @@DBTS

当前值在@@DBTS中