リード開発メモ

大阪のソフトウェア会社です。 技術的な事柄についてのメモとしてブログを始めます。

SQL Server

LINQ to SQL で Group by して上位1件を取得する

テーブルAとBがあり、AとBは1対Nになっているとする。
AとBを結合し、Aの項目a_groupごとにグループ化して、Bの項目b_dateのもっとも古いものだけを取り出したいとする。

SQL Server で普通にやる場合、row_number を使った以下のような SQL を思いつく。
select * from (
    select
        row_number() over (partition by A.a_group order by B.b_date, B.b_id) as rn,
        B.*
    from A 
    inner join B
        on A.a_id = B.a_id
) t
where rn = 1

これを LINQ to SQL で書けば以下のようになる。
var query = from dr in db.B
            join cr in db.A
                on B.a_id equals A.a_id
            group dr by new { aGroup = cr.a_group } into gdr
            select gdr.OrderBy(x => x.b_date).ThenBy(x => x.b_id).First();

そして、実際に LINQ to SQL が実行する SQL を見てみると、row_number は使わず outer apply を使っていた。
以下のような SQL になると思うが、たしかに最初の SQL とこちらの SQL は同じ結果となる。なるほど。
ただし、select される項目は違う。
outer apply は outer apply 句の前の select 項目の後ろに、outer apply 句の後の select 項目がくっつく。
select * from (
    select A.a_group
    from A 
    inner join B
        on A.a_id = B.a_id
    group by A.a_group
) t
outer apply (
    select top 1 B.*
    from A 
    inner join B
        on A.a_id = B.a_id
    where t.a_group = A.a_group
    order by B.b_date, B.b_id
) u

以上です。

SQL Server トランザクションログを小さくする

SQL Server のデータベースは、mdf ファイルと ldf ファイルから成っている。ldf ファイルはトランザクションログであり、肥大している場合は、サイズを小さくすることができる。

SQL Server Management Studio (ssms) を使って、トランザクションログのサイズを小さくする手順は以下のとおり。

1. SSMS で、対象となるデータベースで右クリックし、[タスク] - [圧縮] - [ファイル] とたどり、[ファイルの圧縮]ダイアログを開く。
2. [データベース ファイルおよびファイル グループ]の[ファイルの種類]で[ログ]を選ぶ。
3. [圧縮アクション]で[未使用領域の解放前にページを再構成する]を選び、[圧縮先のファイル]に圧縮後の目標サイズを入力する。ただし希望通りになるとは限らない。
4. [OK]ボタンを押す。
sqlserver-log-compress3

これでldf ファイルのサイズは小さくなる。ただし、実行中のトランザクションがあったりすると目標通りのサイズにならない場合もあるようだ。

以上。

SQL Server で CSV ファイルからインポートする

Microsoft SQL Server Management Studio (ssms)を使って CSV ファイルからインポートするのに、手間取ったのでその時のメモ。

インポートする対象となるデータベースで右クリックし、[タスク] - [データのインポート]をクリック。すると「SQL Server インポートおよびエクスポート ウィザード」が起動する。
ssms-csv-import1

[次へ]を押し、[データソースの選択]で[フラット ファイル ソース]を選ぶ。すると、ファイルを選択できるようになるので、[参照]ボタンを押して、データ元となる CSV ファイルを選ぶ。ここで、CSVファイルの1行目が列名の場合は、[先頭データ行を列名として使用する]にチェックを入れておく。
ssms-csv-import2

次に気をつけるポイントは[マッピングの詳細]だが、もしテーブルに IDENTITY 属性を持った項目がある場合は、ここで以下のように [ID 挿入を許可する]にチェックを入れる必要がある。
ssms-csv-import4

また、以下のようなエラーが出るテーブルがあった。
- 実行しています (エラー)
メッセージ
エラー 0xc02020a1: データ フロー タスク 1: データ変換に失敗しました。列 "町域名カナ" のデータ変換から、状態値 4 と状態を示すテキスト "テキストが切り捨てられたか、または対象になるコード ページで 1 つ以上の文字が一致しませんでした。" が返されました。
 (SQL Server インポートおよびエクスポート ウィザード)
 
エラー 0xc020902a: データ フロー タスク 1: "出力列 "町域名カナ" (26)" が切り捨ての発生により失敗しました。"出力列 "町域名カナ" (26)" の切り捨て行の処理により、切り捨てによる失敗が示されます。ここに示されたコンポーネントのオブジェクトで切り捨てエラーが発生しました。
 (SQL Server インポートおよびエクスポート ウィザード)
 
エラー 0xc0202092: データ フロー タスク 1: データ行 0 で、ファイル "C:\work\BB\xxxxxxxx\郵便番号M.rpt" の処理中にエラーが発生しました。
 (SQL Server インポートおよびエクスポート ウィザード)
 
エラー 0xc0047038: データ フロー タスク 1: SSIS エラー コード DTS_E_PRIMEOUTPUTFAILED。コンポーネント "変換元 - 郵便番号M_rpt" (1) の PrimeOutput メソッドからエラー コード 0xC0202092 が返されました。パイプライン エンジンが PrimeOutput() を呼び出したときに、このコンポーネントからエラー コードが返されました。このエラー コードの意味はコンポーネントで定義されていますが、これは致命的なエラーであるため、パイプラインの実行は停止されました。このエラーの前に、エラーの詳細が記載されたエラー メッセージが報告されている可能性があります。
 (SQL Server インポートおよびエクスポート ウィザード)

このケースでは[データソースの選択]のところで[詳細設定]をクリックし、問題となっている列の[OutputColumnWidth] を大きくしておく必要があるようだ。
以上。

SQL Server 名前を変更できないときの対処法

SQL Server Management Studio を使って、SQL Server 2012 Express のデータベース名を変更しようとすると、以下のようなエラーになった。

xxxx の名前を変更できません。 (ObjectExplorer)
追加情報:
→ データベース 'xxxx' のRenameに失敗しました。  (Microsoft.SqlServer.Smo)
 → Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。
   (Microsoft.SqlServer.ConnectionInfo)
  →操作を実行するために、データベースを排他ロックできませんでした。(Microsoft SQL Server、エラー: 5030)

sqlserver-db-rename1

どうやらデータベースの名前を変更するには、シングルユーザーモードにしておく必要があるようだ。シングルユーザーモードには以下の手順で変更する。

1. 変更したいデータベースで右クリックし、[プロパティ]を選ぶ。
2. [データベースのオプション]ダイアログが開くので、[オプション]ページを開く。
3. [状態]の[アクセス制限]を[MULTI_USER]から[SINGLE_USER]に変更する。

sqlserver-db-rename2

4. [OK]ボタンを押してダイアログを閉じようとすると、次のようなメッセージが表示されるので、[はい]を選ぶ。

sqlserver-db-rename3

これで変更したいデータベースで右クリックから[名前の変更]を実行できるようになる。名前を変更したらマルチユーザーモードに戻しておく。

また、MDF、LDF ファイルの名前を変更したい場合は以下の手順を行う。
http://freed411.doorblog.jp/archives/30018676.html

以上。

SQL Server カーソルで処理する

SQL Server でカーソルを使って処理する場合の基本形。
DECLARE @code1       INT
DECLARE @code2       INT
DECLARE @value1      NVARCHAR(10)

DECLARE cur1 CURSOR FOR
SELECT code1, code2, value1
FROM table1
ORDER BY code1, code2

OPEN cur1

WHILE (1 = 1)
BEGIN
    FETCH NEXT FROM cur1
    INTO @code1,  @code2, @value1

    IF (@@FETCH_STATUS <> 0)
        BREAK

    INSERT table2 (
       code1, code2, value1
    ) VALUES (
       @code1, @code2, @value1
    )
END

CLOSE cur1
DEALLOCATE cur1

以上。

アクセスカウンター
  • 今日:
  • 昨日:
  • 累計:

livedoor 天気