If you’ve ever created a schema bound UDF, you’ll know that schema binding it is just a matter of adding WITH SCHEMABINDING to your definition. The same applies when you create a schema bound view.

Stored procedures are a bit different.

Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound. You can’t create a natively compiled stored procedure without schema binding it.

But if you try to schema bind a regular (non-natively compiled) procedure, you’ll get an error.

Example of a Schema Bound Stored Procedure

Here’s an example of creating a schema bound (natively compiled) stored procedure.

CREATE PROCEDURE dbo.usp_GetCowsByName @cowname varchar(70)
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
    LANGUAGE = N'us_english'
    )  
SELECT 
    CowId,
    CowName,
    Phone
FROM dbo.Cows
WHERE CowName = @cowname
END;

GO

I have included the WITH SCHEMABINDING argument, but I have also included NATIVE_COMPILATION, which indicates that the procedure is natively compiled.

Also notice the ATOMIC WITH block. This is required for natively compiled stored procedures.

This code created a schema bound stored procedure.

Error? Check these Prerequisites

Natively compiled stored procedures require that any underlying tables are memory-optimized tables.

To create memory-optimized tables, you must first create a memory-optimized filegroup.

Here’s the code I used to create the memory-optimized filegroup, its associated file, and the memory-optimized table that I reference in the stored procedure:

ALTER DATABASE Test
    ADD FILEGROUP imoltp_test CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE Test 
    ADD FILE (name='imoltp_test1', filename='/var/opt/mssql/data/imoltp_test1.mdf')
    TO FILEGROUP imoltp_test;
GO

CREATE TABLE [dbo].[Cows](
	[CowId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
	[CowName] [varchar](70) NULL,
	[Phone] [varchar](10) NULL
) 
    WITH
        (MEMORY_OPTIMIZED = ON,
        DURABILITY = SCHEMA_AND_DATA)
GO

So if you want to copy and paste my code, you’ll need to run this first, then the stored procedure code above.

Also note that I’m using SQL Server for Linux, and so the file paths use Linux conventions. If you’re creating a database file on Windows, you’ll need to change it to use the Windows file path convention (and be sure to use a file path that exists on your system).