且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

SQL Server:与Oracle中的RowVersion等效

更新时间:2023-02-05 10:38:10

简单的答案是:否-但是使用NUMBER列和设置/更新它的触发器来创建自己很容易.

The simple answer is No - but it's easy to create one yourself with a NUMBER column and a trigger to set/update it.

Oracle 11gR2的简单示例:

A simple example for Oracle 11gR2:

CREATE SEQUENCE global_rowversion_seq;

ALTER TABLE mytable1 ADD rowversion NUMBER;

ALTER TABLE mytable2 ADD rowversion NUMBER;

CREATE TRIGGER mytable1_biu
   BEFORE INSERT OR UPDATE
   ON mytable1
   FOR EACH ROW
BEGIN
  :NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable1_biu;

CREATE TRIGGER mytable2_biu
  BEFORE INSERT OR UPDATE
  ON mytable2
  FOR EACH ROW
BEGIN
  :NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable2_biu;

(如果您使用的是较早的Oracle版本,则触发器中的分配必须通过查询完成,例如:

(If you're on an earlier Oracle version, the assignments in the triggers must be done with a query, e.g.:

  SELECT global_rowversion_seq.NEXTVAL
  INTO :NEW.rowversion
  FROM dual;

现在,请记住,在某些情况下,由于所有使用相同序列进行数据库插入/更新的争用,该设计在极端情况下(例如,具有极高插入/更新活动的数据库)可能会对性能产生影响.当然,在这种情况下,您可能首先还是会避免触发.

Now, keep in mind in some cases this design may have a performance impact in extreme situations (e.g. databases with extremely high insert/update activity) due to contention from all database inserts/updates using the same sequence. Of course, in this circumstance you probably would avoid triggers in the first place anyway.

取决于您如何使用rowversion列,***对每个表使用单独的序列.当然,这意味着行版本将不再是全局唯一的-但是,如果您仅对比较表中行的更改感兴趣,那么就可以了.

Depending on how you use the rowversion column, it may be a good idea to use a separate sequence for each table instead. This would mean, of course, that rowversion would no longer be globally unique - but if you are only interested in comparing changes to rows within a table, then this would be fine.

另一种方法是分别为每一行增加计数器-不需要序列,可以让您检测到行的更改(但不允许将任何行与另一行进行比较):

Another approach is to advance the counter for each row individually - this doesn't need a sequence and allows you to detect changes to a row (but does not allow comparing any row to another row):

ALTER TABLE mytable ADD rowversion NUMBER;

CREATE TRIGGER mytable_biu
  BEFORE INSERT OR UPDATE
  ON mytable
  FOR EACH ROW
BEGIN
  :NEW.rowversion := NVL(:OLD.rowversion, 0) + 1;
END mytable_biu;

每行将以rowversion = 1插入,然后对该行的后续更新会将其递增到2、3等.

Each row will be inserted with rowversion = 1, then subsequent updates to that row will increment it to 2, 3, etc.