One interesting concept in SQL Server is Deferred Name Resolution. This is something many developers struggle with understanding how this works and where it works.
In the Microsoft docs, there is a specific section in the CREATE TRIGGER docs that covers Deferred Name Resolution. This is a short section, and I’ve reproduced it below:
SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that don’t exist at compile time. This ability is called deferred name resolution.
I don’t know how batches are compiled, but procs and triggers are compiled for sure. What this statement says is that I can reference a table in a proc or trigger that doesn’t exist. When I create the trigger or proc, the reference is deferred at compile time and resolved at runtime.
Let’s see how this works. I’ll run this code in SSMS, all at once. In this code, I create a database in one batch, switch to it in the next, and then create a proc in the third.
CREATE DATABASE DNRTest
GO
USE DNRTest
go
CREATE PROCEDURE dnrproc
AS
SELECT * FROM sdfsfdsfs
GO
If we look in SSMS, this works.
In my database, I have only one object, the stored procedure.
However, if I execute this, it fails.
The table doesn’t exist, so the proc fails. However, I can not create the table and re-run the proc, and it works.
This is handy as I might create procs that reference temp tables, which don’t exist until they’re created. Often this happens in the proc, but if we were to try and resolve the reference at compile time, it would fail.
This also works in triggers as I might often script a table and triggers that reference a second table. When I run that script, I don’t want to trigger creation to fail, so I defer the name resolution until the trigger fires. This way my scripts can be organized logically.
This also works with functions, as seen below.
