リンクサーバーは重い

SQLでリンクサーバーとJOINしようとすると、「equal to 操作での照合順序の競合を解決できません」というようなエラーが出る。
その場合、JOINの条件たる「on a.XXXX=b.XXXX」の後におまじないのように

COLLATE JAPANESE_CS_AS_KS_WS


と書くと解決できる。異なるDBの照合順序を、SQLサーバーに教えてあげるわけである。
おまじないだから意味を覚える必要はないのだけれども、「CS」「AS」「KS」「WS」というのは、

「C」=大文字小文字
「A」=アクセント
「K」=ひらがなカタカナ
「W」=全角半角


で、それを「S」=区別する、ということらしい。ちなみに区別しないは「I」。

…のだが、如何せんこの照合順序の競合を一所懸命解決しようと頑張っているからリンクサーバーは重い。
が、そもそも、SQLサーバーにはデフォルトの照合順序があって、DBやテーブルの作成時に特に指定しなければ、「Japanese_CI_AS」になっているようである。
DBに設定されていない照合順序まで、こちらから指定して解決してあげる必要はないから、「COLLATE Japanese_CI_AS」と指定してあげさえすれば、競合は解決する。
そして、解決しようとする労力が減った分、軽くなる。

なお、DBに設定されている照合順序は、下記のSQLで確認することができる。

SELECT SERVERPROPERTY('collation')

日付の指定

SQLで、where句に日付を指定する場合についてのちょっとしたメモ。

データ作成日([create_datetime])が2016年8月のものを抽出したいとする。なお、このカラムはdatetime型であることとする。

where
[create_datetime] >= '2016-08-01' and
[create_datetime] <= '2016-08-31'


このように条件を指定すれば、2016年8月に作成されたデータがすべて抽出できるかというと、そういうわけではない。
2行目FROM「'2016-08-01'以上」の方は問題ないのだけれども、3行目TO「'2016-08-31'以下」は、実際には「'2016-08-31 00:00:00 000'以下」だから、この時刻を1msでも越えたら範囲外になる。だから、この条件では、事実上、「'2016-08-31'」のデータは抽出されない。

3行目TOを変更して、

where
[create_datetime] >= '2016-08-01' and
[create_datetime] < '2016-09-01'


とすれば、「'2016-09-01'未満」となるから8月分のデータはすべて抽出できる。だからこれで良しと言えないことはないのだけれども、何かのツールに組み込んでこれをパラメーターにする場合、ユーザーに常にプラス1日で入力して貰うことには、理解を得がたい場合もあるだろう。

そこで、次のようなSQL文を書く人もいる。

where
[create_datetime] >= '2016-08-01' and
convert(date,[create_datetime]) <= '2016-08-31'


[create_datetime]の値をdate型に変換すれば、8月31日のデータはすべて'2016-08-31'という形になるから、この条件で8月分のデータはすべて抽出できることになる。

僕も、最初の内はこういう書き方をしていたのだけれども、これだと[create_datetime]をすべてdate型に変換した上で、その変換結果の中から該当日付のデータを抽出することになるので、処理としては重くなる。[create_datetime]にインデックスが張られれていたとしても、convert(date,[create_datetime])に対してそのインデックスが効くわけではない。

それで、こんな書き方をするようにした。

where
[create_datetime] >= '2016-08-01' and
[create_datetime] < dateadd(day,1,'2016-08-31')


「'2016-08-31'プラス1日(='2016-09-01')未満」という条件になるから、これで8月分のデータがすべて抽出できる。前のSQLと違って、検索するカラムのデータに対して型変換を掛けているわけではないから、処理が無用に重くなるということもないし、毎回1日足す必要がないからユーザーフレンドリーでもある。

各種変換

以前のエントリをちょっと見直していたら、昨年末、「今度はSQLのメモでも書いてみるか」などと書いていた。すっかり忘れていたけれども、予告していたものならもう少し書かないとカッコ付かないので、書いておくことにする。ただし、書くごとにどんどん大したことがなくなって行くのは致し方ない。

簡単なんだけれども、すぐに忘れてしまってその都度検索してしまうSQLのメモ、その2。

--指定の値より大きい最小の整数を返す…切上げ
select ceiling( 1.11 ) --'2'

--指定の値より小さい最大の整数を返す…切捨て
select floor( 1.11 ) --'1'

--ステートメントを宣言
DECLARE @postalcode nvarchar(8) = '236-0044'

--指定のカラムの値の順序を指定して並べ替え
ORDER BY case [カラム] when N'鈴木' then 1 when N'岡本' then 2 when N'佐藤' then 3 else 4 end

--データの前後のスペースを取り除く。
--先頭
select ltrim(' あああああ ') --'あああああ '
--末尾
select rtrim(' あああああ ') --' あああああ'
--両方
select ltrim(rtrim(' あああああ ')) --'あああああ'
--全角のスペースを取り除く場合。
select ltrim(replace('   あああああ',' ',' ')) --'あああああ'

--改行コード、TABを削除--CHAR(13)=line feed(LF),CHAR(10)=carriage return(CR),CHAR(9)=TAB
select REPLACE(REPLACE(REPLACE(N'カラム カラム',CHAR(13), ' '), CHAR(10), ''), CHAR(9), '') --'カラムカラム'

--文字列の一部を切り出す
substring('1234567890',6,4 -- '6789')

日付型の変換など

ひとつ書いたから、ついでにもう少し書いておく。あくまでも自分の備忘用だから、そんなにネタがあるわけではない。

簡単なんだけれども、すぐに忘れてしまってその都度検索してしまうSQLのメモ。
日付形式の変換あれこれ。

--現在日付・時刻を取得
select getdate() --'2016-08-24 00:00:00.000'

--現在日付を取得
select CONVERT(date,getdate()) --'2016-08-24'

--現在日付をyyyy/mm/dd形式で取得
select CONVERT(NVARCHAR,getdate(),111) --'2016/08/24'

--現在日付をyyyymmdd形式で取得
select CONVERT(NVARCHAR,getdate(),112) --'20160824'

--現在月をyyyymm形式で取得
select CONVERT(char(6),getdate(), 112) --'201608'

--前月月初日付を取得
select DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0) --'2016-07-01 00:00:00.000'
select CONVERT(date,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0)) --'2016-07-01'

--当月月初日付を取得
select DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, 0) --'2016-08-01 00:00:00.000'
select CONVERT(date,DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, 0)) --'2016-08-01'

--前月月末日付を取得
select DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) -1, -1) --'2016-07-31 00:00:00.000'
select CONVERT(date,DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, -1)) --'2016-07-31'

--前月月初日付をyyyy/mm/dd形式で取得
select CONVERT(NVARCHAR,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0),111) --'2016/07/01'

--前月月末日付をyyyy/mm/dd形式で取得
select CONVERT(NVARCHAR,DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, -1),111) --'2016/07/31'

縦持ちのデータを横持ちのデータに変換する

聊か唐突ではあるけれども、SQLについてのメモ。

縦持ちのデータを横持ちのデータに変換するSQL。
やり方を知って、案外簡単なことにちょっと感心したので、備忘のために書き付けておく。
何のことだかさっぱり判らないという方は、EXCELの「形式を選択して貼り付け」→「行列を入れ替える」と似たようなものだと思えばイメージを摑みやすいかもしれない。もとより「かもしれない」程度の例えだが。
もっとも、SQLを知らなかったからと言って日常生活に差し支える恐れは一切ないから、イメージが摑めなくても何の問題もないことは言うまでもないし、もし摑めたとしてもどうということもない。

たとえば、こんなデータがあるとする。

[SAMPLE_tbl]TBL
Sales_NoLine_NoSell_Item
00011いちご
00012みかん
00013なし
00014りんご
00021いちご
00022みかん
00023りんご
00024ぶどう
00031なし
00032ぶどう
00033みかん
00034りんご
00041なし
00042りんご
00043もも
00044みかん
00051もも
00052もも
00053もも
00054もも

伝票0001でいちご・みかん・なし・りんごが、伝票0002でいちご・みかん・りんご・ぶどうが…売れたという情報を縦持ちで持っているテーブル([SAMPLE_tbl])である。
これを、伝票番号([Sales_No])ごとの横持ちのデータとして抽出したい。

こんなものである。

Sales_NoSell_Item1Sell_Item2Sell_Item3Sell_Item4
0001いちごみかんなしりんご
0002いちごみかんりんごぶどう
0003なしぶどうみかんりんご
0004なしりんごももみかん
0005もももももももも

下記のようなSQL文で、抽出することができる。

select
 [Sales_No],
 max(case [Line_No] when 1 then [Item] end) as [Sell_Item1],
 max(case [Line_No] when 2 then [Item] end) as [Sell_Item2],
 max(case [Line_No] when 3 then [Item] end) as [Sell_Item3],
 max(case [Line_No] when 4 then [Item] end) as [Sell_Item4]
from
[SAMPLE_tbl]
group by [Sales_No]


「case [Line_No] when 1 then [Item] end~case [Line_No] when 4 then [Item] end」の部分で、「Sales_No = 0001」を例に言うと、

Sales_NoSell_Item1Sell_Item2Sell_Item3Sell_Item4
0001いちごNULLNULLNULL
0001NULLみかんNULLNULL
0001NULLNULLなしNULL
0001NULLNULLNULLりんご

というデータが抽出される。
それを「Sales_No」でgroup化して「Sell_Item1~4」のmaxを取ると、値の入っているものが取られて(NULLが省かれて)上記のようなデータになるわけである。

なるほどな、と思った次第。