Hi John,

>I read couple of articles that talk about primary key, and it mentioned that we should use integer as primary key and don try to re-use it. If we need something unique such as ID number or customer code, we could place it as candicate key. It is the same effect.

This a choice of design. It is not a must. Some use intelligent keys (like itemno) some use a surrogate key that replaces the intelligent one. Some use a 16 byte long GUIid instead of an integer field. This has advantages when inputting records at non-connected sites. Using surrogate keys has other characteristics over intelligent ones. You might want to look at Thread #605083 Message #605969

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

An extra non-filtered normal key on this candidate key.
However you've got to beware that if you want to have some contrains like "I can not change this Itemno key whenenver this item has been sold to a particular client" you cannot solve this with the RI builder anymore: You'got to write it yourself. You'd not be the first one who'll step into this trap.

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

Yes, because Invoice No. ussually is one item that is stable: Once assgned, never changed. It makes no sense to make this a candidate.

Walter,