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';

double unique index

Hello.

I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example:

Row 1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 2

this shouldn't be possible.

Row1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 3

this should be possible

is there any way I can do this?

thanks in advance

You can create a UNIQUE constraint or a unique INDEX.

ALTER TABLE YourTable ADD CONSTRAINT UK_YourTable_ColumnA_ColumnB UNIQUE (ColumnA,ColumnB)

Or

CREATE UNIQUE INDEX IX_YourTable_ColumnA_ColumnB ON YourTable (ColumnA,ColumnB)

The constraint or index can also be clustered if you do not have a clustered PK/index/constraint yet.