Function WITH SCHEMABINDING in SQL Server
Table of Content:
Creating a function WITH SCHEMABINDING option:
- The function fn_GetEmployeeNameById(), is dependent on Employees table.
- Delete the table Employees from the database.
Drop Table Employees - 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.
- Now, recreate the table and insert data, using the scripts provided.
- 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