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

[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] 数値を0埋めの文字列で表す

SQLServerにはOracleのLPADやRPADのような、文字を埋め込む関数がないらしい。
そこで、指定した文字数を右、もしくは左から抜き出す関数があるのでそれを活用する。
例えば数値型項目numを0001の用に長さ4で左を0埋めした文字列で表すときには

RIGHT('0000' + convert(varchar,num), 4)

とする。

また、文字列型項目strの右を空白で埋め、長さ10となるようにするときには

LEFT(str + '          ', 10)

とする。

[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

[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を最後に並べることが出来る。