[SQL Server] 注意事項！
Convertで日付を文字列（フォーマット）に変更してする時にインデックス(index)注意。
Convertで日付のフォーマット変更（交換）および時刻のスタイル変換については下記のリンクをご参考ください。
今回はそのCONVERTで日付のフォーマット変更（交換）した時によく間違えるINDEX問題について説明したいと思います。
また今回はテストの為に日付データが入っている「dbo.tComDay」を利用します。
「dbo.tComDay」の作成については下記のSQLを参考してテーブルを作成及びデータを登録してください。
-- #### テーブル作成
CREATE TABLE dbo.tComDay
(
[ComDay] [char] (10) PRIMARY KEY NOT NULL,
[Idx] [int] NOT NULL
) ON [PRIMARY]
-- #### データ登録（実行時間は約30秒所要）
DECLARE @i int
SET @i = 0
WHILE @i <= 50000
BEGIN
INSERT INTO dbo.tComDay
SELECT CONVERT(CHAR(10), DATEADD(DD, @i, '2000-01-01'), 111), @i+1
SET @i = @i + 1
END
では、「dbo.tComDay」まで準備ができましたか。
「dbo.tComDay」テーブルの「ComDay」カラムデータ型はchar(10)で「YYYY/MM/DD」型のデータが入っています。
では、「@Today」変数を作成して「20140101」文字列のデータをdbo.tComdayテーブルから検索してみます。
また実行する前に「Ctrl+M」もしくはメニューバーから「クエリ > 実際の実行プランを含む」を有効化します。
そうすると実行結果と一緒に実行プランが表示されます。
では、下記のSQLを実行してみましょう。
DECLARE @Today char(8)
SET @Today = '20140101'
-- 1 : @Today変数をCONVERTで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE ComDay = CONVERT(CHAR(10), CONVERT(datetime, @Today),111)
-- 2 : 「ComDay」カラムをReplaceで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE REPLACE(ComDay, '/', '') = @Today
-- 3 : 「ComDay」カラムをCONVERTで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE CONVERT(CHAR(8), CONVERT(datetime, ComDay), 112) = @Today
まず、「実行結果」タブから見ましょう。
同じ結果が得られました～
実行結果が同じだからどれでもいいじゃない？？？
そう？？
では、「実行プラン」タブから見ましょう。
「Index Seek」と「Index Scan」があります。
簡単に説明すると。。。。
「Index Seek」：INDEX(インデックス)を利用する。
「Index Scan」：INDEX(インデックス)を利用しない。
という意味です。
詳しく各SQLについて説明すると。
1 : @Today変数をCONVERTで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE ComDay = CONVERT(CHAR(10), CONVERT(datetime, @Today),111)
このクエリは@Today変数をCONVERTして日付フォーマットに変更後再度「YYYY/MM/DD」形式（フォーマット）の文字列に変更してから
「ComDay」カラムからデータを検索しているのでIndexを利用しますので、検索速度が速いです。
2 : 「ComDay」カラムをReplaceで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE REPLACE(ComDay, '/', '') = @Today
このクエリは「ComDay」カラムの「YYYY/MM/DD」形式データを「YYYYMMDD」形式にREPLACEで変更してから
「@Today」変数「20140101」データと同じデータを検索しています。
では、ここで！！！
ComDayカラムのデータが「@Today」変数「20140101」と同じデータを検索するためには
全データを「YYYYMMDD」形式（フォーマット）に変更しないと「@Today」変数データと同じデータかを分からないので、
インデックスの利用はできずテーブルの全データを検索するFULL SCANをすることになります。
ですので、検索速度が遅くなります。
3 : 「ComDay」カラムをCONVERTで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE CONVERT(CHAR(8), CONVERT(datetime, ComDay), 112) = @Today
このクエリも2番のクエリと同じく「ComDay」カラムのデータを変更しているので、
当然INDEXを利用しません。もちろん速度も遅いです！！！
INDEXを利用するSQLを作成する時に注意するところは！！
カラムのデータを変更してはいけない！！ということです。
REPLACE, CONVERT, LEFT, RIGHT, SUBSTRING, TRIMなどなどを使ってカラムのデータを変更すると
INDEXを利用しなくなるのでご注意ください！！
では、「メッセージ」タブも見ましょう。
1 : @Today変数をCONVERTで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE ComDay = CONVERT(CHAR(10), CONVERT(datetime, @Today),111)
上記のクエリ実行についてメッセージ内容を見てください。
テーブル 'tComDay'。スキャン回数 0、論理読み取り数 2、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
SQL Server 実行時間:
、CPU 時間 = 0 ミリ秒、経過時間 = 1 ミリ秒。
ここで注目する部分は「スキャン回数」、「論理読み取り数」、「CPU 時間」、「経過時間」です。
-.スキャン回数 : 0
-.論理読み取り数 : 2
-.CPU 時間 : 0 ミリ秒
-.経過時間 : 1 ミリ秒
2 : 「ComDay」カラムをReplaceで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE REPLACE(ComDay, '/', '') = @Today
上記のクエリ実行についてメッセージ内容を見てください。
テーブル 'tComDay'。スキャン回数 1、論理読み取り数 145、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
SQL Server 実行時間:
、CPU 時間 = 125 ミリ秒、経過時間 = 130 ミリ秒。
-.スキャン回数 : 1
-.論理読み取り数 : 145
-.CPU 時間 : 125 ミリ秒
-.経過時間 : 130 ミリ秒
3 : 「ComDay」カラムをCONVERTで交換した場合
SELECT ComDay FROM dbo.tComDay WHERE CONVERT(CHAR(8), CONVERT(datetime, ComDay), 112) = @Today
上記のクエリ実行についてメッセージ内容を見てください。
テーブル 'tComDay'。スキャン回数 1、論理読み取り数 145、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
SQL Server 実行時間:
、CPU 時間 = 47 ミリ秒、経過時間 = 45 ミリ秒。
-.スキャン回数 : 1
-.論理読み取り数 : 145
-.CPU 時間 : 47 ミリ秒
-.経過時間 : 45 ミリ秒
Indexを利用する1番とIndex利用しない2，3番の実行メッセージを比べてみてください。
論理読み取り数も1番に比べ、2，3番が結構読み取っています。
CPU 時間、経過時間も差があります。
これは作業環境によって結果が違う場合がありますが、
データが多ければ多いほど2，3番のようなクエリはどんどん遅くなりますので、
SQLを作成する時はご注意ください。