とまと あんらいぷ…

エンジニアの活動記録とかつぶやきとか

GitHub

カテゴリ:SQL server の記事一覧

デッドロックの考え方と調査方法指針をまとめてみた

【デッドロック関係の調査の仕方】
SQL Server Management Studio(以下SSMS)の
管理>拡張イベント>セッション>system_health>package0.event_fileを開いて
[name]がxml_deadlock_reportとなっているものがデッドロックの情報。
ここを見て、だいたいのあたりを付ける。

また、以下のコマンドで、ログに出力することができる。実行されているSQLをコピペしたいときは有効にするとよい。
-- SQLServerログにデッドロックのログを出力する
DBCC TRACEON(1204,-1)
-- エラーログにデッドロックのログを出力する
DBCC TRACEON(3605,-1)
-- トレースフラグの設定確認
DBCC TRACESTATUS
SQL Serverログを有効にすると、管理>SQL Serverログ>現在 に、クエリが出力されるようになる。

【調査コマンド・ツール・SQLなど】
以下に、SSMSで叩くコマンドやツールを記載する

■コマンド
sp_who2 =>セッション、およびプロセスに関する情報を提供する。要するにブロッキングを見ることができる。
print @@trancount =>DBのトランザクション数を表示する

■ツール
利用状況モニタ => データベースを右クリック「利用状況モニタ」で起動する。SQLの利用状況が一覧表示される
SQL Server プロファイラ => SSMSのツール>SQL Server プロファイラ より起動 ログインユーザーでフィルタリングして、実行されているSQLを確認するなどに使う。デッドロック検出に関してはDeadlock Graph イベントを使う
  SS2014を使っていればこのイベントはsystem_healthに出力される。
  デッドロックの検出と終了
  https://technet.microsoft.com/ja-jp/library/ms178104(v=sql.105).aspx
設定方法(わかりやすい)
  http://d.hatena.ne.jp/seki-moto/20101117/DeadLock
グラフィカル表示されたサーバー プロセスIDは、「select * from sys.sysprocesses 」のhostname を検索して調べる
    
■SQL
以下のSQLはロック状況を出力する(sys.dm_tran_locksに情報が入ってる)
-----------------------------------------------------------------------
SELECT
resource_type AS type
,resource_associated_entity_id as entity_id
,( CASE WHEN resource_type = 'OBJECT' THEN
OBJECT_NAME( resource_associated_entity_id )
ELSE
( SELECT
OBJECT_NAME( OBJECT_ID )
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id )
END)
AS object_name
,request_mode AS request_mode
,request_type AS request_type
,request_status AS request_status
,request_session_id AS session_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> 'DATABASE'
ORDER BY
request_session_id
-----------------------------------------------------------------------

【トランザクション・デッドロックに係る知識】
この辺りがざっくりまとまって良い
https://blogs.msdn.microsoft.com/jpsql/2012/06/27/609/

変換デッドロック防止のため、DBは、READ_COMMITTED_SNAPSHOT 設定をONにしている
http://higus70.seesaa.net/article/145227121.html

デッドロックが発生した場合、第三者の介入がなければそれを解消することはできない。
SQL Serverでは、Lock Managerというリソースがデッドロックの検出を行い、強制的に片方の要求をロールバックさせる。
デッドロック発生の検出を決める既定時間は5秒。

テーマ:データベース - ジャンル:コンピュータ

DROP TABLE のお作法

いつもDROP TABLE する時にテーブルが存在してエラーとなってしまうので
テンプレを貼っておきます。(更新対策・・・ではないよ 多分)

IF OBJECT_ID('dbo.TEST_DB') IS NOT NULL BEGIN
DROP TABLE TEST_DB
END

ほい。

基本情報処理試験のためのデータベース概念習得

基本情報処理の試験を受けて
SQLに関しては常に100点だったので
私の持っているデータベースへの理解について
とてもファンキーに乱暴にお伝えします。
本職のくせに実務の点数が悪いのはスルーしてくださいね。

基本情報処理試験結果

固定観念を捨て去る


このブログには「SQL 勉強」とかいうキーワードで
飛び込んでくる人が多いようです。
当然のごとくデータベースの概念がふわふわしてる方が多いでしょう。

SQL、クエリの学習方法でおすすめのサイト紹介(SQL攻略 - 実行すれば理解できる!) でも書いてますが
一般的なプログラム言語とはやや毛色が違うため
なかなかとっつきにくいものだと思います。

通常のプログラミングは
命令1行につき、「1処理」を行うように記述するのに対して
データベースの操作は「集合」を操作するように実行します。
(後述のイメージ参照)

文化が違うために、通常のプログラミングを勉強し始めた方は
既に「プログラムとはこういうもの」という固定観念ができあがっているため
混乱するのだと思います。

例えば・・・
データ集合の一部を変更するようなプログラムをイメージしてみましょう。
データの集合として、10.20,30,40,50 というひとくくりのデータのまとまりがあるとします。

一般的なプログラム言語では、複数のデータを処理する場合
プログラムコードで、1行ずつ命令します。

---プログラムコード---
// 10~50の配列
int[] arr = { 10, 20, 30, 40, 50};

// 20と40を0に更新する
arr[1] = 0;
arr[3] = 0;

//結果 arr { 10, 0, 30, 0, 50};
----------------------
イメージ
一般的なプログラミング言語
「データ」を1行ずつ処理する。
20140501_01_database.png

対してデータベースの操作は「ひとまとめ」で処理します。

20140501_02_database.png

この概念の違いを理解しないと、固定観念によって混乱してしまいます。


データベースのイメージを掴もう


データベースといっても、今はxmlデータベースとかリレーショナル・データベースとか
種類はあります。
今回は基本情報処理で出題される
リレーショナルデータベースシステム(RMBS)-関係データベースの概念をまとめてしまいます。

ぶっちゃけた話、データベースはExcel(エクセル)です。
本当は違いますけど、基本情報処理試験に合格するためや、
仕事を始める前のとっかかりとしては
Excelだと思ってよいです。うん。間違いない。

だってさウィキペディアにも
データは表に似た構造で管理され~

って書いてるもん。
表でしょ? Excelなんですよ。
20140501_03_database.png

基本情報処理の勉強をしていると
「選択」「射影」「結合」とかよくわからない言葉がでてきます。
もうね、いちいち小難しい漢字を当てはめちゃった感が出まくりなんですよ。

こんな言葉は試験が終わったら使いません。(使ったこと無い)
ですが、試験で出てくるので仕方なく覚えるしか無いので
覚えるしか無いですね・・・

というわけで、Excelに例えましょう。

・選択:行の選択
SELECT [*](全部の意味) FROM [表の名前]
20140501_04_database.png

・射影:列の選択
列の選択⇒SELECT [列の名前] FROM [表の名前]
20140501_05_database.png

ね、簡単でしょ。

・結合:
結合については、Excelで、別の表をコピペしたりするでしょ?それです。
他の表と、元の表をガッチャンコ。
それが、結合です。何の捻りもありませんね。

結合はコピペ
INNER JOIN
20140501_06_database.png

それぞれデータベース用語に置き換えると

・選択:行の選択⇒SELECT [*](全部の意味) FROM [表の名前]
・射影:列の選択⇒SELECT [列の名前] FROM [表の名前]
・結合:INNER JOIN だったり LEFT JOIN だったり・・・基本情報処理ではLEFTかINNERを覚えておけばOK!

です。
はい、これだけ覚えたら基本情報処理の
データベースに関連する免疫がつきます。

あとは小手先の技、、というか出題が圧倒的に多い
GROUP BY とやらを覚えたら6割ぐらいは取れるんじゃないですかね。多分。
続きを読む

テーマ:プログラミング - ジャンル:コンピュータ

ストアド プロシージャの自動実行と解除方法

SQL Server でストアドの自動化と解除


自作のストアドを、SQL Server サービスの起動と同時に実行する手順
及び、解除方法をを記します。

SQL ServerはWindowsのログオンと同時にサービスの起動が行われるため
データベースの起動に合わせてストアドを実行させるには
データベース起動トリガに自作のストアドをセットしてやれば良い。

利用する関数は
sp_procoption (Transact-SQL)

使い方は以下のとおり
--ストアド プロシージャ自動実行の設定

exec sp_procoption [USER_STORED_PROCEDURE], startup, true;
で、データベース開始時に実行するストアドをセット
[USER_STORED_PROCEDURE]には、実行したいストアド名を指定します。

即時実行したい場合は
exec [USER_STORED_PROCEDURE];
も実行。

念の為に有効になっているかを確認します。
select * from ::fn_trace_getinfo(default)
まっさらな状態だとID2が増えているはず。

ID1はSQLのデフォルトトレースなので気にしなくてOKです。

ストアドの自動起動を解除する時は
exec sp_procoption [USER_STORED_PROCEDURE], startup, false;
で解除。

何でもかんでもアプリケーション実装側のプログラミングで対処するのではなくて、データベースのトリガも念頭においた設計をすることで、開発効率アップや保守性にすぐれたシステムを実装できるかもしれませんね。

気をつけることは、アーキテクチャの機能を手探りで進めすぎると、どこに何を設定したんだか分からなくなるので軌跡は残しましょう。

テーマ:プログラミング - ジャンル:コンピュータ

【SQL Server】ユーザー <user> はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。

遅いだけじゃなかった


以前 【SQL Server】Expressが遅い理由【アタッチで設定が変わる】 なんて記事を書いたけど、
遅いだけじゃなかった。

どうやらSQL Serverが自動終了するタイミングによっては、ログオンできないというエラーが発生するようです。

自動終了オプション(AUTO_CLOSE)がONの時、SQL Server内部では以下のシーケンスで
DBのクローズ処理が行われます。

1.DBのアクセスが無くなる
2.自動終了オプションにより、DBのクローズ処理が発生
3.DBのクローズ完了

この、2のタイミングの時に再びデータベースへのログオンを試みると

エラー 18456 「ユーザー 'XXX' はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。」

が発生します。

Microsoft SQL Server Japan Support Team Blogによると


AUTO_CLOSE オプションが有効になっているデータベースは、通常はクローズされた状態になっています。誰かがそのデータベースにアクセスしようとすると、その時点でデータベースはオープンされます。誰もアクセスしていない状態になると、データベースは自動的にクローズされますが、クローズ処理中はデータベースにはアクセスできません。そのため、あるユーザーがデータベースへのアクセスを終えてデータベースのクローズ処理が実行されている最中に、別のユーザーがそのデータベースを指定してログインしようとすると、その「別のユーザー」は、エラー 18456 「ユーザー 'XXX' はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。」となり、ログインに失敗します。


とのこと・・・


しかもエラーログにはこのエラーメッセージそのままで表示されるため
理由を知らないとSQL実行ログをトレースして追跡調査を行うハメになります。

回避するには自動終了オプション(AUTO_CLOSE) オプションを OFFにすること。

テーマ:プログラミング - ジャンル:コンピュータ

【SQL Server 2008】DBCC SHRINKFILEコマンドで重大なエラーが発生する

.NETから、SQL Serverに圧縮コマンド「DBCC SHRINKFILE(DATABASE_NAME1)」を投げる時の注意点



.NETアプリケーションから、SQL Serverにコマンドを投げてるのだけれど、
SQL Server内で、エラー番号が付与されていないエラーが発生した場合、

クライアント側
つまり.NET側や、SQL Server Management Studioのクエリウィンドウでは、次のようなエラーメッセージが渡されます。
「現在のコマンドで重大なエラーが発生しました。結果は破棄しなければなりません。」

このメッセージ、重大だとかいう割には
意外としょぼい理由だったりします。

今回はDBファイルを圧縮するDBCC SHRINKFILEコマンドのお話です。

まずはSQLのログを見る


SQL Server のインストールフォルダに「Log」っていうフォルダがあり
その中に「ERRORLOG」っていうファイルが存在するので、まずはこのログを見るだけでも
かなり情報を収集できます。

それでも、最初に書いたようにエラー番号がない場合、恐らくエラーログには表示されません。
当エントリであるDBCC SHRINKFILEコマンドがまさにそうでした。

とりあえず、SQL Server Profilerでトレースファイル作成

取り方参照
http://technet.microsoft.com/ja-jp/library/ms175520.aspx

できあがったトレースログを確認します。
重大なエラーが出るパターン

SQL:BatchStarting 54 2013-01-01 14:15:20.000 DBCC
-------- ここからDBCC SHRINKFILE コマンド実行開始
SHRINKFILE(DATABASE_NAME1);  
SQL:StmtStarting 54 2013-01-01 14:15:20.000 DBCC
SHRINKFILE(DATABASE_NAME1);
-------- データベースのデータファイル自動拡張
Data File Auto Grow 54 2013-01-01 16:34:48.383
SQL:BatchCompleted 54 2013-01-01 14:15:20.000 DBCC
-------- DBCC SHRINKFILE コマンド完了がここで終了
SHRINKFILE(DATABASE_NAME1);


重大なエラーが出ないパターン

SQL:BatchStarting 54 2013-01-01 14:15:20.000 DBCC
-------- ここからDBCC SHRINKFILE コマンド実行開始
SHRINKFILE(DATABASE_NAME1);
SQL:BatchCompleted 54 2013-01-01 14:15:20.000 DBCC


ふむ。というわけで
Data File Auto Grow コマンドが投げられているかの違いがあります。

Data File Auto Grow って?


自動拡張ですね。うん。
なんで自動拡張?

SQL ServerのDBCC SHRINKFILEコマンドは、データベース「ファイル内部」で並べ替えを行なう仕様です。

つまり、メモリにデータベースファイルをロードしないんですね。
まぁ、ギガデータとかもあるから当たり前か・・・
で、ファイルが既にとっても小さくて圧縮されてた場合、
その並べ替えのための作業用の領域が必要になることがあって、
その時にファイルにサイズが小さい時に、いちいち拡張するんですね。

結果として、圧縮してるのにでっかくなる。
という状態になり、エラー番号のないエラーが返される。

結果、クライアント側には
「現在のコマンドで重大なエラーが発生しました。結果は破棄しなければなりません。」
っていうエラーメッセージのみが返されるってことでした。

対策は?


とりあえず、Try~Catchで無視しましょう(笑)
ログにもエラーコードも出ないんだから無視するしかないです。

どうしても不安なら、「重大な~」っていうメッセージを見て
無視するとか、それ以外ならthrowするとかすればいいよ。

テーマ:プログラミング - ジャンル:コンピュータ

SQLにおける期間重複チェックの考え方


データベースに登録された一定期間を表すデータに
指定した日付がかぶっているかを確認するSQLを作成する要件は意外と多いです。

実際の業務、小売業のデータマートをサンプルとして考えてみます。
とまとあんらいぷスーパーでは、
商品データを登録する時
商品マスタに
その商品を販売する期間と金額を設定しています。

    商品マスタ
  1. 商品ID
  2. 適用開始日
  3. 適用終了日
  4. 商品名
  5. 金額

商品マスタの金額とは別に、セール(特売)期間テーブルが存在し
セール期間中の金額を設定できます。

    特売期間テーブル
  1. 商品ID
  2. 適用開始日
  3. 適用終了日
  4. 金額

データ登録時の制約として、
商品マスタには同一商品IDの適用開始日が
既に存在する適用開始日~適用終了日の間になるような指定はできません。

【商品マスタ】

商品ID 適用開始日 適用終了日 商品名 金額
登録OK 0001 2013-03-01 2013-08-31 とまとM 258
登録時にエラー 0001 2013-04-01 2013-07-31 とまとM 218

特売期間テーブルに登録したデータは
商品マスタの適用開始日~終了日の期間と重複する場合
特売期間テーブルの金額を優先します。

【特売期間テーブル】

商品ID 適用開始日 適用終了日 金額
商品マスタより
優先される
0001 2013-04-01 2013-07-31 198

さて、特売期間の登録時に、商品マスタのように
特売期間の重複が発生するエラーチェックを行なっていなかった事が発覚しました。

【特売期間テーブル】

商品ID 適用開始日 適用終了日 金額
商品マスタより
優先される
0001 2013-04-01 2013-07-31 198
特売日がかぶってる 0001 2013-04-15 2013-06-31 158


特売期間とはいえ、特売金額が同じ日に複数あるのは問題なので
特売期間テーブルも商品マスタと同様に
同一商品IDによる期間重複制限を行います。

その前に、既に登録している特売期間のうち
特売期間がかぶっているデータの洗い出しを行わなければいけません。

というようなシチュエーションです。
期間重複しているデータを洗い出す方法を考えてみます。
続きを読む

テーマ:プログラミング - ジャンル:コンピュータ

【SQL Server】リモート経由で行なうバックアップに必要な設定まとめ【リモート操作】

SQL Serverをリモート経由でクエリ操作を行ったり
BACKUP DATABASE TO DISK の対象にSQL Serverから見てリモートにあるドライブにバックアップファイルを出力する場合、セキュリティによってアクセスできない事があります。

DBから共有フォルダ等にバックアップを行なうには
以下6つの設定が必要になります。

バックアップファイルを受け取るクライアントマシンAの設定

バックアップ用ユーザーアカウント作成

共有フォルダの有効化とアクセス許可設定

DBサーバー(リモート端末より操作される)の設定

SQL Serviceのログオン権限付与

ファイアウォールの設定(SQL ServerとBrowser)

SQL Serverのリモートログオンを許可する

構成マネージャーでTCP/IPを有効にする


例えばTO DISKにリモートドライブを指定した時に返ってくる例。

実行コマンド


実行結果
バックアップ デバイス '\\pc01\Backup\backup.bat' を開けません。オペレーティング システム エラー 5(アクセスが拒否されました。)。


セキュリティに関する設定が行われていない場合、アクセスが拒否されエラーとなります。

というわけで、SQL Serverに対してリモート操作、及びSQLサーバーからリモートドライブへのアクセスに必要な設
定をまとめました。

この設定は
クライアントマシンAからSQL Serverにバックアップコマンドを発行し、
SQL ServerからクライアントマシンAに対してバックアップファイルを作成することを想定した
構成を元に記載しています。

20130128_バックアップシーケンスPNG

詳細な手順は以下 続きを読む

【SQL Server】Expressが遅い理由【アタッチで設定が変わる】

データベース

システム開発で大容量データを扱う時は
Oracleか、MS SQL Serverが双璧をなしてました。

今、SQL Serverはバージョン2012まで出ていますが

2003の時はひどかったですね。
データ量が1億ぐらいになると、途端に止まってしまう不安定さがありました。
なので、ビッグデータとなりえる場合はOracleを使うってのがスタンダードでした。

最近はMicrosoft SQL Serverも億を超えるデータ量でもそこそこ動くようになってきたようですね。

他にも他ベンダーのデータベースも最適化されてきて
LiteSQLとか、XMLデータベースとか、3次元DBなども登場してきましたが
それでもOracleとSQL Serverも現役です。


ちなみに私はOracleよりSQL Serverが好きです。
データをUPDATEする時の動きがどうも好きになれないんですよね。

OracleとSQL Serverの違いは、クエリの解析方法が違います。 続きを読む

テーマ:日記 - ジャンル:日記

SQL、クエリの学習方法でおすすめのサイト紹介(SQL攻略 - 実行すれば理解できる!)

「SQL勉強しといてー!」
といきなり言われて、なんのこっちゃ分からなかった時代

環境構築も意味が分からず、
プログラムのプの字も知らなかったので途方にくれていた頃
むちゃくちゃ勉強になるサイトを見つけた。

ここでSQLの概念をある程度知り、
そしてクエリを叩けるようになった。

SQL攻略 - 実行すれば理解できる!

ちょっと叩けるようになったら
もう見なくなってしまっていたし、
環境があったから自分の環境で試したりしたもんだから
すっかり見失ってた最高のサイト。

やっとみつけた!

新人学習だとか、環境を用意できない人にとって
このサイトはピカイチにおすすめだと思う。

サイト上でSQL叩けるし、実行結果も見れる。
よくもまあこんなサイトを作ったもんだと感嘆の声も出そうだ。
とにかくお世話になったサイトさんです。

同じような海外もののサイトがこちら。
SQLzoo.net

上記サイトでSQLを叩けば、3日もかからずにSQLのSELECTとは何か的なものがつかめるようになる。
SQLに対する抵抗がなくなるまでが目的なら問題ないんだけど、
業務や仕事で使うとなるともっと複雑な事を知らないとダメになってきます。

JOINなんてものが出てきたときにはパニックになるんだよねぇ。
「LEFT JOIN 覚えておいて!」とか言われて2日間放置された苦い記憶が・・・

あとは、よく使う関数とかのマッピングが頭にないと、SQLを作るだけで数時間・・・なんてこともある。

最初は、SQLポケットリファレンス片手に概念をつけていくのがSQL最短の近道だと思います。


一応、投げっぱなしの先輩よりも役に立つ、手取り足とり解説してくれている本はコチラということで紹介しておきます。

ただ、上の本をクリア後、OracleやSQL Server、MySQLなどといったデータベース固有のアーキテクトに基づいたチューニングをする場合はそれ専門の知識が必要で、
上記本でDBスペシャリストやOracleゴールドが取れるってわけではないのであしからずです。

そもそもデータベースとはなんぞや?という方へは「基本情報処理試験のためのデータベース概念習得」を記載しているので
参考ください。

基本的な考えに基づいたチューニングを行う場合は
SQLを速くするぞ」このサイトさんが勧めです。
本職のDBエンジニアさんがまとめてるようで、実際の現場におけるHOW TOがよくまとめられています。

2014/05/07 追記
※最近のデータベースは賢くなっているので、SQLのコーディングによる速度アップはあまり期待できなくなってきています。

xp_cmdshell を有効にする方法(SQL Server 2005)

■SQL Server 2005 で xp_cmdshell を有効にする

<コマンド>
USE master

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO


<解説>
show advanced options オプション

show advanced options オプションを使用して、
sp_configure システム ストアド プロシージャの拡張オプションを表示できます。
show advanced options を 1 に設定すると、sp_configure を使用して拡張オプションを表示できます。
既定値は 0 です。

この設定は、サーバーを再起動しなくてもすぐに有効になります。

■Shellの実行に関する注意事項

◇注意1
Shellの操作は、DBトランザクションに含めることができない。


◇注意2
Shellのコマンドを実行する際のToken(トークン)について。
→実行ユーザを明示的に指定しない場合、呼び出し元のコンテキストを引き継ぐ。
ASPNETの場合、Webサーバの実行アカウントになる。
(Webサーバの設定しだいで、ASPNETであったり、IUser_[マシン名]であったり、
 指定のユーザアカウントである場合がある)
明示的に指定した場合には、そのユーザトークンで実行される。
いずれにおいても、Shellの実行ユーザには該当の処理を行うための権限が必要になる。

グループ集計で最小(最大)値を持つ行のみ取得するSQL

完全にトラックバックのURL紹介になりますが。
というか、まんまなんですけど、非常に役に立ったので!


MiYaBiS Note.さん

タイトルの通り
集計した結果の最大(最小)値を持つレコードを1行だけ欲しい事って意外と多い。

いろいろクエリをこねくり回してなんとか取得できるんだけど
スマートに且つディ・モールトかっこよく!書きたいじゃないですか。

MiYaBiS Note.さんの引用がもうね。最高っす。
>>引用<<

TableA
TableA

SELECT *
FROM Test A
WHERE NOT EXISTS
( SELECT *
FROM Test
WHERE Type = A.Type
AND Value < A.Value
)

サブクエリで取得出来るデータは、該当するデータとは反対のデータです。
メインクエリの Type 値とサブクエリの Type 値が等しく(グループとしてる)、
サブクエリの Value 値より メインクエリの Value 値が大きいデータとなります。
サブクエリの結果を NOT EXISTS としてるのでその反対、最小値のみとなります。

※最大値の場合は、「Value < A.Value」の「小なり(<)」が「大なり(>)」になります。


>>引用<<

これで、一番でっかいデータが取ってこれるのか。
キモは Type = A.Type でグルーピングできるとこか。


実際にはこんな感じで中で中で倍増して、マッチしなかった結果のみ返ってきてると考えればいいのか。。。

TableA_SUB2


何しろ、すっきりしたクエリがかけていいっす!



SQLServer2005で Oracle のGreatest 関数実装

特に対した事はないけど
同一行内で最大値をとりたい時、

Oracleなら Greatest( Value1, Value2) ってやるだけでいいらしい。
SQLServerってそういうの無いの?

例えば次ぎのようなTESTテーブルがあったとして、
ID | Value1 | Value2 | Value3
1 | 1 | 2 | 3
2 | 2 | 5 | 6
3 | 4 | 1 | 2

結果
Greaetest
3
6
4

みたいな結果を出したい。
Value1と2だけ比べるならこんな感じなんだけど3項目ってなるとちょっと入れ子Caseの書き方がこんがらがったのでメモ書き(笑)

-------2項目比較--------
select id
, case when Value1 < Value2 then Value2
else Value1
end as moreThen
from [Test].[dbo].[Greatest]
 
-------3項目比較--------
SELECT
id,
case when
case when [value1] < [value2] then [value2]
else [value1]
end < [value3] then [value3]
else
case when [value1] < [value2] then [value2]
else [value1]
end
end as Greatest
FROM Sample

結果
1 3
2 6
3 4


成功ってことで^^A

ちなみに参考サイト。
Case式のススメ
殆ど記事の写しみたいになってしまったけど、
インデントのやり方をより自分で理解しやすいように書き直しただけっていう・・・・

DBエンジニアの方が直々に執筆してるサイトで非常に分かりやすい。
DB初心者の私にしてみたらぐっとくる事がいっぱい書いてる。
オヌヌヌ!

BCPを使って~Selectクエリをファイル(csv等)に出力(エクスポート)する。

テーブルデータをエクスポートする場合、
SQL Server Management Studioではタスク>データのエクスポートを選べばよろし。
でも、Expressだとデータのエクスポートが無いのだなぁ。あったらごめんよ。

なので代表的なのはBCPを使ってデータをエクスポートする方法でしょうか?

ここなんか見ると一発で解が出ました。
全ては時の中に~さん

MSにも乗ってるしね。
MS

ただ、注意事項はクエリの制限が約1023byteということらしい。役にたたねえええええ!
BCPでQUERYOUTを使用する場合の文字数制限

でもま、データデータ抜き出すぐらいなら問題ないと思います。

SQL Server上でmaster..xp_cmdshell 使って書いてもいいけど、
xp_cmdshell の実行権限をログインユーザーに付与しないとダメなのでお忘れなく。
(2005からなのかな?)
xp_cmdshell を有効にする方法(SQL Server 2005)


とりあえずサンプルできたのでソースは以下。
◆xp_cmdshell を使う場合、以下を流す。
EXEC master..xp_cmdshell 'bcp.exe "SELECT * FROM Sample.dbo.SampleTable" queryout "C:\sample.txt" -c -S ServerName -U LoginUser -P PassWord

◆batファイルを使ってローカルで実行する場合
bcp.exe "SELECT * FROM Sample.dbo.SampleTable" queryout "C:\sample.txt" -c -S ServerName -U LoginUser -P PassWord
pause

こんな感じで。

bcpには色んなオプションがあるんで、何かと便利だと思う。

BCPを使ってバックアップコマンドなんかも投げれそうです。
【SQL Server】リモート経由で行なうバックアップ操作まとめ【リモート操作】

MSヘルプ
http://msdn.microsoft.com/ja-jp/library/aa174646(SQL.80).aspx

テーマ:雑記 - ジャンル:ブログ

インデックス付きViewの作成

Viewがクソ遅い時にIndexの検討をすればいいんかな。

メリットは多そうやけど、デメリットもあるっぽいので
注意して扱ったほうがいいかもしれん。

以下参考されたし
http://technet.microsoft.com/ja-jp/library/cc917715.aspx

http://handcraft.blogsite.org/ComponentGeek/ShowArticle/122.aspx

http://d.hatena.ne.jp/zecl/20080302/p3

作成時の間違えやすい点とかは以下
・ビューでは、テーブルとユーザ定義関数について、スキマー名.オブジェクト名の2つの要素で構成される名前で参照すること。
・ビュー内の式で参照される関数は決定的であること。
 決定的であるとは、関数の引数に同じ値を渡した場合、
 常に同じ値が返される状態を指す。これに対して、GETDATEやNEWID関数は、
 実行する度に異なる値が返されるので非決定的関数と言える。


サンプルソース

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
GO

-- あったら消す
IF OBJECT_ID('dbo.View_1') IS NOT NULL
DROP VIEW dbo.View_1
GO
IF OBJECT_ID('dbo.TEST_TABLE') IS NOT NULL
DROP VIEW dbo.View_1
GO
IF OBJECT_ID('dbo.TEST_TABLE2') IS NOT NULL
DROP VIEW dbo.View_1
GO

-- ビュー用テーブル1
CREATE TABLE [dbo].[TEST_TABLE](
[PK1] [varchar](50) NOT NULL,
[pk2] [varchar](50) NOT NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL,
CONSTRAINT [PK_TEST_TABLE] PRIMARY KEY CLUSTERED
(
[PK1] ASC,
[pk2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


-- ビュー用テーブル2
CREATE TABLE [dbo].[TEST_TABLE2](
[PK1] [varchar](50) NOT NULL,
[pk2] [varchar](50) NOT NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL,
[col4] [varchar](50) NULL,
[col5] [varchar](50) NULL,
CONSTRAINT [PK_TEST_TABLE2] PRIMARY KEY CLUSTERED
(
[PK1] ASC,
[pk2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



-- VIEW作成
CREATE VIEW dbo.View_1 WITH SCHEMABINDING
AS
SELECT TBL1.PK1 AS TT_PK1
, TBL1.pk2 AS TT_PK2
, TBL1.col1 AS T1_COL1
, TBL1.col2 AS T1_COL2
, TBL1.col3 AS T1_COL3
, TBL2.col1 AS T2_COL1
, TBL2.col2 AS T2_COL2
, TBL2.col3 AS T2_COL3
, TBL2.col4 AS T2_COL4
FROM dbo.TEST_TABLE TBL1
INNER JOIN dbo.TEST_TABLE2 TBL2
ON TBL2.PK1 = TBL1.PK1
AND TBL2.PK2 = TBL2.PK2
GO

-- INDEX作成(クラスタ)
CREATE UNIQUE CLUSTERED INDEX PK_View_1
ON dbo.View_1 ( TT_PK1, TT_PK2 )
GO

-- INDEX作成(ノンクラ)
CREATE NONCLUSTERED INDEX IX_View_1
ON dbo.View_1 ( T1_COL1, T1_COL2, T1_COL3 )
GO

テーマ:ブログ日記 - ジャンル:ブログ

GridViewのヘッダ行に改行を挿入する

GridViewのヘッダ行に改行を挿入したい。いっぱいしたい!
色々ふんばってみた。

しかしググっても文献が出てこないじゃないか!
見つけられないだけなんだろうな。
自分の検索キーワードの乏しさにげんなり。

結局、どこかの国のToddさんだかに助けられた。
ありがとう!Todd !
Toddさんぐっじょぶ!!

掲載元はこちら

RowDataBoundイベント内で、改行コードを置換すればいいみたいだけど。
共通にするにしてもなんだかなぁ・・・・・・




もやもやしていたら、もう一つ方法があった!
Columnsの中のHtmlEncodeプロパティをFalseにして、(デフォルトはTrue)
ヘッダテキスト内に"<br />"を記述すれば改行できた。

ソースコードを記述しない分、デザインだけでできるけど。
表示内容にhtmlタグとして認識可能なもんが入ってればアウトっすね^^A

もっと他に良い方法ないかなー。
もうしばらく調べてあるようだったらまたフィードバックします。


---閲覧いただいた皆様へ---
もし、この記事が役立った場合、お手数ですが拍手ボタンを押していただければ幸いです。


2009.08.03
コメントにも書いたけど、記事の方を更新しました。

テーマ:雑記 - ジャンル:ブログ

FC2Ad