章立民研究室

SQL Server 允許您替運算欄位建立索引，不過要能夠替運算欄位建立索引，必須符合下列條件：

■ 運算欄位中所用到之函數的擁有者與資料表擁有者必須為同一人。

■ 運算欄位運算式必須是定性的。所謂定性的運算式也就是每次給予一組相同的輸入值時皆會傳回相同的結果。如果符合下列條件，則運算欄位運算式便是定性的：

• 運算欄位運算式中所使用的函數（包括內建的函數與使用者自訂函數）皆是定性且精確的。
  
  比方說，假設資料表內含兩個 money 資料型別的欄位 CurSalary 與 HireSalary，則運算欄位 ROUND(HireSalary－CurSalary) 是可以被編制索引的，但是運算欄位 ROUND(HireSalary－CurSalary)＋DAY(GETDATE()) 則是不能被編制索引的，此乃因為在不同的日期下 GETDATE() 函數會傳回不同的日期時間而使得該運算欄位的運算結果是非定性的。
• 運算欄位運算式中，所使用的欄位必須是該運算欄位所在資料表中的其他欄位。
• 運算欄位運算式中，沒有任何一個欄位中的資料係來自多筆資料記錄彙總後的結果。比方說，如果某欄位的內容係使用AVG或SUM等彙總函數來彙總自多筆資料記錄，將使得運算欄位運算式成為非定性的。
  
  如果您沒有辦法很肯定的判斷運算欄位運算式是否是定性的，可借助於 COLUMNPROPERTY(id, column, 'IsDeterministic' ) 函數。如果該函數傳回 1，表示運算欄位運算式是定性的；如果該函數傳回 0，表示運算欄位運算式是非定性的。在下面的範例程式碼中，我們建立一個擁有兩個運算欄位「總價」與「準備日期」的資料表「運算欄位範例資料表」，並分別去檢查這兩個運算欄位是否為定性的：
  
  CREATE TABLE 運算欄位範例資料表
  (
  產品名稱 varchar(20) NOT NULL,
  單價 money NOT NULL,
  折扣 decimal(3,2) NOT NULL,
  數量 int NOT NULL,
  出貨日期 datetime NOT NULL,
  總價 AS 單價 * 折扣 * 數量,
  準備日期 AS DATEDIFF(day,GETDATE(),出貨日期)
  );
  
  SELECT
    COLUMNPROPERTY( OBJECT_ID('運算欄位範例資料表'),
                         '總價', 'IsDeterministic' );
  SELECT
    COLUMNPROPERTY( OBJECT_ID('運算欄位範例資料表'),
                         '準備日期', 'IsDeterministic');
   
• 不涉及任何系統資料或使用者資料的存取。
• 如果運算欄位使用到 CLR 使用者自訂函數或使用者自訂資料型別的運算式，除了該運算欄位必須是定性之外，於建立此運算欄位時，還需要加上關鍵字 PERSISTED。

■ 運算欄位運算式必須是精確的。什麼樣的運算式才是一個精確的運算式呢？如果運算欄位運算式符合下列條件，則它便是一個精確的運算式：

• 運算式不可以是 float 以及 real 資料型別。

附註：
關於 float 與 real 資料型別的說明，請參閱「SQL Server 2005 完全實戰」一書的第五章。

• 在其定義中不能使用到 float 以及 real 資料型別。以下面的程式碼而言，雖然運算欄位「判定結果」是定性的，不過由於在其定義中「欄位四」使用了 float 資料型別，因此運算欄位「判定結果」將是不精確的：
  
  CREATE TABLE 判定檢測資料表
  (欄位一 int, 欄位二 int, 欄位三 int, 欄位四 float,
   判定結果AS CASE欄位四
     WHEN 0 THEN欄位一
     WHEN 0 THEN欄位二
     ELSE欄位三
   END);
  
  如果您沒有辦法很肯定的判斷運算欄位運算式是否是精確的，可借助於 COLUMNPROPERTY(id, column, 'IsPrecise') 函數。如果該函數傳回 1，表示運算欄位運算式是精確的；如果該函數傳回 0，表示運算欄位運算式是不精確的。比方說，您想要檢查先前所建立之「運算欄位範例資料表」中的兩個運算欄位「總價」與「準備日期」是否是精確的，可執行以下的程式碼：
  
  SELECT 
    COLUMNPROPERTY( OBJECT_ID('運算欄位範例資料表'), 
                         '總價','IsPrecise');
  SELECT 
    COLUMNPROPERTY( OBJECT_ID('運算欄位範例資料表'), 
                         '準備日期','IsPrecise');

■ 運算欄位的資料型別必須符合下列條件：

• 在運算欄位運算式中，所使用之資料型別不能是 text、ntext、與 image 這三種SQL Server 2005不建議再繼續使用的 LOB 大型物件資料型別。
• 只要運算欄位是一個非鍵值欄，就不用考慮在一個非叢集索引中，運算欄位是否有使用 text、ntext、與image 資料型別來建立非鍵值欄。
• 只要運算欄位是一個鍵值欄，就不用考慮運算欄位是否有使用 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 與 xml 這七種 LOB 大型物件資料型別來建立索引。

■ 在下列兩種情形，您必須指定不同的 SET 選項設定值：

• 使用 CREATE TABLE 或 ALTER TABLE 陳述式來建立運算欄位時，必須將 ANSI_NULLS 選項設定 ON。
  
  如果您不知道目前SET選項的設定值，可借助於 OBJECTPROPERTY(id, 'IsAnsiNullsOn') 函數。如果該函數傳回 1，表示 ANSI_NULLS 選項是 ON 的；如果該函數傳回 0，表示此選項是 OFF 的。比方說，您想要檢查先前所建立之「運算欄位範例資料表」，其 ANSI_NULLS 是否設定為 ON，可執行以下的程式碼：
  
  SELECT
    OBJECTPROPERTY( OBJECT_ID('計算欄位範例資料表'),
                        'IsAnsiNullsOn');
   
• 當您在建立索引或是使用 INSERT、UPDATE、或DELETE 陳述式時，您必須如圖表 1 指定這 7 個 SET 選項的設定值，以便查詢最佳化工具使用此運算欄位所建立的索引。
  
  其實，當您將 ANSI_WARNINGS 選項指定為 ON 時，SQL Server 就會視為 ARITHABORTON 選項也被指定為 ON，但是您無法借助於 DATABASEPROPERTYEX(database, 'SET選項屬性名稱') 函數來得知這個 ARITHABORTON 選項的設定狀態。如果該函數傳回 1，表示此選項是 ON 的；如果該函數傳回 0，表示此選項是 OFF 的。您可以透過 SQL Server Management Studio，開啟的「索引調校練習」資料庫之「資料庫屬性」視窗，按一下「選取頁面」的「選項」頁面，然後將右邊窗格中「其他」分類項目下方的之「ANSI警告已啟用」下拉選項指定為 True 並按下「確定」按鈕來讓設定生效，接著請執行以下的程式碼：
  
  SELECT
    DATABASEPROPERTYEX('索引調校練習',
                             'IsAnsiWarningsEnabled');
  SELECT
    DATABASEPROPERTYEX('索引調校練習',
                             'IsArithmeticAbortEnabled');
  
  執行之結果，如圖表 2 所示。

圖表 2