RowID in Teradata

Question

I need to output the row id using the select statement. Something like oracle string id. How can I do this in Teradat? I am trying to execute the following query, but this is an error.

select rowid,emp_id,e_name from test; Error msg : Syntax error: ROWID not allowed. 

Thanks in advance.

Answer 1

Try the window aggregation function ROW_NUMBER() :

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

Unlike RANK() , the window aggregate ROW_NUMBER() does not allow binding in the result set. Another alternative would be to use the IDENTITY column in your table, but this is erratic and causes problems in the future. It is easier to include ROW_NUMBER() in your ETL processing if you need to save a surrogate key.

It should be noted that if your ORDER BY or PARTITION BY column is distorted, your performance in the STAT FUNCTION step in terms of the query will affect me on large data sets. The PARTITION BY is optional and allows you to define a window in which the result of ROW_NUMBER() will be reset when the section changes. Usually not used with ROW_NUMBER() , but may come in handy.

Edit To uniquely identify a record with a surrogate and do not need to rely on the logic in the ETL usage and identification column in your table. A properly configured IDENTITY column will not reuse any domain values ​​when deleting records.