我认为他说的是，当您使用Prepared Statements时，SQL Server可以缓存您的查询执行计划，因此，即使您修改了一些参数在正在执行的查询上，服务器可能会选择错误的(可能是已缓存的)执行计划，而执行计划会非常糟糕.

他还提到了SQL Server 2008的一项新功能，该功能迫使引擎重新评估他用来克服这种情况的执行计划.

对于准备好的语句，我唯一的问题就是这个.考虑以下Java代码:

String sql = "select * from table where name like ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "PATTERN%");
ResultSet rs = pstmt.executeQuery();

在这里，您希望，如果表(名称)上有索引，则查询计划将使用该索引.好吧，不会.因为PraparedStatement必须预编译并预料到最坏的情况，例如'％PATTERN％'.因此，它不会优化.我花了一些时间才弄清楚这一点.这导致我的数据库受苦. :(

希望有帮助.

If I remember correctly, I think Jeff has mentioned in the Stack Overflow podcast a possible weakness in SQL prepared statements. I'm wondering what kind(s) of weakness(es) did he refer to? Was it possibly just about inappropriate usage thereof, or something more sinister?

The podcast, to my remembering, didn't go deeper into the subject, it was just a pass-by-remark.

I think what he said was that, when you use Prepared Statements, SQL server could cache your query execution plan, so, even if you modify some of the parameters on the executing query, the server could pick the wrong (probably cached) execution plan that would perform very badly.

He also mentioned a new feature of SQL Server 2008 to force the engine to re-evaluate execution plans that he used to overcome this situation.

With Prepared Statements, the only issue I have is this. Consider the following Java Code:

String sql = "select * from table where name like ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "PATTERN%");
ResultSet rs = pstmt.executeQuery();

Here you would expect that, if you have an index on table(name) it will be used by the query plan. Well, it won't. Because PraparedStatement must precompile and expect the worst: '%PATTERN%', for example. So, it won't optimize. It took me a while to figure this one out. It was causing my database to suffer. :(

Hope it helps.