Filtered Index 篩選索引

之前聽過老師介紹篩選索引用使用時機,大多會使用在大資料表的某些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