Are parameterized queries automatically cached by ODBC?

I understand that query plans for parametrized queries are cached, but at what level? If I drop my connection does that drop the query pla开发者_开发问答n? If I used a different command object does that drop the query plan?

In short, what object do I need to cache in my application (if any) to retain the query plan?

Query plans are cached by the SQL Server itself, and can be recycled across multiple different connections.

In MS SQL Server both the query and the parameter signature are hashed to located cached plans.

This can be important for variable length parameters; if you do not specify the size of, for example, a VARCHAR parameter, it will be chosen for you - based on the actual length of the parameter provided. This means different parameter signatures where the parameters lengths vary. Instead one should specify the length of the VARCHAR parameter, ensuring the same parameter signature, and allowing plan recycling.

Your client code does not need to actively do anything for this to take effect.