Forced parameterization does not work for partly parameterized queries.

In my previous blog I described how you can recognize a forced parameterized query. If you have set the parameterization option to forced on database level, you can still find some queries which are not parameterized. In this blog I will describe why?

Please use the AdventureWorks database to use the scripts.

USE AdventureWorks


--Enable the forced parameterization on the database
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
GO
-- Clear the procedure cache
DBCC FREEPROCCACHE
GO
-- Update 2 different records both with an different Title.
UPDATE HumanResources.Employee SET Title = 'Support' WHERE ContactID = 1002
GO
UPDATE HumanResources.Employee SET Title = 'xx1290Support' WHERE ContactID = 1290
GO
-- Look in the procedure cache.

SELECT text,execution_count
FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text
-- You will find 1 entry Title and ContactID are parameterized.


-- Clear procedure cache
DBCC FREEPROCCACHE
Same query but ContactId is parameterized. Title is not parameterized.
GO
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = ''Support'' WHERE ContactID = @P1' ,N'@P1 INT',@P1 = 1002
GO
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = ''xx1290Support'' WHERE ContactID = @P1' ,N'@P1 INT',@P1 = 1290
GO
-- Look in the procedure cache
SELECT text,execution_count
FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text
-- You will find 2 entries. Forced parameterization does not work.
-- Clear procedure cache
DBCC FREEPROCCACHE

-- Same query ContactID and Title are parameterized.
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = @P2 WHERE ContactID = @P1' ,N'@P1 INT, @P2 varchar(20)',@P1 = 1002, @P2 = 'Support'
GO
exec sp_executeSql N'UPDATE HumanResources.Employee SET Title = @P2 WHERE ContactID = @P1' ,N'@P1 INT, @P2 varchar(20)',@P1 = 1290, @P2 = 'xx1290Support'
GO

-- Look in the procedure cache
SELECT text,execution_count
FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text
-- You will find 1 entry because the query is already parameterized.

Conclusion: Forced parameterization does only work if the query does not contain any parameter. If at least one parameter is defined, forced parametization does not work anymore.

Enjoy the use of forced parameterization in the right context.