之前聽過老師介紹篩選索引用使用時機，大多會使用在大資料表的某些Hot資料。例如近一年的資料會常被查詢，所以僅對今年的資料建Index...但大家有想過其他的應用情境嗎?
近日在討論某專案的會員卡Table Schema時，專案經理問了我一個問題，他想將身分證號+卡片狀態欄位設定成PK，這樣一來就可以確保會員的有效卡在Table中只會有一筆。
我跟他說該Table中會員的有效卡及無效卡都會累積儲存，無效卡並不會從Table刪除，因此一旦該會員有兩張無效卡的話那就會違背PK而發生寫入失敗的問題。此時專案經理又問我可以針對身分證號+卡片狀態是有效的資料建PK嗎? 當時我跟他說不行。
事後我想了一下，對方只是為了確保某個身分證號在該Table中只能有一張有效卡，我們無法針對這樣的需求建PK，但我們可以建立Unique的篩選索引來達到這樣的目的啊。以下是小小的示範
我們先建立一張記錄會員卡資料的Table
我們建立一個Unique的篩選索引叫IX_Active_Card，注意下圖中Where的條件式，該Index只記錄CardStatus=10的資料。
下圖的範例執行完後，身分證號為F123456789的會員在Membership Table中就會有二筆無效卡(11111111及22222222)和一筆有效卡33333333。
此時我們如果再試圖寫入一筆有效卡44444444的資料(CardStatus=10)，就會發生錯誤，如下圖所示。該錯誤訊息很明顯的表示資料重複。
透過上面的方式我們就可以達到專案經理的需求，該Table中一個身分證號只能有一筆有效卡，但可以有多張無效卡。
參考資料來源 : 篩選索引設計指導方針
我是ROCK
rockchang@mails.fju.edu.tw
