>There is something wrong in the execution plan. Are you executing this command from within a stored procedure? If so, add the WITH RECOMPILE option to the execution of the stored procedure
The actual situation is that I was able to simulate that directly from the SSMS environment. This only happens when the values are being parameterized. It only relates to a specific field in the table. When that field is included, I obtain all kind of behaviors. I have found that the only way to make that run is to get rid of the TOP clause. But, I get the impression that I do not have the full speed. I reindexed that field twice tonight. This didn't change anything. That field is also the same size as another field, the one I am using in the first of two fields in that WHERE clause. They are both the same type as well. I checked that data and everything appear to be ok. There are no null values. I have empty values and data entry values.
So, for example, this will run slow:
DECLARE @Altern_ID varchar(20)
set @Altern_ID = '2424019%'
SELECT TOP 101 Client.Name
FROM Client
WHERE Client.Altern_Name LIKE @Altern_ID
ORDER BY Client.Name
But, this will run fast:
SELECT TOP 101 Client.Name
FROM Client
WHERE Client.Altern_Name LIKE '2424019%'
ORDER BY Client.Name