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

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

主站蜘蛛池模板: 通化市| 运城市| 肇东市| 克山县| 延寿县| 汤原县| 合肥市| 罗城| 织金县| 万荣县| 梅州市| 个旧市| 商水县| 辽源市| 河间市| 微山县| 四会市| 佛山市| 新源县| 天峻县| 惠州市| 西平县| 清丰县| 万州区| 南通市| 高阳县| 湖南省| 巴彦淖尔市| 筠连县| 沈丘县| 永年县| 乌鲁木齐市| 青海省| 周宁县| 双柏县| 高州市| 毕节市| 德阳市| 金乡县| 巴东县| 雷山县|