Odd issue with LIKE operator and variable

[schismaticus]

Hoping someone can shed some light on this one, using MS SQL 2000,

I have a query like this;

Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data  T where T.TransactionRecordClass like '10000002%'

which will return around 10000 records in under 1 second.
My issue is that I need to make this part of a function, but as soon as I add parameters/variables ( see below ) , it goes from returning in under 1 second, to 11 minutes!!!

DECLARE @TRC_ID nvarchar (9)
SET @TRC_ID = rtrim('10000002') + '%'
 
Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data  T where T.TransactionRecordClass like @TRC_ID

There is a non-clustered index on the 2 fields being returned , and about 77 million records in the table. I have attempted using Index hints, but found they are not allowed on remote queries..... So... any clues why this happens, and how to fix????

Thanks in advance for any help

[schismaticus]

OK, found a solution...

The field type of T.TransactionRecordClass is "char", my variable is declared "nvarchar", but if I declare the variable as "char" then the query time is back to < 1 second

Hoping someone can shed some light on this one, using MS SQL 2000,

I have a query like this;

Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data  T where T.TransactionRecordClass like '10000002%'

which will return around 10000 records in under 1 second.
My issue is that I need to make this part of a function, but as soon as I add parameters/variables ( see below ) , it goes from returning in under 1 second, to 11 minutes!!!

DECLARE @TRC_ID nvarchar (9)
SET @TRC_ID = rtrim('10000002') + '%'
 
Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data  T where T.TransactionRecordClass like @TRC_ID

There is a non-clustered index on the 2 fields being returned , and about 77 million records in the table. I have attempted using Index hints, but found they are not allowed on remote queries..... So... any clues why this happens, and how to fix????

Thanks in advance for any help

[debasisdas]

You never forget anything that you learn by intrest.