カテゴリー別アーカイブ: SQL

PostgreSQLを再インストールするときのメモ

Windows7にてPostgreSQL9.1の再インストールを行った。
postgresのパスワードを変更するため変更後のパスワードを入力したところ

“The password specified was incorrect. Please enter the correct password for the postgres windows user account.”

というメッセージが表示され、新しいパスワードが受け付けられなかった。
調べたところ、前にインストールした際に作られるpostgresというユーザの情報が残っていることが原因らしい。

以下のコマンドでユーザの削除を行った。

> net user postgres /delete

再びインストーラにてインストールを行うと、変更後のパスワードが受け付けられた。

[SQLServer]convertでdatetime型を文字列型に変換するときによく使うstyle

SQLServerでdatetime型からvarchar型に型変換を行うときに、convert関数を使うが、単純に

convert(varchar,getdate())

とすると出力結果は

2012-07-23 22:13:12

という出力になってまう。

convert関数の構文は以下。(CAST および CONVERT (Transact-SQL)より)

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

第三引数のstyleでフォーマットを指定する。

このstyleの値が数値なのだが、よく使うものをまとめておく。

styleの値

フォーマット

出力例

111

yyyy/MM/dd

2012/07/23

112

yyyyMMdd

20120723

108

hh:mm:ss

15:18:05

以下、使用例。

-- [1] yyyy/MM/dd形式
-- [出力結果] 2012/07/23
select convert(varchar,getdate(),111)

-- [2] yyyyMMdd形式
-- [出力結果] 20120723
select convert(varchar,getdate(),112)

-- [3] hh:mm:dd形式
-- [出力結果] 15:18:05 
select convert(varchar,getdate(),108)

-- [4] (応用)hh:mm形式
-- [出力結果] 15:18
select LEFT(convert(varchar,getdate(),108),5)

[SQLServer]デッドロックを発生させてログを拾ってみる

先日、DBサーバーSQLの処理に時々時間がかかってしまうという問題が発生した。
おそらくデッドロックが原因ではないのだが、「そういえば、デッドロックのログってどうなってんだ?」って話になって、調べてみるとトレースフラグなるものを設定しておかなければ、デッドロックが発生してもログに残らないらしい。

トレースフラグはSQLServer Management Studioのクエリウィンドウから、以下のコマンドを実行すれば良い。

-- SQLServerログにデッドロックのログを出力する
DBCC TRACEON(1204,-1)

-- エラーログにデッドロックのログを出力する
DBCC TRACEON(3605,-1)

-- トレースフラグの設定確認
DBCC TRACESTATUS

-1をはグローバルフラグで、このオプションをつけなければセッションを終了したらトレースフラグはオフになってしまう。

これでログを採る準備は完了。
デッドロックが発生すればログを出力してくれる。はず。。。

気になったので、自分でデッドロックを起こしてみた。
方法としては、一つのテーブルに対して、複数のセッションから1行ずつアップデートをかけ、
デッドロックを発生させる。
まずはテーブルの作成から。簡単なテーブルと、レコードが2件あれば良い。

--テーブルの作成
CREATE TABLE T_TEST
(
  ID INT NOT NULL,
  VALUE CHAR(2) NOT NULL
)
GO

--テストデータを入れる
INSERT INTO T_TEST VALUES
(
  1,
  '11'
)

INSERT INTO T_TEST VALUES
(
  2,
  '22'
)

-- 一意キーをセットする
ALTER TABLE T_TEST
ADD CONSTRAINT PK_T_TEST
PRIMARY KEY(ID)
GO

COMMIT

--データが2レコード表示される
SELECT * FROM T_TEST

そして、SQLServerManagementStudioをもう一つ立ち上げる。
片方のSQLServerManagementStudioをA、もう片方をBとする。
まずはAにて以下のSQLを実行。

BEGIN TRANSACTION

UPDATE T_TEST SET VALUE = '11' WHERE ID = 1

次にBにて以下のSQLを実行

BEGIN TRANSACTION

UPDATE T_TEST SET VALUE = '22' WHERE ID = 2

これでID=1の行はセッションAのロックがかけられ、ID=2にはセッションBのロックがかけらた状態に。

コミットはせず、続けてセッションAにて以下のSQLを実行。

UPDATE T_TEST SET VALUE = '222' WHERE ID = 2

するとID=2のレコードにはセッションBのロックがかかっているため、解放待ちとなる。

ここでセッションBにて以下のSQLを実行する。

UPDATE T_TEST SET VALUE = '111' WHERE ID = 1

このSQLはセッションAの解放待ちとなるため、ここでデッドロックが発生。
数秒後(設定による?)に片方のSQLは実行され、もう片方のトランザクションはロールバックされる。

ログの確認方法は、オブジェクトエクスプローラにて
管理」→「SQL Serverログ」→「現在」で確認することができる。
少し分かりにくいが、Node1、Node2がそれぞれのセッションを表しているようで、それぞれどのSQLを発行した際にデッドロックとなったか、どちらをロールバックしたかを確認することができる。

ちなみに、トレースフラグの解除は以下のコマンドをクエリウィンドウから実行する。

-- SQLServerログにデッドロックのログを出力する
DBCC TRACEOFF(1204,-1)

-- エラーログにデッドロックのログを出力する
DBCC TRACEOFF(3605,-1)

-- トレースフラグの設定確認
DBCC TRACESTATUS

[SQL]SQLを整形する

データベースと連携するプログラムを書いていると、SQL文は文字列として扱うことが多い。
そのためSQLを出力させると、1行の文字列になってしまい非常に読みづらくなってしまう。

自動で整形してくれるツールを探してみると、SQL Convertというソフトがあった。

クリップボードにあるSQL分を自動で整形してくれるというソフトである。
プログラムで生成した結果、長く複雑になってしまったSQL文の分析に使えそうだ。

作者のページ:http://www.geocities.jp/rgyxd366/
Vector:SQLConvert
WEB版:SQLConvert(WEB版)

[SQLServer][SQL] Order by で NULLを最後に持ってくる

仕事でMicrosoftのSQLServerを使っているが、このSQLServer、資格で勉強したORACLEのSQLと細かい部分で違っている。

今日知ったのはOrder by句におけるNULLの扱い。
デフォルトではNULLが先頭にくる。ORACLEにはNULLを最後に持ってくるオプションがあったが、SQLServerのOrder by句にはそのようなオプションは無いらしい。

という訳で、Order by句に以下のCase文を加えるのが手っ取り早いようだ。

    CASE WHEN [列名] IS NULL THEN 0 ELSE 1 END DESC

使うとすれば、こんな感じ。

 SELECT
     *
 FROM
     SYOHIN
 ORDER BY
     CASE WHEN PRICE IS NULL THEN 0 ELSE 1 END DESC
    ,PRICE

こうすれば、PRICEという列の値を昇順でかつ、NULLを最後に並べることが出来る。