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

Stored procedures

A stored procedure is a group of one or more T-SQL statements that is stored as an object in a SQL Server database. Stored procedures are like procedures in other programming languages in that they can accept input parameters and return output parameters, they can contain control-flow logic, such as conditional statements (IF … ELSE), loops (WHILE), and error handling (TRY … CATCH), and they can return a status value to the caller that indicates success or failure. They can even contain calls to other stored procedures. There are many benefits to using stored procedures, but in this section, we will focus mainly on their benefit of reducing the overhead of the compilation process through caching.

The first time a stored procedure is executed, SQL Server compiles and optimizes the T-SQL within the procedure, and the resulting execution plan is cached for future use. Every subsequent call to the procedure reuses the cached plan, until the plan is removed from the cache due to any of the following reasons:

  • Memory pressure
  • Server restart
  • Plan invalidation, that is, when the underlying objects are changed in some way or a significant amount of data is changed

Stored procedures are the preferred method for plan caching as they provide the most effective mechanism of caching and reusing query plans in SQL Server.

主站蜘蛛池模板: 梧州市| 乐平市| 丹巴县| 马公市| 磴口县| 射阳县| 绍兴县| 额敏县| 西乌珠穆沁旗| 轮台县| 白水县| 库尔勒市| 濮阳县| 湘乡市| 平远县| 阿合奇县| 墨竹工卡县| 彰化县| 饶阳县| 罗源县| 香港| 桐柏县| 文化| 瑞金市| 梁山县| 清徐县| 双峰县| 句容市| 叙永县| 紫金县| 五原县| 利川市| 丰顺县| 鹰潭市| 武川县| 昌乐县| 石门县| 石台县| 铁岭县| 合作市| 襄樊市|