Existing Table - Teradata Vantage

row_number() is more likely to impact performance.

Example: IDENTITY Clause with GENERATED ALWAYS and NO CYCLE

The table iris_attribute_train does not have a unique row identifier:

SELECT TOP 5 * FROM iris_attribute_train ORDER BY pid;

        pid     attribute    attrvalue
-----------  ------------  -----------
          1  petal_width           0.2
          1  sepal_width           3.5
          1  petal_length          1.4
          1  sepal_length          5.1
          2  sepal_width             3

The following CREATE and INSERT statements create a new table, iris_attr_train_uniqueid, which is identical to iris_attribute_train but has an additional unique id column named a1.

CREATE MULTISET TABLE iris_attribute_train_uniqueid (
  a1 INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1 NO CYCLE),
  pid INTEGER,
  attribute VARCHAR(30),
  attrvalue FLOAT
);

INSERT INTO iris_attribute_train_uniqueid
  SELECT null, iris_attribute_train.* FROM iris_attribute_train;

SELECT TOP 5 * FROM iris_attribute_train_uniqueid ORDER BY a1;

         a1          pid     attribute    attrvalue
-----------  -----------  ------------  -----------
          1           57  petal_width           1.6
          2           57  sepal_length          6.3
          3           57  petal_length          4.7
          4           57  sepal_width           3.3
          5          118  sepal_width           3.8

Example: row_number()

CREATE MULTISET TABLE test04 AS (
  SELECT product, mttf, row_number() OVER (ORDER BY 1) AS id_col FROM factory_7
) WITH DATA;