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

Simple parameterization

In order to minimize the impact of ad hoc queries, SQL Server will automatically parameterize some simple queries by default. This is called Simple parameterization and is the default setting of the parameterization database option. With parameterization set to Simple, SQL Server will automatically replace literal values in an ad hoc query with parameter markers in order to make the resulting query plan reusable. This works for some queries, but there is a very small class of queries that can be parameterized this way.

As an example, the query we introduced previously in the Parameterization section would not be automatically parameterized in simple mode because it is considered unsafe. This is because different PersonType values may yield a different number of rows, and thus require a different execution plan. However, the following query executed in the AdventureWorks sample database would qualify for simple automatic parameterization:

SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE BusinessEntityID = 5;

This query would not be cached as is. SQL Server would convert the literal value of 5 to a parameter marker, and it would look something like this in the cache:

(@1 tinyint) SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE BusinessEntityID = @1;
主站蜘蛛池模板: 昆山市| 固原市| 渝中区| 庐江县| 拜城县| 安顺市| 白朗县| 丰都县| 桑日县| 白水县| 井冈山市| 璧山县| 修水县| 长阳| 阳信县| 鲁甸县| 北票市| 乌什县| 东城区| 云霄县| 无极县| 叙永县| 抚顺县| 明星| 日照市| 涡阳县| 修武县| 淅川县| 乌拉特前旗| 射洪县| 抚顺县| 盘锦市| 资中县| 颍上县| 仪陇县| 威海市| 玉屏| 桦南县| 寿阳县| 临澧县| 永仁县|