Deferred Name Resolution In SQL Server

Kendra Little explains the concept of deferred name resolution in SQL Server:

In this case, I’m creating a temporary stored procedure (out of laziness, it means I don’t have to clean up a quick demo) –

CREATE OR ALTER PROCEDURE #testASIF 1=0 EXECUTE dbdoesnotexist.dbo.someproc;GO

The database dbdoesnotexist does NOT exist, but I’m still allowed to create the procedure.

When I do so, I get an informational message:

The module ‘#test’ depends on the missing object ‘dbdoesnotexist.dbo.someproc’. The module will still be created; however, it cannot run successfully until the object exists.

This can be useful in some cases where you’ll be querying a table or procedure that may not exist all the time, but which will exist when a certain code block is run.

But, as Kendra points out, deferred name resolution doesn’t work everywhere, so it’s important to know the rules around when it will or will not work.

Related Posts

Finding The SQL Server Port With T-SQL

Jack Vamvas shows us how to find the port SQL Server is listening on using T-SQL: Question: Without going into the SQL Server Configuration manager via the GUI is there a command oriented method to extract the port number SQL Server is listening on? Answer: There are a few different methods to extract the port number without going […]

Read More

Indexed View Matching

Erik Darling has a series of posts on indexed views, with the latest covering query matching even when using a keyword in creation of the indexed view itself: There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031