官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 164字
  • 2021-06-24 14:38:13

Forced parameterization

If an application tends to generate many ad hoc queries, and there is no way to modify the application to parameterize the queries, the Parameterization database option can be changed to Forced. When Forced Parameterization is turned on, SQL Server will replace ALL literal values in ALL ad hoc queries with parameter markers. Take the example of the following query executed in the AdventureWorks sample database:

SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE PersonType = N'EM' AND BusinessEntityID IN (5, 7, 13, 17, 19);

This query would be automatically parameterized under Forced Parameterization, as follows:

(@1 nchar(2), @2 int, @3 int, @4 int, @5 int, @6 int)  SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE PersonType = @1 AND BusinessEntityID IN (@2, @3, @4, @5, @6);

This has the benefit of increasing the reusability of all ad hoc queries, but there are some risks to parameterizing all literal values in all queries, which will be discussed later in section The importance of parameters.

主站蜘蛛池模板: 芦山县| 安图县| 安阳市| 遂昌县| 汨罗市| 林周县| 乌拉特中旗| 高邑县| 丹阳市| 张家港市| 南陵县| 兴安县| 崇信县| 印江| 新河县| 云林县| 昌黎县| 改则县| 科技| 博野县| 大连市| 瑞丽市| 金门县| 松江区| 翁源县| 桓仁| 海晏县| 长白| 罗城| 五寨县| 洛浦县| 望奎县| 南昌市| 和顺县| 宁陕县| 托克逊县| 罗定市| 赣榆县| 石嘴山市| 横峰县| 南昌县|