Altering Natively Compiled T-SQL Modules

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

In SQL Server ( SQL Server 2016 (13.x) and later) and Azure SQL Database, you can perform ALTER operations on natively compiled stored procedures and other natively compiled Transact-SQL modules such as scalar UDFs and triggers using the ALTER statement.

When executing ALTER on a natively compiled Transact-SQL module, the module is recompiled using a new definition. While recompilation is in progress, the old version of the module continues to be available for execution. Once compilation completes, module executions are drained, and the new version of the module is installed. When you alter a natively compiled Transact-SQL module, you can modify the following options.

• Parameters
• EXECUTE AS
• TRANSACTION ISOLATION LEVEL
• LANGUAGE
• DATEFIRST
• DATEFORMAT
• DELAYED_DURABILITY

For more information on ALTER PROCEDURE functionality and syntax, see ALTER PROCEDURE (Transact-SQL).

You can execute sp_recompile on a natively compiled Transact-SQL modules, which causes the module to recompile on the next execution.

Example

The following example creates a memory-optimized table (T1), and a natively compiled stored procedure (usp_1) that selects all the T1 columns. Then, usp_1 is altered to remove the EXECUTE AS clause, change the LANGUAGE, and select only one column (C1) from T1.

CREATE TABLE [dbo].[T1] (  
  [c1] [int] NOT NULL,  
  [c2] [float] NOT NULL,  
  CONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED ([c1])  
  ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
CREATE PROCEDURE [dbo].[usp_1]  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH  
(  
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'  
)  
   SELECT c1, c2 FROM dbo.T1  
END  
GO  
  
ALTER PROCEDURE [dbo].[usp_1]  
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS BEGIN ATOMIC WITH  
(  
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Dutch'  
)  
   SELECT c1 FROM dbo.T1  
END  
GO    

See Also

Natively Compiled Stored Procedures