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

  • 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.

主站蜘蛛池模板: 措勤县| 罗源县| 鲁甸县| 平顶山市| 澄城县| 华池县| 榆中县| 日照市| 淮安市| 望江县| 温州市| 延长县| 石首市| 内丘县| 东山县| 尚志市| 辽源市| 海晏县| 蓬溪县| 周至县| 集贤县| 安溪县| 安宁市| 台东市| 丰都县| 措勤县| 湟源县| 固安县| 泰州市| 龙泉市| 尤溪县| 古丈县| 疏附县| 皋兰县| 蓝田县| 阿坝县| 崇文区| 平邑县| 乌兰察布市| 大邑县| 六盘水市|