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中