DB2自动生成列/ GENERATED ALWAYS优缺点 [英] DB2 Auto generated Column / GENERATED ALWAYS pros and cons over sequence

问题描述

我们之前使用的是 总是生成来生成主键的值。但是现在建议，我们应该使用序列填充主键的值，而不是使用 GENERATED ALWAYS。您认为造成此变化的原因是什么？

早期代码：

 创建表SCH.TAB1 
（始终将TAB_P INTEGER生成的身份不为空（以1开头，以1，递增1，不缓存），
。
。
）； 
  

现在是

 创建表SCH.TAB1 
（TAB_P INTEGER），
。 
。 
）; 
  

现在在插入时，通过序列生成TAB_P的值。

解决方案

我倾向于使用身份列而不是序列，但我将为您比较两者。

序列可以出于任何目的生成数字，而标识列严格附加到表中的列上。

由于序列是一个独立的对象，因此它可以生成数字用于多个表（或其他任何表），并且在删除任何表时不受影响。当删除带有标识列的表时，将不会存储该标识列最后分配的值。

一个表只能有一个标识列，因此，如果要在同一表的不同列中记录多个序列号，则序列对象可以处理。 / p>

对于数据库中的序列号生成器，最常见的要求是为行分配一个技术密钥，该行为可以由标识列很好地处理。对于更复杂的数字生成需求，序列对象提供了更大的灵活性。

Earlier we were using 'GENERATED ALWAYS' for generating the values for a primary key. But now it is suggested that we should, instead of using 'GENERATED ALWAYS' , use sequence for populating the value of primary key. What do you think can be the reason of this change? It this just a matter of choice?

Earlier Code:

CREATE TABLE SCH.TAB1
 (TAB_P         INTEGER         NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
.
.
);

Now it is

CREATE TABLE SCH.TAB1
 (TAB_P         INTEGER ),
.
.
);

now while inserting, generate the value for TAB_P via sequence.

解决方案

I tend to use identity columns more than sequences, but I'll compare the two for you.

Sequences can generate numbers for any purpose, while an identity column is strictly attached to a column in a table.

Since a sequence is an independent object, it can generate numbers for multiple tables (or anything else), and is not affected when any table is dropped. When a table with a identity column is dropped, there is no memory of what value was last assigned by that identity column.

A table can have only one identity column, so if you want to want to record multiple sequential numbers into different columns in the same table, sequence objects can handle that.

The most common requirement for a sequential number generator in a database is to assign a technical key to a row, which is handled well by an identity column. For more complicated number generation needs, a sequence object offers more flexibility.

这篇关于DB2自动生成列/ GENERATED ALWAYS优缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！