What is deferred name resolution in SQL Server?

This is one of the important interview question and most of the people do not have the proper practical answer for this question. Let us look at the following query.


Create procedure spNumberOfStudents
as
Begin
Select * from College
End

Note: In the above query the table College does not exist in the database.


When we are trying to create the above query it will be created successfully in the database, but while you are trying to execute the procedure you will get a dynamic error that is invalid object name College.


So what we understood here is that the object College was not checked while creating the store procedure(no syntax error) and that was checked only while executing stored procedure (syntax error occurred), this process is called as differed name resolution.

Note: The differed name resolution is not supported by functions.