Hi,

I just made a simple test... I build a dynamic SQL to execute and executed the same query directly in SMSS...

Can anyone explain why the dynamic SQL is 46% of total time and the same query but "fixed" is 54%, as shown in the attachment..?!?!

CREATE NONCLUSTERED INDEX [idx_Employees_003] ON [dbo].[Employees]

(

[empname] ASC,

[salary] ASC

);

This is the index being used...

If I use the "fixed" SQL but "guessing" the parameters, like this:

SELECT empid, empname, salary FROM dbo.Employees WHERE empname >= CASE WHEN LEN(@FilterName) > 0 THEN @FilterName ELSE empname END AND salary >= ISNULL(@FilterSalary, 0)

SQL suggests an index on salary including empid and empname..

If instead of empname >= ... I use empname LIKE ..., on the dynamic SQL, it also suggests the index on salary and include empid and empname... Isn't LIKE as good as >= for index seeks?!?

Thanks,

Pedro