create surrogate key using @INROWNUM/@OUTROWNUM/@NUMPARTITIO

[cetzhbo]

Hello Gurus,

How can I create surrogate key using @INROWNUM/@OUTROWNUM/@NUMPARTITIONS/@PARTITIONNUM, which will minimum the number space ? such as " 1, 2,3 " is better, however " 1, 18, 39" is bad.

thanks very much!

[ArndW]

Instead of answering the question, I would like to point out that at Version 8 the transform stage has a surrogate key function that works quite well.

[chulett]

In other words, it's a bad idea.

[cetzhbo]

thanks, so please tell me what do these parameter do ? please give me some scenario about that.

[cetzhbo]

thanks, so please tell me what do these parameter do ? please give me some scenario about that.

[pksahu]

Yoc can use the below formula:-

@partitionnum+(@inrownum-1) * @NUMPARTITIONS

[pksahu]

Yoc can use the below formula:-

@partitionnum+(@inrownum-1) * @NUMPARTITIONS

[vmcburney]

There is a thread in the FAQ forum that discusses how to build a parallel counter in a Transformer using those @ variables. Note that it is very hard to get an unbroken sequence - you usually end up skipping one or more numbers at the very end of the data flow due to imbalanced parallel streams.

For example with four nodes processing 12 rows with key partitioning you can use up these numbers:
1, 5, 9
2, 6, 10, 14
3, 7
4, 8, 11

Round robin partitioning may help but then you face performance worries.