MW211 EXIT

devlog
MSSQL／大文字小文字の区別
2019年10月28日 
MSSQLだと、大文字と小文字を基本的に区別しない。
それは、デフォルトだと「Japanese_CI_AS」だからである。

区別にするには「COLLATE Japanese_CS_AS」オプションをつければよい。

まず、各オプション値については、以下の通り。
┌──────────────────────────────────────┐
│「Japanese_CI_AI_KI_WI」～「Japanese_CS_AS_KS_WS」の組み合わせもしくは      │
│「Japanese_BIN」、「Japanese_BIN2」が設定可能                               │
│┌─┬────────────┐┌─┬────────────┬───┐    │
││S │区別する     (Sensitive)││C │大文字と小文字    (Case)│a    A│    │
│├─┼────────────┤├─┼────────────┼───┤    │
││I │区別しない (Insensitive)││A │濁点や半濁点    (Accent)│か  が│    │
│└─┴────────────┘├─┼────────────┼───┤    │
│                                │K │ひらがなとカタカナ(Kana)│あ  ア│    │
│                                ├─┼────────────┼───┤    │
│                                │W │半角と全角       (Width)│a   ａ│    │
│                                └─┴────────────┴───┘    │
└──────────────────────────────────────┘

次に「COLLATE」句の使い方は、以下の通り。

表生成時に定義してしまう場合
┌──────────────────────────────────────┐
│CREATE TABLE [表] (                                                         │
│    [文字列]  [nvarchar](16) COLLATE Japanese_CS_AS NULL,                   │
│);                                                                          │
└──────────────────────────────────────┘

WHERE文にて作用させる場合
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM [TESTDB].[dbo].[T_TEST2]                                           │
│    WHERE [文字列] = 'a' COLLATE Japanese_CS_AS;                            │
└──────────────────────────────────────┘

DISTINCT句にて作用させる場合
┌──────────────────────────────────────┐
│SELECT DISTINCT [文字列] COLLATE Japanese_CS_AS AS [文字列]                 │
│    FROM [TESTDB].[dbo].[T_TEST2]                                           │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／週単位で集計
2019年10月24日 
日付ごとのデータを週単位で集計する場合は、
集計キーとなる列(同一週内で同一の値となるもの)を作り、それでGROUP BYすればよい。
┬──────────────────────────────────────┬
│毎週日曜日を起点する場合                                                    │
┴──────────────────────────────────────┴
【集計キーを起点日からの週目とする方法】
┌──────────────────────────────────────┐
│SELECT [日付],                                                              │
│       DATEDIFF(week, '2019-09-29', [日付]) AS [週目]                       │
│    FROM (                                                                  │
│        VALUES ('2019-10-01'),('2019-10-02'),('2019-10-03'),('2019-10-04'), │
│               ('2019-10-05'),('2019-10-06'),('2019-10-07'),('2019-10-08'), │
│               ('2019-10-09'),('2019-10-10'),('2019-10-11'),('2019-10-12'), │
│               ('2019-10-13'),('2019-10-14'),('2019-10-15'),('2019-10-16'), │
│               ('2019-10-17'),('2019-10-18'),('2019-10-19'),('2019-10-20'), │
│               ('2019-10-21'),('2019-10-22'),('2019-10-23'),('2019-10-24'), │
│               ('2019-10-25'),('2019-10-26'),('2019-10-27'),('2019-10-28'), │
│               ('2019-10-29'),('2019-10-30'),('2019-10-31')                 │
│    ) AS [表]([日付])                                                       │
│    ORDER BY [日付] ASC;                                                    │
└──────────────────────────────────────┘
  ※起点日は日～土曜日のいずれを指定しても同じ結果になる
────────────────────────────────────────
【集計キーを年頭からの週目とする方法】
┌──────────────────────────────────────┐
│SELECT [日付],                                                              │
│       DATEPART(week, [日付]) AS [週目]                                     │
│    FROM (                                                                  │
│        VALUES ('2019-10-01'),('2019-10-02'),('2019-10-03'),('2019-10-04'), │
│               ('2019-10-05'),('2019-10-06'),('2019-10-07'),('2019-10-08'), │
│               ('2019-10-09'),('2019-10-10'),('2019-10-11'),('2019-10-12'), │
│               ('2019-10-13'),('2019-10-14'),('2019-10-15'),('2019-10-16'), │
│               ('2019-10-17'),('2019-10-18'),('2019-10-19'),('2019-10-20'), │
│               ('2019-10-21'),('2019-10-22'),('2019-10-23'),('2019-10-24'), │
│               ('2019-10-25'),('2019-10-26'),('2019-10-27'),('2019-10-28'), │
│               ('2019-10-29'),('2019-10-30'),('2019-10-31')                 │
│    ) AS [表]([日付])                                                       │
│    ORDER BY [日付] ASC;                                                    │
└──────────────────────────────────────┘
  ※年を跨ぐとリセットされるので年末年始の識別が難解
┬──────────────────────────────────────┬
│毎週任意の曜日を起点する場合                                                │
┴──────────────────────────────────────┴
【集計キーを基準日とする方法】(下記例では毎週月曜日を基準日としている)
┌──────────────────────────────────────┐
│SELECT [日付],                                                              │
│       CONVERT(date,                                                        │
│               DATEADD(                                                     │
│                   day,                                                     │
│                   -1 * (                                                   │
│                       DATEPART(weekday,                                    │
│                                DATEADD(day, -1 * 2 + 1, [日付]))           │
│                   ) + 1,                                                   │
│                   [日付])                                                  │
│       ) AS [週集計日]                                                      │
│    FROM (                                                                  │
│        VALUES ('2019-10-01'),('2019-10-02'),('2019-10-03'),('2019-10-04'), │
│               ('2019-10-05'),('2019-10-06'),('2019-10-07'),('2019-10-08'), │
│               ('2019-10-09'),('2019-10-10'),('2019-10-11'),('2019-10-12'), │
│               ('2019-10-13'),('2019-10-14'),('2019-10-15'),('2019-10-16'), │
│               ('2019-10-17'),('2019-10-18'),('2019-10-19'),('2019-10-20'), │
│               ('2019-10-21'),('2019-10-22'),('2019-10-23'),('2019-10-24'), │
│               ('2019-10-25'),('2019-10-26'),('2019-10-27'),('2019-10-28'), │
│               ('2019-10-29'),('2019-10-30'),('2019-10-31')                 │
│    ) AS [表]([日付])                                                       │
│    ORDER BY [日付] ASC;                                                    │
└──────────────────────────────────────┘
────────────────────────────────────────
分類：MSSQL
MSSQL／DBを別名でリストアする方法
2019年10月01日 
基本的に、通常のリストアを同じように進める。(データベースの復元を実行)

その中で、以下の二つを行う。

(1) 「全般」にて、「転送先」の「データベース」の名称を直接入力して変更する
┌──────────────────────────────────────┐
│データベースの復元                                                          │
├──────┬───────────────────────────────┤
│■全般      │ソース────────────────────────────│
│□ファイル  │  ○データベース                                              │
│□オブション│  ●デバイス                                                  │
│            │                ┌─────────────────────┐│
│            │    データベース│旧DB                                      ││
│            │                └─────────────────────┘│
│            │転送先────────────────────────────│
│            │                ┌─────────────────────┐│
│            │    データベース│新DB                                      │★
│            │                └─────────────────────┘│

(2) 「ファイル」にて、
    「すべてのファイルをフォルダーに移動する」にチェックを入れる
┌──────────────────────────────────────┐
│データベースの復元                                                          │
├──────┬───────────────────────────────┤
│□全般      │次のデータベースファイルに復元────────────────│
│■ファイル  │  ■すべてのファイルをフォルダーに移動する                    ★
│□オブション│                                ┌─────────────┐│
│            │    データファイルのフォルダー  │                          ││
│            │                                └─────────────┘│
│            │                                ┌─────────────┐│
│            │    ログファイルのフォルダー    │                          ││
│            │                                └─────────────┘│
│            │┌───────┬ファイルの┬───────┬───────┐│
│            ││論理ファイル名│      種類│元のファイル名│復元先        ││
│            │├───────┼─────┼───────┼───────┤│
│            ││旧DB          │行データ  │…旧DB.mdf    │…新DB.mdf    ││
│            │├───────┼─────┼───────┼───────┤│
│            ││旧DB_log      │ログ      │…旧DB_log.ldf│…新DB_log.ldf││
│            │└───────┴─────┴───────┴───────┘│
この時点では物理ファイル(復元先)は新DBになるが、論理ファイル名は旧DBのまま。

(3) リストアを実行後、以下SQL文を実行し論理ファイル名を変更する
┌──────────────────────────────────────┐
│ALTER DATABASE [新DB] MODIFY FILE (NAME = 旧DB, NEWNAME = 新DB);            │
│ALTER DATABASE [新DB] MODIFY FILE (NAME = 旧DB_log, NEWNAME = 新DB_log);    │
└──────────────────────────────────────┘

これで完璧。
分類：MSSQL
MSSQL／文字列比較の末尾スペース(2)
2019年09月27日 
┌──────────────────────────────────────┐
│SELECT LEN('a ');                                                   →1(≠2)│
└──────────────────────────────────────┘
文字数としても末尾は除去されるのでお手上げな気がする

でも、末尾の空白は置換には反応するという特性がある。
┌──────────────────────────────────────┐
│REPLACE('a ',' ','_')                                               →「a_」│
└──────────────────────────────────────┘

この特性を活かせば、末尾に空白があるか否かを識別できる。
┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表                                                                 │
│    WHERE LEN(REPLACE(列,' ','_')) <> LEN(列);                              │
└──────────────────────────────────────┘
上記では、末尾に空白があるもののみ抽出されるはずだ。
分類：MSSQL
MSSQL／文字列比較の末尾スペース
2019年08月11日 
MSSQLでは文字列比較時に末尾のスペースを無視するのだ。
つまり可変長を固定長と比較しても、中身が同じだったら真になるのである。

ちなみに以下で確認すると「真」になる。
┌──────────────────────────────────────┐
│SELECT CASE                                                                 │
│         WHEN 'a' = 'a ' THEN '真'                                          │
│         ELSE                 '偽'                                          │
│       END AS [テスト];                                                     │
└──────────────────────────────────────┘

文字数としても末尾は除去されている。
┌──────────────────────────────────────┐
│SELECT LEN('a ');                                                   →1(≠2)│
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／プログラムを実行する
2019年05月30日 
┬──────────────────────────────────────┬
│「xp_cmdshell」を実行できるようにする                                       │
┴──────────────────────────────────────┴
  ┌────────────────────────────────────┐
  │EXEC xp_cmdshell …                                                     │
  └────────────────────────────────────┘
  上記のような「EXEC xp_cmdshell」文を実行した場合に以下のエラーが出る
  ┌────────────────────────────────────┐
  │メッセージ 15281、レベル 16、状態 1、プロシージャ xp_cmdshell、行 1     │
  │SQL Server によって、コンポーネント 'xp_cmdshell' の                    │
  │プロシージャ 'sys.xp_cmdshell' に対するアクセスがブロックされました。   │
  │このサーバーのセキュリティ構成で、                                      │
  │このコンポーネントが OFF に設定されているためです。                     │
  │システム管理者は sp_configure を使用して、                              │
  │'xp_cmdshell' の使用を有効にできます。                                  │
  │'xp_cmdshell' を有効にする手順の詳細については、                        │
  │SQL Server オンライン ブックで、'xp_cmdshell' を検索してください。      │
  └────────────────────────────────────┘
  これは既定では実行制限がかかっているからである
┌[注意]───────────────────────────────────┐
│既定で実行制限がかかっているというのは                                      │
│これから先の設定変更を行うとMSSQLからプログラム実行ができるため             │
│ある意味、如何様な悪さでもし放題ということになる                            │
│よって、SQLインジェクション対策には十分注意された上で臨んでいただきたい     │
└──────────────────────────────────────┘
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  そこでその設定変更(緩和)方法
  以下二組のコマンドを続けて実行する
  ┌────────────────────────────────────┐
  │EXEC sp_configure 'show advanced options', 1;                           │
  │RECONFIGURE;                                                            │
  │┌────────────────────────────────────┐
  ││構成オプション 'show advanced options' が 0 から 1 に変更されました。   │
  ││RECONFIGURE ステートメントを実行してインストールしてください。          │
  │└────────────────────────────────────┘
  ├────────────────────────────────────┤
  │EXEC sp_configure 'xp_cmdshell', 1;                                     │
  │RECONFIGURE;                                                            │
  │┌────────────────────────────────────┐
  ││構成オプション 'xp_cmdshell' が 0 から 1 に変更されました。             │
  ││RECONFIGURE ステートメントを実行してインストールしてください。          │
  │└────────────────────────────────────┘
  └────────────────────────────────────┘
  なお、二つ目だけをいきなり実行すると以下のエラーとなるので注意
  ┌────────────────────────────────────┐
  │メッセージ 15123、レベル 16、状態 1、プロシージャ sp_configure、行 62   │
  │構成オプション 'xp_cmdshell' が存在しないか、                           │
  │詳細構成オプションの可能性があります。                                  │
  └────────────────────────────────────┘
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  なお、別案として、以下でも同等なことができる
  ┌────────────────────────────────────┐
  │「Microsoft SQL Server Management Studio」におけるツリー上のサーバの    │
  │右クリックメニュー「ファセット」を選択                                  │
  │┌─────────────────────┐                          │
  ││ファセットの表示                          │                          │
  │├──┬──────────────────┤                          │
  ││全般│          ┌───────────┐│                          │
  ││    │ファセット│サーバーセキュリティー││                          │
  ││    │          └───────────┘│                          │
  ││    │┌───────────┬────┐│                          │
  ││    ││XPCmdShellEnabled     │False   ││→「True」にする          │
  ││    │└───────────┴────┘│                          │
  └────────────────────────────────────┘
  GUIで「XPCmdShellEnabled」を「True」に設定すればよい
┬──────────────────────────────────────┬
│「xp_cmdshell」の実行                                                       │
┴──────────────────────────────────────┴
  通常のコマンドを「EXEC xp_cmdshell」の後に文字列と記述すれば実行すればよい
  例えば以下のような感じ
  ┌────────────────────────────────────┐
  │EXEC xp_cmdshell 'dir'                                                  │
  └────────────────────────────────────┘
  処理結果として、結果が行ごとのレコードとして返却される
  また、以下のように標準出力を変えてあげることもできる(通常のコマンドと同様)
  ┌────────────────────────────────────┐
  │EXEC xp_cmdshell 'dir > D:\work\test.txt'                               │
  └────────────────────────────────────┘
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  なお、正常終了(0)か否かの戻り値が返るので、これを捕捉することもできる
  ┌────────────────────────────────────┐
  │DECLARE @ret int;                                                       │
  │EXEC @ret = xp_cmdshell 'dir'                                           │
  │SELECT @ret;                                                            │
  └────────────────────────────────────┘
────────────────────────────────────────
分類：MSSQL
MSSQL／UPDATE文でRANK()を使う
2019年05月22日 
[群]ごとの連番を[枝番]として振りたい場合に、
UPDATE文に直接RANK()を使うとエラーとなる。
┌──────────────────────────────────────┐
│UPDATE [表]                                                                 │
│    SET [枝番] = (RANK() OVER(PARTITION BY [群]                             │
│                              ORDER BY [連番] ASC));                        │
├──────────────────────────────────────┤
│ウィンドウ関数は、SELECT 句または ORDER BY 句だけで使用できます。           │
└──────────────────────────────────────┘

これを克服する方法。
┌──────────────────────────────────────┐
│UPDATE [表]                                                                 │
│    SET [枝番] = [入力Ｄ].[枝番]                                            │
│    FROM [表] AS [出力Ｄ]                                                   │
│        INNER JOIN (                                                        │
│            SELECT [群],                                                    │
│                   [連番],                                                  │
│                   RANK() OVER(PARTITION BY [群]                            │
│                               ORDER BY [連番] ASC) AS [枝番]               │
│                FROM [表]                                                   │
│        ) AS [入力Ｄ]                                                       │
│          ON  [入力Ｄ].[群]   = [出力Ｄ].[群]                               │
│          AND [入力Ｄ].[連番] = [出力Ｄ].[連番];                            │
└──────────────────────────────────────┘
一旦副表上で[枝番]を生成しておく。
更新対象の[表]とこの副表は、主キーでJOINしないと
副表の先頭一行目(つまり意図しない入力データ)で更新されてしまうので注意。
分類：MSSQL
MSSQL／CTEで主キー条件不要だっけ問題
2019年03月14日 
┌──────────────────────────────────────┐
│WITH [表1]([キー],[バリュー]) AS (                                          │
│         SELECT [キー],                                                     │
│                [バリュー]                                                  │
│             FROM [表0]                                                     │
│     ),                                                                     │
│     [表2]([キー],[バリュー]) AS (                                          │
│         SELECT [キー],                                                     │
│                [バリュー]                                                  │
│             FROM [表1]                                                     │
│     ),                                                                     │
│     [表3]([キー],[バリュー]) AS (                                          │
│         SELECT [キー],                                                     │
│                [バリュー]                                                  │
│             FROM [表2]                                                     │
│     )                                                                      │
└──────────────────────────────────────┘
例えば、CTEをつなげていくと、ふと、「主キーの結合条件っていらなかったっけ」って
不安になることがある
CROSS JOINが繰り返される想定外のレコードができてるんではという不安だ
┌──────────────────────────────────────┐
│     [表2]([キー],[バリュー]) AS (                                          │
│         SELECT [表1].[キー],                                               │
│                [表1].[バリュー]                                            │
│             FROM [表1]                                                     │
│                 INNER JOIN [表0]                                           │
│                   ON [表0].[キー] = [表1].[キー]                           │
│     ),                                                                     │
└──────────────────────────────────────┘
とかしなくていいのかなという不安だ

でも、これは不要
だって、展開すると以下のように自分で自分に結合している訳だから
(「[表0].[キー] = [表1].[キー]」は実質「[表0].[キー] = [表0].[キー]」だ)
こんなものは要らないということになる
┌──────────────────────────────────────┐
│     [表2]([キー],[バリュー]) AS (                                          │
│         SELECT [表1].[キー],                                               │
│                [表1].[バリュー]                                            │
│             FROM (                                                         │
│                 SELECT [キー],                                             │
│                        [バリュー]                                          │
│                     FROM [表0]                                             │
│             ) AS [表1]                                                     │
│                 INNER JOIN [表0]                                           │
│                   ON [表0].[キー] = [表1].[キー]                           │
│     ),                                                                     │
└──────────────────────────────────────┘

ま、あっても悪さしないけど(以下実験例([表0]の内容がそのまま返る(増殖しない)))
┌──────────────────────────────────────┐
│WITH [表1]([キー],[バリュー]) AS (                                          │
│         SELECT [キー],                                                     │
│                [バリュー]                                                  │
│             FROM (                                                         │
│                 VALUES (1, 'A'),                                           │
│                        (2, 'B'),                                           │
│                        (3, 'C')                                            │
│             ) AS [表0]([キー],[バリュー])                                  │
│     ),                                                                     │
│     [表2]([キー],[バリュー]) AS (                                          │
│         SELECT [キー],                                                     │
│                [バリュー]                                                  │
│             FROM [表1]                                                     │
│     ),                                                                     │
│     [表3]([キー],[バリュー]) AS (                                          │
│         SELECT [表2].[キー],                                               │
│                [表2].[バリュー]                                            │
│             FROM [表2]                                                     │
│                 INNER JOIN [表1]                  --本当は不要             │
│                   ON [表1].[キー] = [表2].[キー]  --本当は不要             │
│     )                                                                      │
│SELECT *                                                                    │
│    FROM [表3];                                                             │
└──────────────────────────────────────┘
逆にいうと、気づかなくて厄介といもいえる
分類：MSSQL
MSSQL／接続中のDBを確認する方法
2019年02月26日 
「Microsoft SQL Server Management Studio(SSMS)」で
開いているクエリがどのDB上のものなのかを確認する方法。
すなわち、USE文で接続中のDB名の確認方法。

・SQL文で確認する
  「SELECT DB_NAME();」

・SSMSのタイトルバーで確認する(DB名が表示されている)

・SSMS右上のプルダウンメニューで確認する(DB名が選択されている)
  (プルダウンメニューの値を変更すると(USE実行と同様に)接続先も変更できる)

・SSMS内のクエリウインドウ右下のステータスバーで確認する(DB名が表示されている)
分類：MSSQL
MSSQL／日時型の日付部分
2018年12月22日 
日時型の日付部分だけを抽出する方法。
┌──────────────────────────────────────┐
│[日付] = CONVERT(date, [日時])                                              │
└──────────────────────────────────────┘
分類：MSSQL
前へ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 次へ