Does a change in a query executed with sp_executesql cause recompilations?

Question

I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure.

Sometimes the query changes (not the parameters, but the query itself). I know this causes SQL Server to invalidate the plan.

If I change this query:

exec sp_executesql 
        N'SELECT nome 
        from customer 
        where name = @name'
    ,N' @name varchar(50)'
    , @name=N'rafael';

to

exec sp_executesql 
        N'SELECT nome, address, phone 
        from customer 
        where name = @name'
    ,N' @name varchar(50)'
    , @name=N'rafael';

The plan will be invalidated.

Does this also cause a re-compilation?

Those queries are not the queries I am running, they are only examples.

EDIT

I am not using #temp tables. Bellow are 2 queries that are being recompiled:

SELECT TOP 1000 X, Y, Z 
FROM MyTable 
WHERE Contains(VarcharMaxField, @Clause) 
    AND XXX IN (SELECT data FROM fn_ArrayTable(@Pub)) 
    AND YYY = 1 AND ZZZ < DateAdd(n, -10, GetDate()) 
ORDER BY A, B, C;

SELECT TOP 1000 A, B, C 
FROM MyTable 
WHERE Contains(VarcharMaxField, @Clause) 
    AND (XXX IN (@Pub0,@Pub1)) 
    AND (AAA= 1) 
    AND (BBB< DateAdd(n, -10, GetDate())) 
ORDER BY A, B C;

I had to change the fields due to an internal company policy. The fn_ArrayTable is a function that returns a comma separated string as a table.

Answer 1

It won't actually cause a recompilation, rather you have a completely new query and so it will have to be compiled for the first time.

If these sp_executesql statements are being called from within a stored procedure there might be some other cause for the recompile (such as a temp table or option).

I would recommend following the steps in http://support.microsoft.com/kb/243586 to troubleshoot exactly what statement is causing your recompilations.