What I need (for quite a few tables) is a unique integer per table, which increments reasonably, is not set on the client side. insert performance is no issue.
Identity columns are no option because: - i can't access the column in a trigger (which i need to capture the change history and workflow) - i does not increment strictly - copying data to a new instance of the same table is problematic
I can easily put the logic on the client side (using row_number/csum or something like that) because: - i have a high level of concurrency - i have little control over the clients
I tried - identity columns, obviously - a trigger/stored procedure combination, which only worked for non-unique primary indices and single row inserts
Does anyone have any idea how to solve this? Any help would be appreciated.
(and i'm fully aware that teradata is not the best platform for what i try to do, but it's the only one i have here)
One alternative is to use an identity column (defined as a bigint) on the staging table. From there, the value is moved into your EDW without the restrictions placed on an identity column (triggers, join indexes, etc.)
I was wondering if you actually found any serious disabilities using an IDENTITY column for what u intended. We are implementing a data warehouse with Data-vault Modeling which makes a unique id necessary for all the tables.
I am more uncertain about the data types the IDENTITY column can be defined under? What is the maximum limits you can set for the column (any impacts in using the largest number )