原來 COLLATE (定序)這麼重要

　　因為一般測試環境都會和正式環境做相同的設定，以往開發系統鮮少注意資料庫的定序與伺服器定序間的差異，今天就碰到定序的問題!(七月)!
開發之前只跟對方確認 OS, DB 及 .Net Framework 的版本，前天開發完成後，就將系統安裝於客戶的機器上，因為DB內已事先匯入客戶的組織資料及使用者預設帳號(客戶不想逐一建帳號)。
試運行一天，客戶反應沒有問題，所以就將測試資料清除，正式交機!
結果下午五點多客戶來電，說某個查詢在特定條件下會造成網頁錯誤，可是在我的測試環境進行相同條件的查詢卻正常!
趕緊到客戶的機器試試，在Local上顯示「無法解析 equal to 動作的定序衝突」，再仔細看 ASP.NET 的訊息，此錯誤是發生在某一行 SQL 查詢上，此查詢會呼叫 DB上的使用者自定函數，因此懷疑是這個自定函數的問題(方向正確)!

經比對：

• 測試環境的 MS SQL Server 定序是 Chinese_Taiwan_Stroke_CI_AS，當然資料庫也是相同的定序。

• 客戶的環境 MS SQL Server 定序是 Chinese_Taiwan_Stroke_CS_AS，而資料庫是利用備份還原的方式掛上去的，所以還維持 Chinese_Taiwan_Stroke_CI_AS的定序
• 雖然利用 alter database xxxx COLLATE  Chinese_Taiwan_Stroke_CS_AS 將DB 定序修正跟 Server 一樣，但還是出現相同的問題!
• 檢視此使用者自定函數，只要在宣告 Table 的地方再加上COLLATE  Chinese_Taiwan_Stroke_CS_AS 就解決

CREATE FUNCTION myFunction (@Ids nvarchar(10))
RETURNS @result TABLE (fieldId nvarchar(10) COLLATE  Chinese_Taiwan_Stroke_CS_AS) 
AS
BEGIN
    DECLARE @r INT
    DECLARE @tb INT
    DECLARE @tempTb1 TABLE ( fieldId varchar(10) COLLATE  Chinese_Taiwan_Stroke_CS_AS)
   DECLARE @tempTb2 TABLE ( fieldId varchar(10) COLLATE  Chinese_Taiwan_Stroke_CS_AS)
   
    Set @r = 0
    Insert Into @tempTb1 Select FieldId From IdDB Where PId In (@Ids)
    Set @tb = 1 
    Insert Into @tempTb2 Select fieldId From @tbl_temp            
    Insert Into @result Select fieldId From @temp  
    SET @r = @@ROWCOUNT
    Delete From @tempTb1
    Set @tb    = 2
   
    While ( @r > 0 )
        BEGIN
           If @tb = 1
              Begin
                Insert Into @tempTb2 Select fieldId From IdDB 

                                 Where PId In (Select fieldId From @tempTb1)
                Insert Into @result Select fieldId From @tempTb2
                SET @r = @@ROWCOUNT
                Delete From @tempTb1
                Set @tb    = 2
              End
           Else
              Begin
                Insert Into @tbl_temp Select fieldId  From IdDB  

                                   Where PId In (Select fieldId From @tempTb2)
                Insert Into @result Select fieldId From @tempTb1  
                SET @r = @@ROWCOUNT
                Delete From @tempTb2
                Set @tb    = 1             
              End
        END
    Delete From @tempTb1
    Delete From @tempTb2
    RETURN
END      

這次的經驗，讓我以後在設定 DB 時會更加謹慎注意定序問題!