{coding}Sight
アプリケーションを開発したり、SQLデータベースシステムでコードを記述したりするときは、データのソートと比較方法を理解することが重要です。 データを特定の言語で保存することも、SQL Serverで大文字と小文字を区別するデータと大文字と小文字を区別しないデータを別々に処理することもできます。 Microsoftは、このような要件を制御および対処するために、照合順序と呼ばれるSQL Server設定を提供しています。
SQL Serverの照合順序とは何ですか？
以下のように、SQL Serverのさまざまなレベルで照合順序を設定できます。
- サーバーレベル
- データベースレベル
- 列レベル
- 式レベル
サーバーレベルの照合は、SQL Serverインスタンスレベルの照合と呼ばれることがあります。
データベースの作成時に特定の照合順序を選択しない場合、データベースレベルの照合順序はサーバーレベルの照合順序設定から継承されます。 データベースレベルの照合順序は、後で変更することもできます。 データベースの照合順序の変更は、照合順序の変更後に作成される予定のオブジェクトまたは新しいオブジェクトにのみ適用されます。
新しい照合順序では、最後の照合順序の種類でソートされたテーブルに格納されている既存のデータは変更されません。 アプリケーションチームは、新しい照合順序設定のために、この保存されたデータ変換を処理するためのさらなる計画を必要とします。
それを行うにはいくつかの方法があります。 1つは、既存のテーブルから新しい照合で作成された新しいテーブルにデータをコピーし、古いテーブルを新しいテーブルに置き換えることです。 また、新しい照合順序を使用してテーブルデータを新しいデータベースに移動し、古いデータベースを新しいデータベースに置き換えることもできます。
注:照合順序の変更は複雑な作業であり、必須のビジネスケースがない限り、照合順序を変更することは避けてください。
SQL Serverでデータベースの照合順序を検索して変更するにはどうすればよいですか？
先に進み、SQL Serverインスタンスとそのインスタンスでホストされているすべてのデータベースの照合順序を確認しましょう。 照合順序を確認するには、SQL Server Management Studioを使用してデータベースまたはインスタンスレベルのプロパティウィンドウにアクセスするか、以下のT-SQLステートメ 各データベースの照合順序は、system object sysに格納されます。データベース–この情報を取得するためにアクセスします。私は上記のT-SQL文を実行し、以下の出力を得ました。 すべてのデータベースとサーバーレベルの照合順序がSql_Latin1_General_Cp1_Ci_Asと同じ設定になっていることがわかります。 これは、データベース照合順序の作成中にサーバーレベルの照合順序によって継承され、デフォルト値は変更されていないことを意味します。
ここで、SQL Server Management StudioでGUIを使用してデータベースの照合順序を確認する方法を示します。
まず、SQL Server Management Studioを使用してSQL Serverインスタンスに接続します。 インスタンスノードを展開し、その後にDatabasesフォルダを展開します。 ターゲットデータベースを右クリックし、プロパティを選択します:
以下のデータベースプロパティウィンドウが表示されます。
ここで、左側のパネルからオプションタブをクリックします。 右側のパネルに複数のプロパティ設定が表示されます。 照合順序はこのページの最初のプロパティです–上記のT-SQLスクリプトと同じであることがわかります。
同様に、SQL Serverインスタンスノードをクリックし、インスタンスレベルのプロパティを右クリックして、サーバーレベルの照合順序を表示できます。
この照合順序を新しい照合順序に変更する場合は、[照合順序]ドロップダウンをクリックして、必要なオプションを選択するだけです。 それを行う前に、データベースの完全バックアップを行っていることを確認してください。
このデータベースに大文字と小文字を区別するSql_Latin1_General_Cp1_Cs_Asを使用して同様の照合を選択し、OKをクリックして適用しました。 メモ: そうしないと、モードをシングルユーザーのモードに切り替えて、この構成を変更する必要があります。
T-SQL文を使用して、このデータベース照合順序を変更することもできます。 そのためには、ALTER DATABASE文のCOLLATE句を使用します。
まず、データベースをシングルユーザモードに切り替え、照合順序を変更し、最後にデータベースをマルチユーザモードに移動しました。
--Change Database Collation using T-SQL USE master; GO Alter DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS; GOAlter DATABASE SET MULTI_USERSQL Serverでサポートされているすべての照合順序を一覧表示
このセクションでは、SQL Serverで使用可能なすべての照合順序を検索する方法を説明します。 まず、SQL Serverインスタンスでサポートされているすべての照合順序のリストを取得する方法を示します。
SQL Serverには、すべての照合順序を取得するために使用できるfn_helpcollations()というシステム関数があります。
以下のコマンドを実行してリストを表示します。
--Display the list of all collations SELECT name, description FROM fn_helpcollations()出力セクションでサポートされている5508の照合順序をすべて見ることができます。 どの照合順序を選択するかわからない場合は、以下のスクリプトでWHERE句を使用して、データベースに設定できるすべての照合順序を除外できます。
たとえば、データを米国英語で保存し、SQL Serverで大文字と小文字を区別する形式で処理する必要があるとします。 以下のコマンドを使用して、クエリの可能な照合順序とサポートされている照合順序のリストを取得できます:
--Display the list of all collations with WHERE clause SELECT Name, Description FROM fn_helpcollations() WHERE Name like 'SQL_Latin1%' AND Description LIKE '%case-sensitive%'出力には、クエリを満たす10個の照合順序のみが表示されます。 上記のスクリプトを使用して、さまざまな照合順序を除外できます。
データベース照合順序の変更がクエリ出力に与える影響
このセクションでは、同じクエリの2つの出力が異なる照合順序で実行された場合の違いを最初に、照合順序（Sql_Latin1_General_Cp1_Cs_As）を使用してMSSQLという名前のデータベースを作成します。 次に、同じクエリを2回実行して出力を取得します。 後で、照合順序をSql_Latin1_General_Cp1_Ci_Asに変更し、同じクエリを再度実行して出力を取得します。 両方の出力を比較し、データベース照合順序の変更の影響を理解することができます。 それでは、データベースの作成から始めましょう。
下の画像に示すように、新規データベース作成ウィンドウを起動します。 T-SQLを使用してこのデータベースを作成することもできます。 その後、データベース名とそのデータファイルを確認できます。 次に、左側のパネルの2番目のタブをクリックして、照合プロパティウィンドウに切り替えます。
このデータベースの照合順序名がデフォルトであることがわかります。 これは、このデータベースがサーバーレベルの照合順序の種類から照合順序を継承することを意味します。 [照合順序]ドロップダウンをクリックして、新しい照合順序を選択します。
私はこのデータベースのために以下の照合Sql_Latin1_General_Cp1_Cs_Asを選択しました–デフォルトのものではありません。 [OK]をクリックして、データベースの作成を続行します。
次に、新しく作成されたデータベースのデータベース照合順序を確認します。 前のステップで選択したように、Sql_Latin1_General_Cp1_Cs_Asであることがわかります。
Sql_Latin1_General_Cp1_Cs_Asでは、CSは大文字と小文字を区別するモードを表し、CIは大文字と小文字を区別しないモードを表します。 これで、以下のT-SQLコードまたは任意のコードを実行して出力を取得できます。
同じコマンドを2回実行しました。 最初のスクリプトは列名を大文字のSYS値でフィルタリングしますが、2番目のスクリプトは同じ列を小文字のsys値でフィルタリングします。 出力セクションでは、最初のスクリプトは出力を表示しませんが、2番目のスクリプトは大文字と小文字が区別されるため、出力を表示しています。
Select * from sysusersWhere name='SYS'GoSelect * from sysusersWhere name='sys'GO
次に、以下のT-SQL文を実行して、このデータベースの照合順序を大文字と小文字を区別しない照合順序Sql_Latin1_General_Cp1_Ci_Asに変更します。 SQL Server Management Studioのデータベースプロパティウィンドウにアクセスして、GUIを使用して変更することもできます。
--Change database collation to SQL_Latin1_General_CP1_CI_ASUSE master; GO Alter DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS; GOAlter DATABASE SET MULTI_USER上記のスクリプトを一度に実行しましたが、データベースの照合順序は大文字と小文字を区別しない新しい照合順序に正常に変更されました。
この変更を確認するには、以下のスクリプトを実行して、新しく作成されたデータベースMSSQLの照合順序を確認します。 下の画像で、このデータベースに新しい照合順序が設定されていることがわかります。
この変更の影響を確認するために、照合順序を変更する前に同じT-SQL文を再度実行します。 今見ることができるように、両方のT-SQL文が出力にあります。
結論
SQL Serverの照合が重要であることが明らかであることを願っています。 SQL Serverの任意のレベルで照合順序を変更した場合にどのような影響が残るかを定義しました。 常に適切な計画を行い、最初にあなたの低いライフサイクル環境で変更をテストします。
次の記事では、サーバーレベルの照合順序を変更するためのステップバイステップの方法を紹介します。