WHERE句のSUBSTRINGを置き変えたい

質問

sqlserver 2008環境です。

WHERE SUBSTRING(TestCD, 9, 4) <> '0000'

上記のように、20桁の文字列コードをSUBSTRINGしている箇所があります。
インデックスが効かないので、LIKEか何かに置き変えられないかと考えて
いるのですが、うまい方法が思い浮かびません。
良い書き方があれば教えていただけないでしょうか。

回答No.4

haseryuu0378

まだ締まっていなかったので、回答しました。

まず、前の回答者様からもありましたが、質問の内容では、LIKEにしてもインデックスを利用することはできません。
インデックスは列情報を加工した場合には無視されるためです。
ここら辺は、インデックスの仕組みを勉強すると理解できるかと思います。

また、前の回答者様と同様、一番は、設計の見直しですが、それも難しい、、、ということであれば、
計算列を作成し、インデックスを貼るという方法があります。

これであれば、現行のテーブルに列を追加、インデックスの追加だけですので、
既存のシステムへの影響はかなり抑えられます。

今回であれば、

１．CalcKey という計算列を作成（この中には、SUBSTRING(TestCD, 9, 4)の値が格納されます。）
ALTER TABLE CalcKey ADD テーブル名 AS (SUBSTRING(TestCD, 9, 4))

２．CalcKey をキーとしたインデックスを作成する。
CREATE NONCLUSTERED INDEX TEST_INDEX ON テーブル名 (CalcCol)

検索時は、以下のようになります。
WHERE CalcKey <> '0000'

計算列の内容は、自動的に切り替わるため、システムからの更新等は必要ありません。

参考URL：

https://msdn.microsoft.com/ja-jp/library/ms188300(v=sql.120).aspx

回答No.3

chie65536（@chie65535）

＞インデックスが効かないので、LIKEか何かに置き変えられないかと考えているのですが

LIKEに書き換えてもインデックスが効かないのは変わらないと思います。

テーブル構造をを書き換え、TestCDフィールドを

TestCDの１文字目から８文字目
TestCDの９文字目から１２文字目
TestCDの１３文字目以降

の３つのフィールドに書き換えた方が早いです。

TestCDフィールドを３つに分離して、TestCD1、TestCD2、TestCD3と言う３つのフィールドにテーブルを改造します。

改造したら、テーブルを別の名前に変更し、CREATE INDEXなどでインデックスを作成します。

そして「元のテーブルと同じ名前で、クエリを新規作成」して

SELECT ～～～～,TestCD1+TestCD2+TestCD3 AS TestCD,～～～～
FROM 改造したテーブル

というクエリを作ります。

このクエリは「改造前のテーブルと同じ名前になっていて、改造前のテーブルと同じ結果を返す」ので、改造前のテーブルを参照している他のクエリを修正する必要はありません。

で、

SELECT ～～～
FROM 元のテーブル名
WHERE SUBSTRING(TestCD, 9, 4) <> '0000'

とやってるクエリを

SELECT ～～～,TestCD1+TestCD2+TestCD3 AS TestCD,～～～～
FROM 改造後のテーブル名
WHERE TestCD2 <> '0000'

に変更して下さい。インデックスが効いた状態でSELECTする筈です。

という訳で「テーブルの設計そのものに問題があるので、テーブルの設計を見直すべき」だと思います。

回答No.2

maiko0333

create index インデックス名 on テーブル名 ( SUBSTRING(TestCD, 9, 4))

ができませんかね？
実験してないので責任はもてませんが。

回答No.1

IDii24

どんなテーブルか、リレーションかに寄りますがインデックスビューの作成可能範囲であればインデックスビューを作成すればよいだけでは？