Azure SQL Databaseでの照合順序について

はじめに

断片化したインデックスを自動的に再構築／再構成するためにAzure Functionsで関数を作成、アプリ側で再構築／再構成してほしくないインデックスを除外リストCSVに入れてもらい、BULK INSERTでワークテーブルに取り込み、sys.DM_DB_INDEX_PHYSICAL_STATS、sys.OBJECTS、sys.INDEXES、sys.SCHEMASとJOINすると以下のエラーが出るのを調べた結果を覚え書き

SQLError

Cannot resolve the collation conflict between "Japanese_CS_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

照合順序とは

データの文字の大小関係を比較する場合の基準となるもの
ソートや検索に関わってくる
e.g.)
「海」と「空」と「陸」はどれが一番大きい？ってのを決めるルール

なんでエラー？

何も考えずにツルっと作成したテーブルには照合順序は設定されていない
片やsys.INDEXESやsys.OBJECTS、sys.SCHEMASには照合順序"SQL_Latin1_General_CP1_CI_AS"が設定されている
照合順序が設定されているものとされていないものをJOINの結合条件で使用すると、照合順序を指定してないものはデフォルトの照合順序に設定される
偶々一致すればエラーにならないが、件の場合は照合順序が異なるためにエラーになる

エラー時の対応方法

• テーブル作成時に照合順序を指定する

CreateTable

CREATE TABLE excludeList
  (SCHEMA_NAME sysname collate SQL_Latin1_General_CP1_CI_AS, 
   TABLE_NAME sysname collate SQL_Latin1_General_CP1_CI_AS, 
   INDEX_NAME sysname collate SQL_Latin1_General_CP1_CI_AS);

• JOIN時に照合順序を指定する

Join

    SELECT *
    FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) AS A 
    LEFT OUTER JOIN  SYS.OBJECTS AS B 
      ON A.OBJECT_ID = B.OBJECT_ID 
    LEFT OUTER JOIN  SYS.INDEXES AS C 
      ON A.OBJECT_ID = C.OBJECT_ID
     AND A.INDEX_ID = C.INDEX_ID 
    LEFT OUTER JOIN  SYS.SCHEMAS AS D 
      ON B.SCHEMA_ID = D.SCHEMA_ID 
    LEFT OUTER JOIN dbo.excludeList AS E
      ON D.NAME = E.SCHEMANAME collate SQL_Latin1_General_CP1_CI_AS
     AND B.NAME = E.TABLENAME collate SQL_Latin1_General_CP1_CI_AS
     AND C.NAME = E.INDEXNAME collate SQL_Latin1_General_CP1_CI_AS

最後に

あまり照合順序を意識したことがなかったが、いろいろな並び順に影響してくる部分なので今後気を付けて見ようと思う