How to Use the WITH SCHEMABINDING Option for Functions in SQL Server: A Detailed Guide

Creating a function WITH SCHEMABINDING option:

1. The function fn_GetEmployeeNameById(), is dependent on Employees table. 
2. Delete the table  Employees from the database. 

   
   Drop Table Employees 
   

3. Now, execute the function fn_GetEmployeeNameById(), you will get an error stating 'Invalid object name Employees'. So, we are able to delete the table, while the function is still refrencing it.
4. Now, recreate the table and insert data, using the scripts provided.
5. Next, Alter the function fn_GetEmployeeNameById(), to use WITH SCHEMABINDING option.

We have a Employee table like below. We will use this table to understand this concept with a practical example

Id	Name	DateOfBirth	Gender	DepartmentId
1	Rambo	1980-12-30 00:00:00.000	Male	1
2	Roma	1982-09-01 12:02:36.260	Female	2
3	Inza	1985-08-22 12:03:30.370	Male	1
4	Sara	1979-11-29 12:59:30.670	Female	3
5	Azam	1978-11-29 12:59:30.670	Male	1

Code: Without SchemaBinding

Alter Function fn_GetEmployeeNameById(@Id int)
Returns nvarchar(20) 
as
Begin
 Return (Select Name from  Employees Where Id = @Id)
End

Without SchemaBinding we can drop table Employees.

Code:

Alter Function fn_GetEmployeeNameById(@Id int)
Returns nvarchar(20)
With SchemaBinding
as
Begin
 Return (Select Name from dbo.Employees Where Id = @Id)
End

With SchemaBinding we can't drop table Employees.

Note: You have to use the 2 part object name i.e, dbo.Employees, to use WITH SCHEMABINDING option. dbo is the schema name or owner name, Employees is the table name.
6. Now, try to drop the table using - Drop Table Employees. You will get a message stating, 'Cannot DROP TABLE Employees because it is being referenced by object fn_GetEmployeeNameById.'

So, Schemabinding, specifies that the function is bound to the database objects that it references. When SCHEMABINDING is specified, the base objects cannot be modified in any way that would affect the function definition. The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

Prerequisite Code to create table

CREATE TABLE Employees
(
	Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Name NVARCHAR(50),
	DateOfBirth DATETIME,
	Gender NVARCHAR(50),
	DepartmentId INT
);

INSERT INTO Employees
VALUES ('Rambo', '1980-12-30', 'Male', 1)

INSERT INTO Employees
VALUES ('Roma', '1982-09-01 12:02:36.260', 'Female', 2)

INSERT INTO Employees
VALUES ('Inza', '1985-08-22 12:03:30.370', 'Male', 1)

INSERT INTO Employees
VALUES ('Sara', '1979-11-29 12:59:30.670', 'Female', 3)

INSERT INTO Employees
VALUES ('Azam', '1978-11-29 12:59:30.670', 'Male', 1)

Select * from  Employees