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

上の写真は、10月に会社のイベントで三島スカイウォークに行った時の写真です。地名である「三島」と、「まるで空を歩いているような感覚を味わえる」という意味が込められた「スカイウォーク」、この2つのキーワードを入れて「三島スカイウォーク」という名前になったそうです。私は初めて行ったのですが、全長400mの日本一長い歩行者専用吊り橋ということで、富士山や駿河湾が一望できるとても素晴らしい景色が楽しめる場所でした。
また、ロングジップスライドという、往復560mのワイヤーを滑車で滑り降りていき、絶景を楽しみながらスリルを味わえるアクティビティもあり、若い社員が挑戦していましたが、この年になると、なかなか勇気が出ないので、若いということは素晴らしいと改めて感じました。また、森の中につくられ、樹から樹へと渡り歩くようなアドベンチャーコースなどもあり、家族で行っても1日楽しめるのではないかと思います。

＜本日の題材＞
TRANSLATE、REPLACE関数（SQL Server）

今回も、今までブログで取り上げていなかった関数について紹介しようと思います。指定した文字を別の文字に置換するTRANSLATE関数とREPLACE関数を取り上げます。

例)
まず、TRANSLATE関数ですが、構文は、
TRANSLATE ( inputString, characters, translations )　
で、inputString という対象文字列に対して、データの中の文字に、characters の文字がどれか含まれていれば、translations の文字でそれぞれの文字を置換するというもので、characters　と translations には同じ文字数の設定が必要となります。
例えば、複数の種類のかっこ（「」【】『』）があり、それらを全て [] に置き換えたいというような場合は、下記のように記載します。

SELECT TRANSLATE(
'「こんにちは」【さようなら】『こんばんは』',
'「」【】『』',
'[][][]'
);

抽出結果は、
[こんにちは][さようなら][こんばんは]
となり、
「 → [ 、 」→ ] 、【 → [ 、 】→ ] 、『 → [ 、 』→ ] というように、それぞれが置換されたことが確認できます。
これを、置換でよく知られている REPLACE 関数を使って同じように処理してみます。REPLACE関数の構文は、REPLACE ( string_expression , string_pattern , string_replacement )　であり、string_expression という対象文字列に対して、string_pattern という検索文字列があれば、string_replacementという文字に置換するというもので、以下のようになります。

SELECT
REPLACE
(
    REPLACE
    (
        REPLACE
        (
            REPLACE
            (
                REPLACE
                (
                    REPLACE
                    (
                        '「こんにちは」【さようなら】『こんばんは』',
                        '「',
                        '['
                     ),
                     '」',
                     ']'
                ),
                '【',
                '['
            ),
            '】'
            ,']'
        ),
        '『',
        '['
    ),
    '』',
    ']'
);

実行してみると

となり、結果は同様ですが、カッコの種類ごとにREPLACE関数で設定しないといけないので、上記のようにSQL文が長くなってしまいます。このような場合には、TRANSLATE関数のほうが簡単に記載できることがわかります。

 
今日は以上まで

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

かなり時間が経ってしまいましたが、上の写真は、今年の春に、普段から交流のある地域の壮年たちと一緒に富士山の近くにいったときの写真です。当日は朝から曇りで、なかなかきれいな富士山が撮れなかったのですが、雲の合間に頂上が見れている写真です。富士5湖の一つ、精進湖（しょうじこ）、精進料理の精進に湖と書いて、しょうじこと言うみたいですが、その湖の傍から撮った写真です。富士5湖というのは、山梨県側の富士山麓に位置する5つの湖の総称で、本栖湖、精進湖、西湖、河口湖、山中湖とあって、2013年には世界文化遺産に登録されています。
富士山が綺麗に見えるパノラマ台という場所に1時間半かけて登って、ガスコンロと水、やかんを用意していたので、そこで湯を沸かして、皆でカップヌードルを食べました。とてもおいしかったです。その後、山を下りて、「富士眺望の湯ゆらり」という近くの温泉に行ったときには晴れて、露天風呂から富士山の頂上が真ん前にきれいに見えました。温泉につかりながら、いつまでも富士山を見ていたいという感じでした。やはりときには自然の中にどっぷりと浸かる時間が必要ですね。

＜本日の題材＞
IIF、CHOOSE（SQL Server）

以前、CASE文を取り上げたことがありましたが、CASE式の簡略版と言えるIIF関数と、後に続くリストに対するインデックスを設定できるCHOOSE関数について紹介したいと思います。

例)
生徒の試験結果について、点数によって成績を振り分ける例を考えてみます。まず、生徒用のテーブルと、試験結果用のテーブルをDBに作成してみます。

CREATE TABLE dbo.Student(
学籍番号  NVARCHAR(8),
氏名      NVARCHAR(20),
性別      NVARCHAR(1),
生年月日  DATE,
CONSTRAINT PK_Student PRIMARY KEY(学籍番号));

INSERT INTO dbo.Student VALUES('20220001', '生徒A', '男', '2006-05-12');
INSERT INTO dbo.Student VALUES('20220002', '生徒B', '女', '2007-02-21');
INSERT INTO dbo.Student VALUES('20220003', '生徒C', '女', '2006-11-07');
INSERT INTO dbo.Student VALUES('20220004', '生徒D', '男', '2006-07-18');
INSERT INTO dbo.Student VALUES('20220005', '生徒E', '男', '2006-12-25');

CREATE TABLE dbo.Test_result(
学籍番号  NVARCHAR(10),
試験日    DATE,
点数      INT,
CONSTRAINT PK_Test_result PRIMARY KEY(学籍番号,試験日));

INSERT INTO dbo.Test_result VALUES('20220001', '2022-05-18', 92);
INSERT INTO dbo.Test_result VALUES('20220002', '2022-05-18', 48);
INSERT INTO dbo.Test_result VALUES('20220003', '2022-05-18', 69);
INSERT INTO dbo.Test_result VALUES('20220004', '2022-05-18', 28);
INSERT INTO dbo.Test_result VALUES('20220005', '2022-05-18', 82);

ここで、試験の点数によって、成績をS,A,B,C,D に分ける抽出を行ってみます。CASE文を使って抽出すると、下記のようにSQL文を書けます。

SELECT
学籍番号,
試験日,
点数,
CASE WHEN 点数 > 90 THEN 'S'
     WHEN 点数 > 80 THEN 'A'
     WHEN 点数 > 60 THEN 'B'
     WHEN 点数 > 30 THEN 'C'
     ELSE 'D' END AS 成績
FROM dbo.Test_result
ORDER BY 学籍番号;

これを、IIF関数を使うと、以下のようにも書けます。

SELECT
学籍番号,
試験日,
点数,
IIF(点数 > 90, 'S', IIF(点数 > 80, 'A', IIF(点数 > 60, 'B', IIF(点数 > 30, 'C', 'D')))) AS 成績
FROM dbo.Test_result
ORDER BY 学籍番号;

IIF関数の構文は、IIF( boolean_expression, true_value, false_value ) ということで、boolean_expression が true に評価された場合に、true value の値を返し、false に評価された場合に、flase value の値を返します。IIF関数は、最大10の入れ子ができるということなので、上記の例では入れ子の設定にしてみましたが、CASE文と同様の抽出ができました。

次に、CHOOSE関数ですが、構文は、CHOOSE ( index, val_1, val_2 [, val_n ] ) であり、後続の val_1 以降のリストから、index 引数の順番の値を返すものになります。
例えば、CHOOSE(3, 'A', 'B', 'C', 'D') であれば、’C’ が返ってきます。

先ほどの Studentテーブルの生徒について、生年月日によって、’春’,’夏’,’秋’,’冬’ のどの季節に生まれたのかを抽出してみたいと思います。

SELECT 学籍番号, 氏名, 生年月日,
CHOOSE (DATEPART(MM, 生年月日), '冬','冬','春','春','春','夏','夏','夏','秋','秋','秋','冬') AS 誕生季節
FROM dbo.Student
ORDER BY 学籍番号;

誕生月によって、季節をCHOOSE関数のリストで設定したので、それに合わせて各生徒の誕生日の季節が抽出されることを確認できました。

今日は以上まで

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

少し前に、娘と一緒に、横浜港の大桟橋国際客船ターミナルから乗船するレストラン船「ロイヤルウイング」に乗って、食事をしながらクルージングを楽しむ機会がありました。大桟橋国際客船ターミナルを出航し、ベイブリッジをくぐり東京湾へ出て、その後再びベイブリッジを通過し横浜港をゆっくり周遊するというもので、90分ほどの内容でしたが、初めてのこともあり、楽しいひと時を過ごすことができました。少し天候がよくなかったというのが残念でしたが、ピアノの生演奏を聴けたり、食事後は外のデッキに出て、海を眺めて過ごすことができます。上の写真は、乗船する前に撮った写真です。
時間も余裕もなかなかないですが、何日間かかけてのクルージングの旅行とか、できたら楽しいでしょうね！

＜本日の題材＞
STRING_SPLIT（SQL Server）

SQL Serverで便利な関数を見つけたので、今回ブログで取り上げてみたいと思います。これは、何らかの区切り文字を含んだ状態でデータが存在しているときに、そのデータを区切り文字で分割して、複数行として値を返すことができます。

例)
衣料品に関する「製品情報」テーブルに「サイズ」「色」という項目があり、それらに、区切り文字を含んで該当するサイズや色が登録されているとします。そのときに、区切り文字を含む項目について、区切り文字で分割して、データを複数行で表示したいときに使用できます。

最初に「製品情報」テーブルを作成して、データを登録します。

CREATE TABLE dbo.製品情報(
製品ID INT,
製品名 NVARCHAR(30),
サイズ NVARCHAR(30),
色     NVARCHAR(30),
CONSTRAINT PK_製品情報 PRIMARY KEY (製品ID));

INSERT INTO dbo.製品情報 VALUES(1, 'Ｔシャツ_001', 'S,M,L,XL,XXL', '赤/青/黄/橙/緑/黒');
INSERT INTO dbo.製品情報 VALUES(2, 'Ｔシャツ_002', 'S,M,L', '青/桃/黄');
INSERT INTO dbo.製品情報 VALUES(3, 'Ｙシャツ_001', 'S,M,L,XL', '白/灰');
INSERT INTO dbo.製品情報 VALUES(4, 'Ｙシャツ_002', 'S,M,L,XL,XXL', '白');
INSERT INTO dbo.製品情報 VALUES(5, 'ポロシャツ_001', 'S,M,L', '赤/青/黒/白');

上記のようにデータを登録した製品情報テーブルについて、データを抽出すると以下になります。

SELECT * FROM [dbo].[製品情報]
ORDER BY 製品ID;

これについて、製品毎、サイズ毎に行を分けて抽出したい場合に、以下のようにすると簡単に抽出が可能となります。
サイズの場合の区切り文字「,」を CROSS APPLY STRING_SPLITの後に、項目名とともに設定します。

SELECT 製品ID, 製品名, value AS サイズ
FROM dbo.製品情報
CROSS APPLY STRING_SPLIT(サイズ, ',');

同様に、製品毎、色毎に行を分けて抽出したい場合には、区切り文字「/」を CROSS APPLY STRING_SPLITの後に、項目名とともに設定します。

SELECT 製品ID, 製品名, value AS 色
FROM dbo.製品情報
CROSS APPLY STRING_SPLIT(色, '/');

また、色に「青」「白」をともにデータとして持つレコードを抽出したい場合に、下記のようなSQLで抽出が可能です。

SELECT 製品ID, 製品名, サイズ, 色 
FROM dbo.製品情報
WHERE '青' IN (SELECT value FROM STRING_SPLIT(色, '/'))
  AND '白' IN (SELECT value FROM STRING_SPLIT(色, '/'));  

同様に、サイズで「XL」「XXL」をともにデータとして持つレコードを抽出したい場合、以下のようにできます。

SELECT 製品ID, 製品名, サイズ, 色 
FROM dbo.製品情報
WHERE 'XL'  IN (SELECT value FROM STRING_SPLIT(サイズ, ','))
  AND 'XXL' IN (SELECT value FROM STRING_SPLIT(サイズ, ',')); 

さらに、色毎の製品の件数を、多い順に抽出してみます。

SELECT value AS 色, COUNT(*) AS 色毎の製品件数
FROM dbo.製品情報 
CROSS APPLY STRING_SPLIT(色, '/') 
GROUP BY value
ORDER BY COUNT(*) DESC;

色の種類ごとの、製品の件数が多い順に表示されることが確認されました。

今日は以上まで

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コーディネータのシュウです。

写真は、群馬県の榛名山の写真です。群馬県の高崎にいる知人が写真を送ってくれました。
群馬県には、赤城山、榛名山、妙義山の上毛三山と言われる有名な山があり、榛名山は中央に位置する火山であり、複数の山で構成される山体の名称です。山頂には、カルデラ湖である榛名湖と榛名富士があり、それらを複数の山が囲む形になっています。火山としては、5世紀から6世紀の頃に、大規模な噴火が度々発生していたとの記録があります。
以前は関越自動車道に乗ることがよくあって、榛名山、赤城山を始めたくさんの山々の自然を見ながらの走行はとても気分がいいものですね。榛名山の側には伊香保温泉もあるということなので、いつか行けたらいいと思います。

＜本日の題材＞
FIRST_VALUE / LAST_VALUE（SQL Server）

かなり以前のブログで、ORACLEでの FIRST / LAST関数について取り上げましたが、SQL Serverにおいても同様の機能があるのを見つけましたので、試してみたいと思います。なお、SQL Serverについては、OVER句についても取り上げたことがありますが、今回の FIRST_VALUE / LAST_VALUE関数は、OVER句と一緒に使用するかたちになります。

例)
以前ブログで取り上げたときと同様に、商品マスタに登録されたデータについて、分類ごとの値段が最も高いものと低いものを、商品名などの情報とともに出力するということをしてみたいと思います。

CREATE TABLE syomst(
 syo_cd   NVARCHAR(10)
, syo_name NVARCHAR(20)
, bnrui    NVARCHAR(20)
, price    NUMERIC(10)
, CONSTRAINT PK_syomst PRIMARY KEY (syo_cd));

データを登録します。

INSERT INTO syomst VALUES('A0001', 'チョコレート', 'お菓子', 120);
INSERT INTO syomst VALUES('B0001', 'りんご', '果物', 100);
INSERT INTO syomst VALUES('C0001', 'キャベツ', '野菜', 160);
INSERT INTO syomst VALUES('A0002', 'ビスケット', 'お菓子', 200);
INSERT INTO syomst VALUES('B0002', '桃', '果物', 160);
INSERT INTO syomst VALUES('C0002', 'にんじん', '野菜', 150);
INSERT INTO syomst VALUES('A0003', 'ガム', 'お菓子', 100);
INSERT INTO syomst VALUES('B0003', 'みかん', '果物', 80);
INSERT INTO syomst VALUES('C0003', 'じゃがいも', '野菜', 100);
INSERT INTO syomst VALUES('A0004', 'スナック', 'お菓子', 140);
INSERT INTO syomst VALUES('B0004', '梨', '果物', 120);
INSERT INTO syomst VALUES('C0004', '玉ねぎ', '野菜', 150);

それでは、商品マスタのデータを抽出するとともに、FIRST_VALUE、LAST_VALUEを使用して、同じ商品の分類の値段が最も低いものと高いものの商品名も右側に並べて抽出してみます。

SELECT bnrui, syo_cd, syo_name, price,
FIRST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC 
 ROWS UNBOUNDED PRECEDING
 ) AS 分類で最も安い商品名,
LAST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC
 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 ) AS 分類で最も高い商品名
FROM dbo.syomst
ORDER BY bnrui, price, syo_cd;

商品マスタの各項目の値が左側に、その右に、分類で最も安い価格の商品の名前と、分類で最も高い価格の商品の名前を正しく抽出することができました。

さて、FIRST_VALUE、LAST_VALUE を使う場合の構文は、以下になります。

FIRST_VALUE / LAST_VALUE ( [scalar_expression ] ) 
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [partition_by_clause] order_by_clause [rows_range_clause] )

上記のパラメータについては、
scalar_expression：
返される値：今回は、商品名称を表示したいので、syo_name。

IGNORE NULLS - パーティションの最初の値の計算時に、データセット内の null 値を無視します。
RESPECT NULLS - パーティションの最初の値の計算時に、データセット内の null 値を使用します。
（今回は商品名称はNULLデータがなく関係がないので指定してはいません）

partition_by_clause は、指定した項目でパーティションに分割して、パーティション毎の結果を出すときに使用しますが、今回は、分類(bunrui)毎に抽出するため、設定しています。
order_by_clause は、今回は価格(price)の最も安い価格と最も高い価格を出すために使用します。

rows_range_clause は始点と終点を指定することによって、パーティション内の行をさらに条件付けることが可能になります。
今回の例について説明すると、FIRST_VALUE句のほうで設定している「ROWS UNBOUNDED PRECEDING」は、分類毎の最初の行から開始することを指定しています。
LAST_VALUE句のほうの「RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING」は、現在の行の値から最後の行の値までということを表わしています。

注意点として、LAST_VALUEのときに、上記の範囲の設定を省略すると、結果が異なってきます。
試してみます。

SELECT bnrui, syo_cd, syo_name, price,
FIRST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC 
 ) AS 分類で最も安い商品名,
LAST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC
 ) AS 分類で最も高い商品名
FROM dbo.syomst
ORDER BY bnrui, price, syo_cd;

分類で最も安い商品名のほうは問題ないですが、分類で最も高い商品名の結果が、前回の結果と異なり、分類毎の最も高い価格の商品名ではなく、抽出した行の商品名が抽出されていることがわかります。これは、rows_range_clause のデフォルトの設定が、「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」ということで、分類毎の最初の行の値から現在の行の値までということになり、分類毎、price順にソートしているため、LAST_VALUEとしては、結局、現在の行の値になってしまっているということです。

そのため、LAST_VALUEを使用する場合には、ROWSやRANGEで、UNBOUNDED FOLLOWINGまでというように、範囲を適切に指定するようにしたほうがよいようですね。

今日は以上まで

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

この写真は、住んでいるところの近くにある、関東最古の大社の一つと言われている「鷲宮神社」の鳥居が再建されている様子です。以前、ブログで人気まんが作品『らき☆すた』の舞台となった神社ということで取り上げ、この神社の鳥居の写真を載せたことがありましたが、2018年8月11日に、それまで建っていた鳥居が老朽化のために倒壊してしまいました。倒壊した鳥居は、100年以上前に建てられた木製のものでしたが、老朽化で、根本のほうがボロボロだったようです。それ以降、解体されて何もない状態でしたが、今回鳥居が再建されることになり、12月3日の祭典終了後から、通れるようになるということです。鳥居がなくて寂しい思いがしていましたが、立派な鳥居が再建されて、とても嬉しい気がします。

＜本日の題材＞
ネイティブコンパイルストアド プロシージャ（SQL Server）

前回のブログで、In-Memory OLTP 機能のメモリ最適化テーブル変数というものを取り上げましたが、今回はそれに関連する内容として、ネイティブコンパイルストアドプロシージャについて、試してみたいと思います。これは、メモリ最適化テーブルでの利用を想定したストアドプロシージャーです。
通常のSQL文によるクエリは、実行時にコンパイルしマシン語に変換したのちに実行されますが、ネイティブコンパイルストアドプロシージャーでは、マシン語に変換した状態のストアドプロシージャーをメモリに読み込ませておくため、従来のクエリよりも素早く実行できるのが特徴です。
ネイティブコンパイルとは、プログラミングの構造をネイティブコードに変換する処理であり、追加のコンパイルまたは解釈を必要としないプロセッサ命令で構成されると説明されています。
https://docs.microsoft.com/ja-jp/sql/relational-databases/in-memory-oltp/native-compilation-of-tables-and-stored-procedures?view=sql-server-ver15

例)
今回は、前回のメモリ最適化テーブル変数を使った処理とほぼ同様の内容を、ネイティブコンパイルストアドプロシージャで実行するようにしてみたいと思います。

ネイティブコンパイルストアドプロシージャの基本的な構文は以下となります。

CREATE PROCEDURE ストアドプロシージャ名
パラメーター定義
WITH
   NATIVE_COMPILATION,
   SCHEMABINDING
AS
BEGIN ATOMIC
WITH (
   TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'japanese')
     -- 実行したいステートメント
END

ネイティブコンパイルストアドプロシージャを作成するには、CREATE PROCEDURE 文で「WITH NATIVE_COMPILATION」、「SCHEMABINDING」を指定する必要があります。「BEGIN ATOMIC」と「END」で行いたい処理を囲むかたちになり、「TRANSACTION ISOLATION LEVEL」でトランザクションの分離レベルを指定します。LANGUAGE は、日付フォーマットやシステムメッセージをどの言語にするかを指定するもので、「japanese」とすることで、日本語の日付フォーマット、およびメッセージを表示できるようになります。

それでは、前回のブログの内容に合わせたネイティブコンパイルストアドプロシージャを作成します。まず最初に、今回は、変数ではなく、メモリ最適化テーブルを作成します。

CREATE TABLE dbo.mem_test_tab(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,CONSTRAINT PK_mem_test_tab PRIMARY KEY
NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

ここで、プライマリーキーのところでハッシュインデックスを設定し、さらに、WITHの後に「MEMOERY_OPTIMIZES=ON」と、「DURABILITY」を設定します。「DURABILITY」には、「SCHEMA_AND_DATA」と「SCHEMA_ONLY」が指定できます。SCHEMA_AND_DATA は、テーブルに持続性があり、変更がディスクに保存され、再起動またはフェールオーバー後も存続することを示しますが、SCHEMA_ONLY　は、テーブルに持続性がないことを示します。

前回は、変数でしたので、一連の処理の中でデータを変数にセットしましたが、今回は、先ほど作成したメモリ最適化テーブルに、最初に50万件のデータを登録しておきます。

DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO dbo.mem_test_tab(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST((1+ABS(CHECKSUM(NewId())) % 9)*10 AS NVARCHAR), ABS(CHECKSUM(NewId())) % 100)
END

データを確認してみます。
SELECT * FROM dbo.mem_test_tab
ORDER BY id;

それでは、ネイティブコンパイルストアドプロシージャを作成します。

CREATE PROCEDURE dbo.USP_inmemory_test
  @区分1 nvarchar(2),
  @区分2 nvarchar(2),
  @区分3 nvarchar(2),
  @区分4 nvarchar(2),
  @区分5 nvarchar(2)
WITH
   NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'japanese')
 
-- メモリ最適化テーブル変数に格納した結果を集計
      SELECT 区分, COUNT(*) 件数
         FROM dbo.mem_test_tab
      WHERE 区分 = @区分1
                 OR 区分 = @区分2
                 OR 区分 = @区分3
                 OR 区分 = @区分4
                 OR 区分 = @区分5
          GROUP BY 区分
          ORDER BY 区分;
END
GO

ネイティブコンパイルストアドプロシージャを実行します。

DECLARE @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
     
EXEC dbo.USP_inmemory_test @区分1, @区分2, @区分3, @区分4, @区分5

こちらも、ほとんど時間はかからずに結果が表示されました。
今回は、ネイティブコンパイルストアドプロシージャを作成して、動作することを確認しました。

今日は以上まで

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

この写真は、前回のブログで写真を載せた、大根島にある「由志園」に行ったときに、ついでに訪れた、島根半島の先のほうにある「美保神社」で撮ったものです。私も高校生の時に一度兄に連れて行ってもらったことがあったというかすかな記憶がありましたが、それ以来なので、ほとんど初めてという感じでした。この神社の祭神は、えびす様（事代主神）と大国主神の后の三穂津姫で、商売繁盛、海上安全、五穀豊穣、夫婦和合などの守護神として祀られています。実家が、毎年、正月には美保神社のお札を送ってもらうようにしているということを、道すがら初めて聞きました。父親も数十年ぶりに参ることができたと喜んでいたので、少し遠回りにはなりましたが、行って良かったと思います。

＜本日の題材＞
メモリ最適化テーブル変数（SQL Server）

SQL Server2014から実装されたIn-Memory OLTP 機能に、メモリ最適化テーブルというものがあります。今まであまり試したことがなかったのですが、今回は、そのメモリ最適化テーブル変数というものを試してみたいと思います。

例)
テスト用テーブルを作成し、テストデータを50万件作成します。そこから検索したいデータを抽出し、テスト用テーブルの列定義と同じ設定のメモリ最適化テーブル変数に登録して、結果を抽出してみます。

まず、メモリ最適化機能を使用するために、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作る必要があります。今回は、既存のデータベース「BLOG」に追加します。

ALTER DATABASE BLOG ADD FILEGROUP BLOG_mod CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE BLOG ADD FILE (name='BLOG_mod1', filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\iBLOG_mod1') TO FILEGROUP BLOG_mod;

次に、データを登録する「 mem_test」テーブルを作成します。

CREATE TABLE mem_test(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,Constraint PK_mem_teste Primary key(id));

このテーブルにテストデータを作成します。今回は、50万件を以下のように作成します。「id」項目には、1～500000までシーケンシャルに値を設定し、名前は「顧客名_」の後にidを頭0埋めで設定、区分は2桁で、10,20,30, ～90までをランダムに、ポイントは、1～100の整数をランダムに登録してみます。ランダムに値を設定するのは、RAND関数と、NewID関数を使用してみます。

--データの作成
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO mem_test(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR), 1+ABS(CHECKSUM(NewId())) % 100)
END

ランダム値についてですが、RAND関数は、0～1の間の数が生成されるので、指定範囲内の整数値乱数が必要な場合は、
SELECT FLOOR([FROM値] + (RAND() * ([TO値] - [FROM値] + 1)))
のようにすれば取得できます。今回は、10,20,..,90 という文字型の値にしたかったので、CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR) としました。
また、NEWID関数は、uniqueidentifier データ型の値を返してきますが、データを数値にしてチェックするCHECKSUM関数と、負の数が返ってくる場合もあるため絶対値を取得するABS関数を使い、さらに求める値の範囲を考慮して、今回は1～100までの値のため、100で割った余りを使うかたちで、1+ABS(CHECKSUM(NewId())) % 100　としました。

データを確認してみます。
SELECT * FROM mem_test
ORDER BY id;

区分とポイントはランダムに値が登録されているのが確認できました。
それでは、メモリ最適化テーブル変数を使用するために、そのためのユーザー定義テーブル型を宣言します。通常と違うのは、「MEMORY_OPTIMIZED = ON」を付けることと、ハッシュインデックスを付与する点が異なります。

CREATE TYPE mem_test_type AS TABLE
(id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,INDEX idx1 NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON );

※HASHインデックスでは、BUCKET_COUNT（バケット数）を適切な値へ設定していないと、性能低下の原因に繋がるようです。（特に小さすぎると性能が大きくさがるとのこと）

ちなみに、最初に行った、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作成していなかった場合には、上記のタイプを作成しようとしたときに、「メモリ最適化テーブル を作成できません。メモリ最適化テーブル を作成するには、オンライン状態かつ 1 つ以上のコンテナーがある MEMORY_OPTIMIZED_FILEGROUP がデータベースに含まれている必要があります。」というエラーが出ます。

それでは、上記を使ってメモリ最適化テーブル変数を宣言し、指定した5つの区分についてのデータ件数を確認してみたいと思います。

-- メモリ最適化テーブル変数の宣言
DECLARE @retValue dbo.mem_test_type

-- メモリ最適化テーブルへデータの INSERT
DECLARE
       @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
 
   INSERT INTO @retValue
   SELECT id, 名前, 区分, ポイント
   FROM dbo.mem_test
   WHERE 区分 = @区分1
              OR 区分 = @区分2
              OR 区分 = @区分3
              OR 区分 = @区分4
              OR 区分 = @区分5
 
-- メモリ最適化テーブル変数に格納した結果を集計
SELECT 区分, COUNT(*) 件数
FROM @retValue
GROUP BY 区分
ORDER BY 区分;

ほとんど時間はかからずに結果が抽出されてきました。
メモリ最適化テーブル変数を、一時テーブルのようなかたちで使用できることが確認できましたが、実際には、もっと適した使用法があるのではないかと思います。今回は、取りあえず使えることを確認してみました。

今日は以上まで