RowID in Teradata

Question

I need to pull a row ID with a select statement. Something that is similar to the row ID of oracle. How would I do that in Teradata? I am trying the following query but it is throwing error.

select rowid,emp_id,e_name from test;

Error msg : Syntax error: ROWID not allowed.

Thanks in advance.

Answer 1

Try the ROW_NUMBER() window aggregate function:

SELECT ROW_NUMBER() OVER(ORDER BY empid) AS RowID_
     , empid
     , empName
  FROM test;

Unlike RANK() the ROW_NUMBER() window aggregate will not permit ties to occur in the result set. Your other alternative would be to use an IDENTITY column in your table but those are messy and cause problems down the road. It is easier to incorporate ROW_NUMBER() in your ETL processing if you need to maintain a surrogate key.

It should be noted that if your ORDER BY or PARTITION BY column is skewed, your performance on the STAT FUNCTION step in the query plan me be impacted for large sets of data. The PARTITION BY clause is optional and allows you to define a window where the result of the ROW_NUMBER() would be reset when the partition changes. Not commonly used with ROW_NUMBER() but it may come in handy.

Edit To uniquely identify the record with a surrogate and not have to rely on logic in your ETL use and identity column in your table. Configured correctly the IDENTITY column will not reuse any domain values when records are deleted.