Teradata: Surrogate Key Concept
![Surrogate key example Surrogate key example](/uploads/1/2/6/0/126045901/580339667.png)
Database Surrogate Key Definition
How to generate surrogate keys with Teradata and Pushdown tatup Nov 25, 2014 5:29 AM Without sequence generator or expression's variable ports I find it really hard to come up with an elegant way of generating ID's.
Surrogate Key is a unique, database supplied or generated identifier generally used as the primary key/index of a table.
Mar 19, 2012 This is a good way to generate surrogate keys if you have a dual active environment and the surrogate keys don't need to be generated in sequential order. The hashing algorithm will generate the same surrogate keys on different Teradata systems but key collisions can happen. A surrogate key is an artificial simple key used to identify individual rows uniquely when there is no natural key or when the situation demands a simple key, but no natural non‑composite key exists. Surrogate keys do not identify individual rows in a meaningful way: they are simply an arbitrary method to distinguish among them. Surrogate keys are typically arbitrary system‑generated sequential integers. You can generate surrogate keys in Teradata Database using the identity column.
➠ When to use a surrogate key
- Surrogate key should be used if each row of table cannot be uniquely identified using 1 or more columns.
- Surrogate key can also be used when a unique key is too long and non-numeric.
➠ Advantages of surrogate key
- Each row can be uniquely identified within a table using surrogate key value.
- Surrogate key can be used as primary index for a table to distribute data evenly on all the AMPs.
Generating Surrogate Key In Teradata
➠ Disadvantages of surrogate key
- We cannot derive any meaning or relationship between the surrogate key and the rest of the data columns in a row, therefore surrogate keys have no meaning to the users.
- There will be cases when data will be shared among different databases. In this case, same rows(from different database) may have different surrogate key and different rows(from different database) may have same surrogate key.
![Teradata Teradata](/uploads/1/2/6/0/126045901/580193486.png)
➠ How to generate surrogate key in Teradata
- By using analytical functions
- By using CSUM analytical function, Syntax/Example 1:Single AMP (usually vproc 0) generally processes all the data when using CSUM(1,1)
- By using SUM analytical function, Syntax/Example 2:
- By using ROW_NUMBER analytical function, Syntax/Example 3:Note: '(SELECT ZEROIFNULL(MAX(emp_no)) FROM employee)' is used in above examples to generate new sequence which must be greater than the current max value present in the table.
- By using CSUM analytical function, Syntax/Example 1:Single AMP (usually vproc 0) generally processes all the data when using CSUM(1,1)
- By using Identity column, check Identity Columns page for more detail on Identity Column(Sequence) in Teradata.
Syntax/Example 4:Note: There will always be gaps in generated number when using identity column because in Teradata it is not one sequence but multiple parallel sequences(one on each AMP).