Return to Answer

According to this article:

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

• Adhoc query caching
• Autoparameterization
• Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
• Stored procedures or other compiled objects (triggers, TVFs, etc.)

In other words, both of your examples should fit SQL Server's criteria for query caching, since one is an ad-hoc query that will be textually the same each time it is run, and the other is a prepared query using sp_executesql.

In addition, it appears that either one would appear in a stored procedure, which itself should have a query execution plan cached. The sp_executesql approach may be slightly slower because it's doing more work to set up the query, but it's possible that SQL Server actually precompiles the call into something that resembles the second query.

As in all cases of optimization, the only way to be sure is to test both approaches against your expected data set.

The second (ad-hoc) query is much more readable, in my opinion. All else being equal, I'd go with that.

According to this article:

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

• Adhoc query caching
• Autoparameterization
• Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
• Stored procedures or other compiled objects (triggers, TVFs, etc.)

In other words, both of your examples should fit SQL Server's criteria for query caching, since one is an ad-hoc query that will be textually the same each time it is run, and the other is a prepared query using sp_executesql.

In addition, it appears that either one would appear in a stored procedure, which itself should have a query execution plan cached. The sp_executesql approach may be slightly slower because it's doing more work to set up the query, but it's possible that SQL Server actually precompiles the call into something that resembles the second query.

As in all cases of optimization, the only way to be sure is to test both approaches against your expected data set.

The second (ad-hoc) query is much more readable, in my opinion. All else being equal, I'd go with that.

According to this article:

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

• Adhoc query caching
• Autoparameterization
• Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
• Stored procedures or other compiled objects (triggers, TVFs, etc.)

According to this article:

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

• Adhoc query caching
• Autoparameterization
• Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
• Stored procedures or other compiled objects (triggers, TVFs, etc.)

In other words, both of your examples should fit SQL Server's criteria for query caching, since one is an ad-hoc query that will be textually the same each time it is run, and the other is a prepared query using sp_executesql.

In addition, it appears that either one would appear in a stored procedure, which itself should have a query execution plan cached. The sp_executesql approach may be slightly slower because it's doing more work to set up the query, but it's possible that SQL Server actually precompiles the call into something that resembles the second query.

As in all cases of optimization, the only way to be sure is to test both approaches against your expected data set.

The second (ad-hoc) query is much more readable, in my opinion. All else being equal, I'd go with that.