SQL Server doesn’t check to see if dependent objects exist when you create stored procedures and certain types of user defined functions. This means that you could mistype a table name when creating a stored proc, for example, and not know realize there is a mistake until running the proc. (Of course, you will properly test your code, and this mistake will be discovered long before it hits production!)

Here’s an example that lets you create the procedure without complaint, even though the table, dbo.BadTable, doesn't exist:

CREATE OR ALTER PROC dbo.usp_CreatesAnyway AS 
SELECT * 
FROM dbo.BadTable; 
GO

If you take a look at the code in the Query window in SSMS, you will see the red squiggly line under the table name, but SQL Server doesn’t stop you from creating the proc. The problem shows up when you try to execute it. If you run this:

EXEC dbo.usp_CreatesAnyway;

you get this:

You may be wondering why SQL Server lets you do something so stupid, but there is a good reason. Many stored procs have steps with tables, possibly temp tables or table variables, that are created inside the proc. You can also have dynamically created T-SQL in a proc. If the table names were resolved at create time, then it would not be possible to do these things.

As mentioned earlier, some types of user defined functions (UDFs) also have deferred name resolution. There are three types of UDFs:

• Inline Table-valued Functions: These functions are similar to views except that they take parameters. The only thing allowed in the definition is a query, but it could be filtered by the parameters. The function returns a set or rows or “table.”
• Multi-statement Table-Valued Functions: These functions take parameters and return a set of rows, but the definition might contain multiple statements, table variables, conditional logic, and looping.
• Scalar-valued Functions: These functions return a single value, not a table. They take parameters, may have table variables, multiple statements, conditional logic, and looping.

Debating the pros and cons of these UDFs is best left for another day. The point here is that, of the three types of UDFs, only the objects in Inline Table-Valued functions are resolved at create time. Here is an example showing Inline Table-valued functions:

USE AdventureWorks2017;
GO
--Create an INLINE TABLE-VALUED Function
CREATE OR ALTER FUNCTION dbo.udf_FullName
(
@BusinessEntityID INT
)
RETURNS TABLE 
AS
RETURN 
(
SELECT BusinessEntityID, 
CONCAT(FirstName,' ' + MiddleName,' ', LastName) AS FullName
FROM Person.Person 
WHERE BusinessEntityID = @BusinessEntityID
);
GO
--Try to create one from a non-existant table
CREATE OR ALTER FUNCTION dbo.udf_FullNameBAD
(
@BusinessEntityID INT
)
RETURNS TABLE 
AS
RETURN 
(
SELECT BusinessEntityID, 
CONCAT(FirstName,' ' + MiddleName,' ', LastName) AS FullName
FROM Person.People
WHERE BusinessEntityID = @BusinessEntityID
);
GO

The first function is created successfully, but the second one fails with this message:

You can use the good function like this:

SELECT C.CustomerID, FN.FullName
FROM Sales.Customer AS C
CROSS APPLY dbo.udf_FullName(PersonID) AS FN;

Here are partial results of the query:

Only the Customer rows that have a match in Person.Person will show up in the query, similar to an INNER JOIN. If you wish to see all Customer rows, use OUTER APPLY instead of CROSS APPLY.

Since this type of function is so similar to a view, it makes sense that the table names are resolved at creation time. Here is another example showing what happens with Multi-statement Table-valued functions:

CREATE OR ALTER FUNCTION dbo.udf_FullNames_MSTV
(
@BusinessEntityID INT
)
RETURNS 
@FullNames TABLE 
(
BusinessEntityID INT,
FullName NVARCHAR(75)
)
AS
BEGIN
--Check the table to see if the ID is right
IF NOT EXISTS(
SELECT * FROM Person.Person 
WHERE BusinessEntityID = @BusinessEntityID) BEGIN 
RETURN
END
INSERT INTO @FullNames(BusinessEntityID, FullName)
SELECT BusinessEntityID, 
CONCAT(FirstName,' ' + MiddleName,' ', LastName) AS FullName
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID;
RETURN 
END
GO
--Create a function that queries a non-existant table
CREATE OR ALTER FUNCTION dbo.udf_FullNames_BadTable
(
@BusinessEntityID INT
)
RETURNS 
@FullNames TABLE 
(
BusinessEntityID INT,
FullName NVARCHAR(75)
)
AS
BEGIN
--Check the table to see if the ID is right
IF NOT EXISTS(
SELECT * FROM Person.People 
WHERE BusinessEntityID = @BusinessEntityID) BEGIN 
RETURN
END
INSERT INTO @FullNames(BusinessEntityID, FullName)
SELECT BusinessEntityID, 
CONCAT(FirstName,' ' + MiddleName,' ', LastName) AS FullName
FROM Person.People
WHERE BusinessEntityID = @BusinessEntityID;
RETURN 
END
GO

SQL Server will allow you to create both UDFs without error. You’ll see the problem when you try to run the second one, however:

--Runs!
SELECT C.CustomerID, FN.FullName
FROM Sales.Customer AS C
CROSS APPLY dbo.udf_FullNames_MSTV(PersonID) AS FN;
--Returns an error
SELECT C.CustomerID, FN.FullName
FROM Sales.Customer AS C
CROSS APPLY dbo.udf_FullNames_BadTable(PersonID) AS FN;

You’ll see the same behavior with Scalar-valued UDFs.

The first returns a row for every Customer, even when there is no FirstName. The second returns an error:

Deferred name resolution is not limited to table names. It’s possible to create a stored procedure that calls a non-existent proc as well. You can’t do that with UDFs, however, because they cannot call stored procedures. You can create a Multi-statement Table-valued or Scalar-valued function that calls a bad function name, however.

Here’s another twist. What if you create a proc with a valid table name but one of the column names is incorrect? Here is an example:

CREATE OR ALTER PROC dbo.usp_BadCol AS 
SELECT FullName
FROM Person.Person;
GO

Since there is no FullName column in the Person table, you see an error instead of creating the proc.

I hope this helps you understand where deferred name resolution comes into play when you are creating objects. This is also important to understand when troubleshooting issues with deployment scripts if objects are not created in the right order or when you are dealing with cross-database dependencies.