I currently have a table in SQL server that has an identity field on it, I need to bring this over into Teradata, but there is no simple way of keeping the same identity fields (which were used in joins). So I am basically creating a new table with that field as an integer within Teradata. Now I need to have a unique field identified in this table. I would prefer for it to be automatically generated and an integer but really don't like the idea of using Identity due to lack of flexibility once its created (moving data from dev to production, etc.)
Will ROWID get me the uniqueness that I need, multiple users will be inserting data into the table. What is the best way to go here?
In Teradata, ROWID is not exposed as a column (except when defining a join index) and cannot be used in queries. As far as I know, an identity column is your only option if you require an automatically generated unique value.