DSXchange

I know this "how do i create a unique number for the data that doesn't have a good key candidate?" issue has been beaten to death. In searches I did and talking to other folks I haven't seen much use of @INROWNUM. What is the best practice or what are the pros and cons of using this to create that unique field?

Thanks..

@INROWNUM starts at 1 with every new run. You would already add it to another number to get something unique.

If you're using a database I would suggest that to generate unique keys.

Ogmios

I left out an important point : in this case the table is being recreated.

Then @INROWNUM is a good choice, because it is guaranteed to be unique.

If you have a constraint on the output link, all this means is that you will have gaps in your sequence. If all you're after is uniqueness this is not a problem.

If you don't want gaps in the sequence, prefer @OUTROWNUM.

PilotBaha wrote:I left out an important point : in this case the table is being recreated.

Some databases have the option to turn off generation of autoincrement fields so that they can be loadad with pre-existing data.

I've had a lot of success with @OUTROWNUM, even when appending a table where the last used number is passed in as a job parameter. People are moving to a shared counter design such as a hash file because there is an increasing use of parallel processing, either by multiple instance standard jobs or parallel jobs. Are you sure your job will never become a multiple instance job?

I am sure 99% It's a job that creates data for a repository to be used by a Crystal Reports report. Since it's a process that recreates one table and there will be no other update or insert to it that makes me feel comfortable with it.