[SQL]SQL Statement 相容性層級的影響
日前遇到一個SQL Statement錯誤的問題，經由幾位MDSN Forum專家的幫助，最後發生問題的原因
是來自於相容性層級的影響問題是這樣的，原本有個類似以下order by 的 SQL Statement
select ......, isnull(B.wdate,A.wdate) as wdate from TableA A left join TableA B on A.id = B.mid left join ..... left join ..... order by A.wdate,B.wdate
A & B是同一個Table，自已join in 自已，這個Table裡有wdate這個欄位
，select 裡有放置isnull(B.wdate,A.wdate) as wdate，此外order by 則是A.wdate,B.wdate
在同一台SQL 2005 SERVER中，二個不同的DB中跑這段相同的語法
結果一個沒問題可以正常執行也達成想要的結果，但另一個DB則出現Error
"排序依據清單指定多次某個資料行。排序依據清單中的資料行必須是唯一的"
由於該SQL Statement只在某一個DB會出錯，因此直覺認為是應該是設定上的問題(後來事實也證明
如此)，所以比較了一下發現該DB的”定序”不同於其它DB，但據個人認知”定序”應該只會影響
Order by的結果，不太可能會導致Statement Error，不過在查不到其它因素的情況下(事實證明有
其它因素Orz….)，只好求助MDSN Forum (真是技術人的好朋友
，原討論串在此)
在幾位專家的協助之下，找到真正的問題原因在於”相容性層級”，而相容性層級的設定主要是用
來將特定的資料庫行為相容於某個特定版本，而會出錯的這個DB其實是由SQL 2000所升級上來至
SQL 2005版本，而借由升級上來的舊DB，它的相容性層級會保留在較早之前的版本，但若是新建
立的DB則會是以該台資料庫主機版本為主，另外在加上Order by的Statement的在新舊版本是有執
行上的差異，因此造成了在不同的DB中跑相同的Order by語法，其中一個DB會出現Error
根據MDSN的文件上所述，在相容性層級80跟90，其Order By的差異性如下
| 相容性層級80 | 相容性層級90 |
|
會將 ORDER BY 清單中的資料行參考繫結至 SELECT 清單中定義的資料行，模稜兩可的資料行會被忽略，有時也會忽略資料行前置詞。這可能導致結果集以非預期的順序傳回。 例如，會接受由單一兩部分資料行 (<table_alias>.<column>) 所組成的 ORDER BY 子句 (該資料行是用來當做 SELECT 清單中之資料行的參考)，但資料表別名會被忽略。請看下列查詢。
在執行時，ORDER BY 中的資料行前置詞會被忽略。排序作業不會依預期在指定的來源資料行 ( |
資料行模稜兩可會發生錯誤。如果 ORDER BY 指定了資料行前置詞，當繫結至 SELECT 清單中所定義的資料行時，便不會忽略這些前置詞。 請看下列查詢。
在執行時，不會忽略 ORDER BY 子句中的資料行前置詞。排序作業會依預期在指定的來源資料行 ( |
由於相容性層80時，Order By會忽略前置詞，因此原先的order by a.wdate,b.wdate
，會變成了order by wdate,wdate，這樣一來當然會違反了
＂排序依據清單中的資料行必須是唯一的＂
相容性層級的設定，可以在資料庫屬性中找到
對應關係則為：
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
對於是否調整相容性層級，由於或多或少會影響到資料庫本身的一些行為模式，因此建議還是
要仔細評估一下
［參考資料］
1.問題討論串
4.sp_dbcmptlevel (Transact-SQL)
By No.18
