Doubts on Indexing.

Hi,

set @.DN=''

set @.DN=@.DN+'AA9999'

select @.ID = ID from TableA where status='A' and ColumnA like '%'+@.DN

ColumnA is NonClusteredIndex

When I use = symbol it goes for Index Seek (like below query) otherwise its going for table scan. Could anyone advice me which index need to be used for getting 'Index Seek' when I use like Operator. Basically TableA has millions of records.

select @.ID = ID from TableA where status='A' and Code = 'AA9999'

Regards

JMR

I think it is the normal behavior because SQL have to compare all ColumnA values with a pattern and have to go row to row; in the other select SQL look at an unique values.

So, it is recommended to avoid like operator in WHERE clause.

|||

If you use LIKE + '%xxxxx' , there is no option; It forced to use Scan. Bcs we don't know where start. When the pattern will match.

If you use LIKE + 'xxxx%', it might use Index Scan / Seek depend with your data density.

bcs the LIke 'xxxx%' will be converted as col >= 'xxxx' and col < 'xxxxY';