ITコーディネータのシュウです。

これは、昨年11月に島根の実家に帰省した際に、牡丹の花で有名な大根島の「由志園」でお土産に購入した牡丹の花を、父親が家の庭に植えていたものが、今年になって咲いたのを撮った写真です。綺麗に咲いたので、とても嬉しいですね。
現在、大根島は量、質ともに全国一の牡丹苗の生産地ということです。大根島は水田や農地が少なく養蚕と漁業以外に産業とよべるものはなかったそうですが、昭和３０年代から島の女性達は家族を養うために全国へ牡丹などの苗木を売り歩く行商に出かけたそうです。島の女性達によって美しい牡丹の花は全国にたくさんのファンを作り、大根島の名前は牡丹の花とともに知られるようになりました。機会があれば、また「由志園」に行ってみたいと思います。

＜本日の題材＞
一時テーブル使用時の照合順序（SQL Server）

照合順序については、以前もブログで取り上げたことがありますが、最近、お客様の開発案件に携わる中で、一時テーブルを使ったSQLで照合順序に関するエラーが発生し、対応した経緯がありますので、ブログで取り上げてみたいと思います。

例)
以前ブログで取り上げた「照合順序（SQL Server）の異なるデータベース間のジョイン」と現象は同じことになりますが、SQL Serverの照合順序と、あるシステム用に作成したデータベースの照合順序が異なるときに、一時テーブルを利用し、かつ、データベース上のテーブルと一時テーブルをジョインするようなときに起きる現象になります。
サーバーのプロパティで確認できる「サーバーの照合順序」は「Japanese_CI_AS」であるSQL Serverを使用するとします。

ここで、今回のシステムで使用する新しいデータベースの照合順序を、「Japanese_CS_AI_KS_WS」とします。（日本語で、大文字と小文字、ひらがなとカタカナ、全角と半角を区別する照合順序）

この環境で一時テーブルを作成した場合、一時テーブルは、システムデータベースの「tempdb」に作成されるため、一時テーブルの照合順序は「tempdb」の照合順序の設定「Japanese_CI_AS」に従って作成されます。

新しく作成したデータベースで、社員マスタと部門マスタを作成して、以下のようにJOINしてデータを抽出します。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

これを、部門マスタを一時テーブルに変更して実行してみます。

CREATE TABLE #bumon(
BUMON_CD varchar(3) NOT NULL,
BUMON_NAME varchar(20) NULL);

INSERT INTO #bumon
SELECT * FROM dbo.BUMON;

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN #bumon b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 13
equal to 操作の "Japanese_CI_AS" と "Japanese_CS_AI_KS_WS" 間での照合順序の競合を解決できません。」

テーブルの存在するデータベースとtempdbデータベースの照合順序が異なるためにこのエラーが起きることが確認されました。

そのため、前回のブログでも取り上げたように、ジョインする項目にCOLLATE句を使用するか、一時テーブルを作成するときに、COLLATE句を設定することで、エラーを回避できます。

ジョインの際にCOLLATE句を使用する場合：

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN #bumon b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CS_AI_KS_WS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

一時テーブルを作成するときに、COLLATE句を使用する場合：

CREATE TABLE #bumon(
BUMON_CD varchar(3) COLLATE Japanese_CS_AI_KS_WS NOT NULL,
BUMON_NAME varchar(20) COLLATE Japanese_CS_AI_KS_WS NULL);

一時テーブルとのジョインでもエラーにならずに抽出できることが確認されました。使用するデータベースの照合順序を、SQL Serverの照合順序と異なるものを使用するときには、注意が必要ですね。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

 

今年もあっという間にあと数日というところまで来てしまいました。今年の後半は仕事もかなり忙しくなり、なかなか時間も取れず、ブログの更新ができませんでしたが、気力を振り絞って書いています。
写真は、今年田舎に帰省した時に寄った出雲大社です。久しぶりに父親と一緒にお参りしたのですが、父親が子供のころは、正月に自転車で数時間をかけてお参りに行ったという話を聞き、今は車で1時間もかからずに行けるので、昔は大変だったんだなと改めて感じました。中を歩くと、旧暦の10月に全国から出雲大社に集う八百万の神様がお泊りになるという十九社という社があるのも初めて知りました。出雲地方以外、旧暦の10月のことを普通は神無月といいますが、出雲地方では神在月と言います。おもしろいですね。

さて、今年は元号が新しく令和になった記念すべき年でしたね。私としては、特にラグビーワールドカップでは、ONE TEAMという言葉がとても心に残りました。思い出せばいろいろなことがありましたが、大晦日に1年を振り返ってよかったと言えるように、最後まで頑張ろうと思います。皆さんも頑張りましょう！

＜本日の題材＞
照合順序（SQL Server）の異なるデータベース間のジョイン

SQL Serverの照合順序について、以前取り上げましたが、今回は、異なる照合順序のデータベース間でのデータのジョインをしたいケースが発生した場合に、可能なのかどうかについて確認してみました。これは、新しく作成したデータベースから、元々運用していた基幹のデータベースのテーブルを参照して抽出を行おうとしたら、照合順序が違うためにエラーが発生したという問合せを受けたことがあったので、自分でも一度試してみたいと思ったためです。照合順序とは、データの文字の大小関係を比較する場合の基準となるものをいうということで、前回説明しましたように、複数のテーブル間のジョインを行う際にも、重要な要素になります。

例）
照合順序が「Japanese_CI_AS」の既存のデータベース「BLOG」とは別に、「Japanese_90_BIN」という新しいデータベース「BLOG2」を作成します。

 ここで、元々「Japanese_CI_AS」の照合順序のデータベース「BLOG」にあるテーブル「BUMON_M」と同じレイアウトのテーブルを、新しく作成した照合順序が「Japanese_90_BIN」のデータベース「BLOG2」に作成して、データも登録します。

その後、「SYAIN_M」と「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

まず、「BLOG」データベース内でジョインする場合は、

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

 上記を、「BLOG」データベースの「SYAIN_M」と先ほど作成した「BLOG2」データベースの「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 4
equal to 操作の "Japanese_90_BIN" と "Japanese_CI_AS" 間での照合順序の競合を解決できません。」

上記の場合に正しく結果を出す方法は、データベースの照合順序を同じになるように、データベースを作り直すという方法もありますが、ジョインする項目について、COLLATE句を使用する方法があります。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM BLOG.dbo.SYAIN_M s
JOIN BLOG2.dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CI_AS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

エラーなく、結果が抽出されたことが確認できます。ただし、ジョインなどで必要な項目については全て、SQLの中でCOLLATE句の設定が必要になるため、面倒な部分もあります。

ちなみに、データベース自体の照合順序を変更する場合は、前回のときに取り上げたように、ただデータベースの照合順序を変更するだけではなく、既存のテーブルの各項目について、COLLATE文で照合順序を指定してALTER TABLE文を実行する必要がありますので、照合順序を正しく設定した空の新規データベースを作成した後に、テーブルの作成と、既存データの登録を行ったほうが早いケースもあると思います。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

ちょっと前に、出雲神話で有名な島根県に行く機会があり、時間があったので、たくさんの銅鐸が出土した加茂岩倉遺跡と、銅剣がたくさん出土した荒神谷遺跡を見に行って来ました。上の写真が加茂岩倉遺跡、下が荒神谷遺跡です。
もともと神話など、古代の歴史に興味があったこともあり、出雲には何かあるような気がして、この考古学上の歴史的な発見であった2つの遺跡に是非行ってみたかったので、とてもいい機会でした。
アマテラスの弟で出雲族の祖とされ、ヤマタノオロチ退治で有名なスサノオノミコト、イナバノ白ウサギで有名な国造りを行ったオオクニヌシノミコト、そして出雲の国譲りなどいろいろな話しが残されています。古代の日本において、邪馬台国がどこにあったのか、そして大和朝廷がどのようにつくられていったのか、いろいろな説がありますが、とても興味深い内容ですね。いつか、真実が明らかにされる日が来るのを楽しみにしたいと思います。

いよいよ、今年も残すところあとわずかになりました。いつの間にか年末になってしまったという感じです。今年も1年間いろいろとありがとうございました。なかなか忙しく、記事をアップする頻度がとても少なくなってしまいましたが、来年も頑張って記事を書ければと思っていますので、どうぞよろしくお願いいたします！

＜本日の題材＞
照合順序（SQL Server）

SQL Serverで、データの文字の大小関係を比較する場合の基準となるものを、照合順序（Collation）と呼んでいます。例えば、アルファベットの「a」「A」、かなの「あ」「ア」「ｱ」を小さいほうから順に並べたらどう並ぶか、漢字の「川」「皮」ではどちらが大きいのかなどの、文字の大小関係を決めているものになります。
様々なシステムでは、データを名前順に並べるとか、データが一致するものを検索するなどの処理が多々存在します。この照合順序が異なれば、クエリの結果が違ってきてしまうため、この文字の大小関係を決める照合順序というものはとても重要な要素になります。

SQL Serverの照合順序には、SQL照合順序とWindows照合順序の2種類があるとのことですが、SQL照合順序はUnicode データ型をサポートしていなかった SQL Server 6.5 以前のバージョンとの互換性のみを目的としている照合順序であるため、基本的に照合順序と言えば、Windows照合順序のことと考えればよいようです。

それでは、例をあげて照合順序について確認してみます。
あるデータベースの照合順序を確認してみると、「Japanese_CI_AS」となっていますが、他にも、CS、KS、WSなどのオプションがあります。例えば、「Japanese_90_CS_AS_KS_WS_SC」。

それぞれを簡単に説明すると、

・Japanese：辞書順に並び変えた場合の並び順が、日本語辞書順であることを表しています。ただし、ソート順の定義で、
Japanese, Japanese_XJIS, Japanese_Bushu_Kakusu, Japanese_Unicode
などの種類があります。

・90：照合順序のバージョンを表しています。 90 はバージョン 9.0 である SQL Server 2005、100 はバージョン 10.0 である SQL Server 2008 を表しています。

・CS：大文字と小文字を区別します。このオプションを設定すると、大文字より小文字が先に並べ替えられます。

・AS：濁音・半濁音・アクセントの有無を区別します。このオプションを選択しないと、濁音・半濁音・アクセントが区別されません。

・KS：ひらがなとカタカナを区別します。このオプションを選択しないと、ひらがなとカタカナは同じものと見なされます。

・WS：全角文字と半角文字を区別します。このオプションを選択しないと、同じ文字の全角表記と半角表記は同じものと見なされます。

・SC：SQL Server 2012 以降で、辞書順に並べる場合に補助文字を認識するかどうかを区別します。

また、並び順は、辞書順ではなくバイナリ順 (ビット配列順、文字コード順、コードポイント順) もあります。バイナリ順の照合順序の名前は、Japanese_90_BIN や Japanese_90_BIN2 のように CS_AS_KS_WS の部分が BIN または BIN2 となります。

「Japanese_CI_AS」となっているデータベースで具体的に試してみます。「大小比較」というテーブルを作成し、そこに、下記のようにデータを登録し、データを抽出してみます。

insert into dbo.大小比較(no, 文字) values(1,'あ');
insert into dbo.大小比較(no, 文字) values(2,'ア');
insert into dbo.大小比較(no, 文字) values(3,'ｱ');
insert into dbo.大小比較(no, 文字) values(4,'皮');
insert into dbo.大小比較(no, 文字) values(5,'川');
insert into dbo.大小比較(no, 文字) values(6,'革');
insert into dbo.大小比較(no, 文字) values(7,'大');
insert into dbo.大小比較(no, 文字) values(8,'題');
insert into dbo.大小比較(no, 文字) values(9,'1');
insert into dbo.大小比較(no, 文字) values(10,'１');
insert into dbo.大小比較(no, 文字) values(11,'a');
insert into dbo.大小比較(no, 文字) values(12,'A');

select * from dbo.大小比較
order by no;

select * from dbo.大小比較
order by 文字;

次に、条件として、「1」に等しいものを抽出してみます。

select * from dbo.大小比較
where文字 = '1'
order by 文字;

全角と半角が同じものとして認識されているのが確認できます。
同様に、条件として、「あ」に等しいものを抽出してみます。

select * from dbo.大小比較
where文字 = 'あ'
order by 文字;

ひらがなとカタカナが同じものとして認識されているのが確認できます。

上記の内容を区別するためには、オプションの部分を変更する必要があります。

ここで、このテーブルを含む「TEST」データベースの照合順序を変更してみます。

ALTER DATABASE TEST COLLATE Japanese_CI_AS_KS_WS;

データベースの照合順序を変更した状態で、先ほどのSQLを実行してみます。

何故か、結果は同じです。これは、データベースの照合順序を変更しても、既存のテーブルの照合順序は変更されないためで、下記のように項目について照合順序を設定してテーブルを変更する必要があります。

ALTER TABLE dbo.大小比較
ALTER COLUMN 文字 varchar(20)
COLLATE Japanese_CI_AS_KS_WS;

この状態で、再度、前回行ったSQLを実行してみます。

全角と半角文字、ひらがなとカタカナが区別されて、それぞれ該当する1行のみが抽出されるようになりました。

今日は以上まで

にほんブログ村