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

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.

主站蜘蛛池模板: 松潘县| 邵阳县| 句容市| 福清市| 明溪县| 会东县| 泰来县| 尉氏县| 忻城县| 桃江县| 岳池县| 盐山县| 涿州市| 衡东县| 宝坻区| 湘阴县| 宜君县| 宜城市| 达日县| 滦南县| 虞城县| 额尔古纳市| 庄浪县| 子长县| 玛纳斯县| 琼结县| 曲阳县| 元朗区| 隆德县| 柳州市| 雷州市| 仙桃市| 伊金霍洛旗| 凯里市| 上栗县| 邢台县| 河池市| 平远县| 岫岩| 张掖市| 西宁市|