Hi John

>I am not very clear about:

The purpose of a primary key is to uniquely identify a record in a table. You can either use a surrogate key (in which case the actual value does not have any meaning - like the simple integer you refer to) or a business key where some unique identifier is used (Acount number, Order Number and so on).

There are two problems with using business keys:

First is that they are usually governed by rules that require checking and validation. Whereas, the only rule that is really required for a PK is that it is unique in the current table. Applying additional rules to PKJs usually means additional (usually convoluted) code!

Second is that, at some point, someone will want to change the key - either because a 'wrong' value was assigned, or because the Company is changing the format of its Account Numbers, Order Numbers etc! This immediately gives you a major RI issue because you now have to cascade changes through all related tables.

>1. Since, filtered index is not optimize, how am I going to re-use the deleted candidate key but not effect the speed?

Why would you re-use a candidate key any more than a primary key?

If I understand you, you are talking about things like Customer Numbers, Order Numbers and so on. If you are thinking about having to issue numbers for transactions that get cancelled, this is merely another reason for using surrogate keys. By using a surrogate key, you can still generate all the related records WITHOUT having to issue the customer number, order number or whatever, until the user actually commits the transaction. That is the whole point of the surrogate key.

Or is there some other scenario that you have to deal with?

>2. If I have a auto-generated field like Invoice No., do you think it is good to be primary key or candicate key??

But you have answered the question yourself, no, it isn't. Presumably invoice numbers MUST be sequential, with no gaps (at least, as I understand it that is the normal practice). That 'rule' does not apply to a Primary Key whose only function is to identify a record, so an invoice number (irrespective of how it is generated) is NOT a good choice for a PK.