This article describes how to recompile a stored procedure in SQL Server by using Transact-SQL. There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called,
SQL Server features statement-level recompilation of procedures. When SQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure. If certain queries in a procedure regularly use atypical or temporary values, procedure performa...
RECOMPILE:表明SQL Server 2019不会保存该存储过程的执行计划,该存储过程每执行一次都要重新编译。在使用非典型值或临时值而不希望覆盖保存在内存中的执行计划时,就可以使用RECOMPILE选项。 ENCRYPTION:表示 SQL Server 2019 加密后的 syscomments表,该表的text字段是包含 CREATE PROCEDURE语句的存储过程文本。使用ENCRYPTI...
触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由个事件来触发,比如当对一个表进行操作(insert,delete,update)时就会激活它执行。 SQL Server 包括三种常规类型的触发器:DML 触发器、DDL 触发器和登录触发器。 DML触发器:当数据库中表中的数据发生变化时,包括insert,update,delet...
1 CREATE PROC [ EDURE ] procedure_name [ ; number ] 2 [ { @parameter data_type } 3 [ VARYING ] [ = default ] [ OUTPUT ] 4 ] [ ,...n ] 5 [ WITH 6 { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 7 [ FOR REPLICATION ] ...
CREATE PROCEDURE [拥有者.]存储过程名[;程序编号] [(参数#1,…参数#1024)] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS 程序行 其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数 参数的使用方法如下: ...
EXECsp_recompile'usp_GetCustomerOrders'; 1. 这样,下次执行usp_GetCustomerOrders存储过程时,SQL Server会重新编译该存储过程并生成新的执行计划。 类图 下面是usp_GetCustomerOrders存储过程的类图示例: StoredProcedure+ Name: string+ Parameters: list+ Query: string+Compile() : void ...
It does this by dropping the existing plan from the procedure cache, forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile....
Execute a stored procedure Describes how to grant permissions on a stored procedure. Grant Permissions on a stored procedure Describes how to return data from a stored procedure to an application. Return data from a stored procedure Describes how to recompile a stored procedure. Recompile...
Learn how a stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a .NET Framework common runtime language method.