Hi Jeff,

Please find my answers to your questions,

1. Does the #TestDataDump table represent all new data or is this maintained in a real table that you keep adding to? If you keep adding to it, how often and how many rows are typically added?

The structure of #TestDataDump is almost the same structure of my production table and every day the table gets inserted into ~ 50000 to 100000 at this moment. but in future it may get increased. In order to tackle those kind of situations i am testing with large sets of data

2. Are there duplicates in the real table represented by #TestDataDump table insofar as the CardNumber and and ProductNumber?

Whenever the customer purchases the product on the store using the card, the information will come through text file and i am inserting into actual production table( like #TestDataDump) table. so lets take an example of purchase of 100 items can be made using the same card per day. so obviously card number will get repeated. Even product number as well. If i by the product @ 10am and the same product @ 12 am using the samecard obviously it will be as separate records.

so i am grouping the data as cardnumber with purchasedate. this logic i didn't included on the sample query

3. What percentage of rows will have HasProcessed = 0 in the table represented by #TestDataDump?

Everyday it will get ~50000 - 10000 rows appended. once the records processed then hasprocessed will be set as 1 and every 3 months once all the old data will be archived.

4. You construct the ProductNumber in your test data using N+1000, which is 100% numeric. Is the ProductNumber totally numeric or does it have non-numeric characters in it or any leading zeros?

The productnumber on my production table is varchar(30). Not sure why the original designer created with varchar. but all are numbers and no alphabets or mixed on it. As i don't want to confuse, in my sample table i kept it as int

Any suggestions please and i have ased transaction related question as well on my original post. please help me

Thanks