<http://msdn.microsoft.com/en-us/library/ms175037%28SQL.90%29.aspx>
describes the PARAMETERIZATION FORCED option for SQL Server 2005
databases.

I don't see how to tell whether PARAMETERIZATION FORCED is in effect
on an existing database. What am I missing?

On the face of it, one way to tell is to prepare and/or execute a
query, maybe on system tables, that is outside the scope of simple
parameterization, and see whether it gets parameterized. "If an
execution plan for a query is cached, you can determine whether the
query was parameterized by referencing the sql column of the
sys.syscacheobjects dynamic management view. If a query is
parameterized, the names and data types of parameters come before the
text of the submitted batch in this column, such as (@1 tinyint). For
information about query plan caching, see Execution Plan Caching and
Reuse."

I haven't checked whether my old trick of including unique text in the
query body in order to find it again, such as "hedgehog" or, more
effectively, a large random number contained in an AS column name,
will be required.

<http://msdn.microsoft.com/en-us/library/ms191275%28SQL.90%29.aspx>
"Specifying Query Parameterization Behavior by Using Plan Guides"
demonstrates a command to generate a parameterized form of a query,
but is there another command whose output depends on a database
PARAMETERIZATION setting?

It also describes how to get your least favourite queries
parameterized, without parameterizing everything.

"Simple parameterization" in practice in SQL Server 2000 appeared to
me to be extremely limited. I wonder if it does more work in SQL
Server 2005. I'll guess not, since it is still reported that
PARAMETERIZATION FORCED can improve a typical server's performance
dramatically (but may have the opposite effect).

Evidently it's better if an application programmer uses queries with
parameters defined appropriately in the first place, but since I don't
own a gun (not yet) I can't enforce that.

I also am curious what happens to a query that involves a database
with PARAMETERIZATION FORCED and another database without.

For that matter, in the example in the plan guides article, the
outcome seems to be for the lines

WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 50

the number 101 becomes a parameter, but the number 50 doesn't?

I wonder what happens then if you create a template that explicitly
treats that term as a parameter?

Robert Carnegie (rja.carnegie (AT) excite (DOT) com) writes:
SELECT name FROM sys.databases WHERE is_parameterization_forced = 1

Actually, I see no reason why you (or your manager) should not accept
that developers are not doing their job. And a developer that is inline
parameters is definitely not fulfilling his duties in my opinion.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jul 13, 11:21*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
I'm much obliged to you for this.

Evidently there is provision for MSDN "Community content" on Microsoft
web pages - if the question was not foolish to start with, do you have
credentials to attach that note to the web page I was looking at? My
employer is probably a very late starter with SQL Server 2005, but I'd
wish to think not the last of all. Oh the humiliation. And I don't
think we have MSDN memberships either.

There are limitations of our development tools, including Java JDBC,
and separately something called Business Objects, each of which takes
actual query-writing out of human hands, but isn't necessarily good at
parameterization. (In which case, why are we using those
technologies? I have no answer.)

And as a former dBase programmer (a "fourth generation language"!)
using SQL at all seemed rather lazy once. Shouldn't the programmer do
their own "table scan" routine, not use a computer to do it
automatically? Where is the creative flair?