Have you ever tried to create an object in SQL Server, but it failed due to a missing table, column, or other dependency? If so, you’ve hit a case where SQL Server doesn’t offer ‘deferred name resolution’.

While these errors can be helpful when you’ve made a typo or accidentally used the wrong database, this can sometimes be a big hassle when you are…

• Deploying database code to set up a partial environment
• Deploying database code from version control to an empty database to ensure the code is valid

In this post, I walk through common scenarios and test whether deferred name resolution works or not.

What Is Deferred Name Resolution?

Deferred Name Resolution allows you to create objects in SQL Server without verifying that referenced objects (or referenced columns in objects) exist.

This is quite a confusing feature because it only works for some types of objects in specific scenarios. Every time I think I understand this feature, I am sure to discover a new gotcha.

Summary Table

I haven’t memorized everything in this post, and I don’t expect that you will, either. I’m creating this as a reference I can revisit when this topic comes up. Here’s a summary of when different object types do and do not get deferred name resolution, per my testing:

To dig into any area, use the table of contents above to jump to that section for demo code and a comment or two.

Working Around a Lack of Deferred Name Resolution

There is no special trick or trace flag which I know of to magically enable deferred name resolution where it doesn’t work. However, if you need to deploy code to an empty database or to a partial database environment and it is failing due to missing dependencies, there are a few techniques which you can leverage to help get around the errors.

Restore Databases as a “Base” for Deployments in Test Environments

Dependencies on objects are only checked when you run CREATE or ALTER code. These dependencies are not checked when you restore a database. It is possible to restore a database containing all sorts of invalid objects, in fact, such as views which reference tables that no longer exist.

This means that you can restore databases to support dependencies, whether they are cross-database or cross-server dependencies, and you can process the restore commands in any order you like.

Use Synonyms for Flexibility Across Environments

Although synonyms aren’t a simple fix for a lack of deferred name resolution, they’re incredibly helpful when it comes to cross-database and cross-server dependencies.

Synonyms provide a helpful level of abstraction which allows you to encapsulate any environment-specific information such as database names or linked server names in a single type of object (synonyms). One common design pattern is to:

• Create synonyms for all object dependencies outside the current database
• Deployments include a script which runs at the beginning of each deployment and checks that all synonyms are created properly for the target environment, and recreates them if needed

This has the benefits of making the definitions of stored procedures, views, and functions identical across databases: all objects refer to the same synonym. It also allows flexibility in environment configuration: perhaps in some environments all databases may be restored to the same SQL Server instance, while in other environments they are referred to multiple instances.

I recommend following this pattern for all cross-database dependencies, even in cases where SQL Server offers deferred name resolution. Being consistent at this helps developers and database administrators working with the database understand the configuration.

Dynamic SQL, If You Dare (I Do Not Dare in This Case)

Another option is to utilize Dynamic SQL and only create objects with external dependencies in some target environments.

I am a fan of Dynamic SQL for many use cases, but I’m not generally a huge fan of it for schema definitions. This makes it hard to understand which objects are in version control.

Don’t Use Linked Servers

This is easier said than done when it comes to legacy codebases, but avoid linked servers whenever possible.

When writing new code, always check to see if data can be managed entirely by application services and dependencies between databases can be kept to a minimum. Modern software trends generally advise on building applications with clear service boundaries and minimizing dependencies at the database level, so this usually should not be a radical request.

Stored Procedures Sometimes Get Deferred Name Resolution

Stored procedures have deferred name resolution for object creation in some cases. This means that you can often create a stored procedure referencing a table that doesn’t exist yet. When the stored procedure is executed for the first time, the SQL Server query processor will validate that dependencies are present.

✅ Schema and/or Object Does Not Exist

For example, SQL Server can successfully create this stored procedure even though my database does not have a schema named SchemaDoesNotExist.

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM SchemaDoesNotExist.TableDoesNotExist;
GO

Similarly, if the schema exists but no table or view exists with the given name, the stored procedure can be created as well. This code succeeds:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM dbo.TableDoesNotExist;
GO

Stored procedures can reference temporary tables (including global temporary tables) which do not exist and be created successfully. This stored procedure can be created:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM #DoesNotExist;
GO

Deferred name resolution isn’t only for tables and views. Deferred name resolution also works when calling other stored procedures which don’t exist. This procedure is created successfully.

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
EXECUTE dbo.ProcedureDoesNotExist;
GO

In this case, SQL Server prints an informational message:

The module 'TestProcedure' depends on the missing object 'dbo.ProcedureDoesNotExist'. The module will still be created; however, it cannot run successfully until the object exists.

We can also successfully create a procedure referencing a scalar or table valued function which does not exist. Here is an example for a table valued function:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM dbo.TableValuedFunctionDoesNotExist();
GO

❌ Table or View Exists but Doesn’t Have the Column Listed

Stored procedures cannot use deferred name resolution when they reference an object which does exist, but which does not contain a column listed. For example:

CREATE TABLE
    dbo.T1
(
    i integer IDENTITY
);
GO

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM dbo.T1;
GO

This code throws an invalid column error and the stored procedure is not created.

Msg 207, Level 16, State 1, Procedure TestProcedure, Line 4 [Batch Start Line 2]
Invalid column name 'SomeColumn'.

❌ Stored Procedure in Another Database Which Is Offline

The rules above also apply to references to objects in other databases on the same SQL Server instance:

• ✅ You can create a stored procedure referencing objects which don’t exist in another database
• ✅ You can create a stored procedure referencing objects in a database which doesn’t exist
• ❌ You CANNOT create a stored procedure referencing a column which does not exist in a table which does exist in another database

However, if you attempt to reference an object in a database which is offline, that sometimes causes a problem.

To demonstrate this, first, create a database and set it offline:

IF DB_ID('PutMeOffline') IS NULL
BEGIN
    CREATE DATABASE PutMeOffline;
END;
GO

ALTER DATABASE
    PutMeOffline
SET OFFLINE;
GO

We get deferred name resolution if we reference a table in an offline database. This statement succeeds:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    *
FROM PutMeOffline.dbo.TableDoesNotExist;
GO

However, if we try to reference a stored procedure in an offline database, our create statement fails:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
EXECUTE PutMeOffline.dbo.ProcedureDoesNotExist;
GO

This returns the following error:

Msg 942, Level 14, State 4, Procedure TestProcedure, Line 3 [Batch Start Line 13]
Database 'PutMeOffline' cannot be opened because it is offline.

This error does not appear if I create a stored procedure referencing a table valued function in an offline database.

❌✅ Object Referenced via a Linked Server

Linked servers ruin everything.

Referencing an object via a four-part name breaks deferred name resolution, for the most part.

In this example, I reference a linked server which does not exist:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM LinkedServerDoesNotExist.DatabaseDoesNotExist.dbo.TableDoesNotExist;
GO

This fails with the error:

Msg 7202, Level 11, State 2, Procedure TestProcedure, Line 3 [Batch Start Line 0]
Could not find server 'LinkedServerDoesNotExist' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

In this example, I use a valid linked server, but reference a database which does not exist on the remote server.

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM [DERP\SEATTLE].DatabaseDoesNotExist.dbo.TableDoesNotExist;
GO

This fails with the error:

Msg 7314, Level 16, State 1, Procedure TestProcedure, Line 3 [Batch Start Line 0]
The OLE DB provider "SQLNCLI11" for linked server "DERP\SEATTLE" does not contain the table ""DatabaseDoesNotExist"."dbo"."TableDoesNotExist"". The table either does not exist or the current user does not have permissions on that table.

I get the same error if I reference a table that does not exist in a valid database across the linked server.

Everything here seems to have an exception. For whatever reason, I am able to get deferred name resolution when it comes to referencing a stored procedure across a linked server. This code succeeds:

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
EXECUTE LinkedServerDoesNotExist.DatabaseDoesNotExist.dbo.ProcedureDoesNotExist;
GO

Synonyms Use Deferred Name Resolution, but Their Power Is Not Transitory

Synonyms are lightweight pointers in SQL Server. As the documentation mentions,

The base object need not exist at synonym create time. SQL Server checks for the existence of the base object at run time.

✅ Schema and/or Object Does Not Exist

This synonym can be created successfully:

CREATE SYNONYM dbo.TestSynonym
FOR dbo.TableDoesNotExist;
GO

✅ Object Referenced via a Linked Server

I can even create a synonym referencing a linked server which does not exist:

CREATE SYNONYM dbo.TestSynonym
FOR LinkedServerDoesNotExist.DatabaseDoesNotExist.dbo.TableDoesNotExist;
GO

✅ Object in Another Database Which Is Offline

Synonyms don’t seem to have any problems with offline databases, either. I tested this with the same offline database which I created earlier with this code:

IF DB_ID('PutMeOffline') IS NULL
BEGIN
    CREATE DATABASE PutMeOffline;
END;
GO

ALTER DATABASE
    PutMeOffline
SET OFFLINE;
GO

The following code to create a synonym referencing the offline database works.

DROP SYNONYM IF EXISTS dbo.TestSynonym;
GO

CREATE SYNONYM
    dbo.TestSynonym
FOR PutMeOffline.dbo.TableDoesNotExist;
GO

❌ Using a Synonym Doesn’t Fix a Lack of Deferred Name Resolution for Other Objects

I love synonyms and it’s terrific that all of these examples work, but don’t get too excited. Synonyms are not a one-stop workaround for a lack of deferred name resolution. ‘Run time’ can apparently mean creation of an object referencing the synonym.

For example, if I create a procedure that references TestSynonym as created above, the code still fails.

CREATE OR ALTER PROCEDURE
    dbo.TestProcedure
AS
SELECT
    SomeColumn
FROM dbo.TestSynonym;
GO

This results in the same error we saw above when referencing the table via a four-part name:

Msg 7202, Level 11, State 2, Procedure TestProcedure, Line 3 [Batch Start Line 8]
Could not find server 'LinkedServerDoesNotExist' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Similarly, synonyms aren’t a workaround that can instantly get you deferred name resolution for other objects which lack it, such as views.

Speaking of views…

Views Do Not Get Deferred Name Resolution

When you create a view in SQL Server, all dependencies need to be able to be resolved or the view will not be created successfully. This is true even if you do not create the view with SCHEMABINDING.

❌ Schema and/or Object Does Not Exist

This script attempts to create a view referencing a table which doesn’t exist in the current database. The script fails:

CREATE OR ALTER VIEW
    dbo.TestView
AS
SELECT
    *
FROM dbo.TableDoesNotExist;
GO

SQL Server returns Error 208 and does not create the view.

Msg 208, Level 16, State 1, Procedure TestView, Line 4 [Batch Start Line 0]
Invalid object name 'dbo.TableDoesNotExist'.

Similarly, if you try to create a view referencing a scalar function that does not exist, the script also fails. For example:

DROP TABLE IF EXISTS dbo.T1;
GO
CREATE TABLE dbo.T1 (i INT IDENTITY);
GO
CREATE OR ALTER VIEW dbo.TestView
AS
 SELECT dbo.SomeFunction(i)
 FROM dbo.T1;
GO

This code fails. SQL Server returns error 4121:

Msg 4121, Level 16, State 1, Procedure TestView, Line 3 [Batch Start Line 5]
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SomeFunction", or the name is ambiguous.

❌ Table or View Exists but Doesn’t Have the Column Listed

SQL Server must resolve not only the objects referenced, but also the columns referenced when you create a view. For example, this code attempts to use a column which doesn’t exist in the referenced table:

DROP TABLE IF EXISTS dbo.T1;
GO
CREATE TABLE dbo.T1 (i INT IDENTITY);
GO
CREATE OR ALTER VIEW dbo.TestView
AS
 SELECT ColumnDoesNotExist
 FROM dbo.T1;
GO

This code fails with error 207 and the view is not created.

Msg 207, Level 16, State 1, Procedure TestView, Line 3 [Batch Start Line 4]
Invalid column name 'ColumnDoesNotExist'.

❌ Object in Another Database Which Is Offline

You cannot create a view referencing an object in an offline database, as attempted in this code:

IF DB_ID('PutMeOffline') IS NULL
 CREATE DATABASE PutMeOffline;
GO
ALTER DATABASE PutMeOffline SET OFFLINE
GO
CREATE OR ALTER VIEW dbo.TestView
AS
 SELECT *
 FROM PutMeOffline.dbo.T1;
GO

This fails with error 942:

Msg 942, Level 14, State 4, Procedure TestView, Line 3 [Batch Start Line 5]
Database 'PutMeOffline' cannot be opened because it is offline.

❌ Object Referenced via a Linked Server (Distributed View)

The above rules hold true for tables in other databases referenced using three-part naming, as well as tables referenced across linked servers using four-part naming. SQL Server must validate that the objects and columns referenced exist, or it cannot create the view.

Here is an example for a linked server:

CREATE OR ALTER VIEW dbo.TestView
AS
 SELECT *
 FROM SomeLinkedServer.dbo.TableDoesNotExist;
GO

This fails with error 208:

Msg 208, Level 16, State 1, Procedure TestView, Line 4 [Batch Start Line 0]
Invalid object name 'SomeLinkedServer.dbo.TableDoesNotExist'.

❌ Synonym References Object Which Doesn’t Exist

As mentioned above, synonyms can be created referencing objects which don’t exist, but this isn’t a simple workaround for objects which lack deferred name resolution. This code successfully creates a synonym, dbo.TestSynonym, which references a table which doesn’t exist. However, the code to create the view which references the synonym fails:

DROP SYNONYM IF EXISTS dbo.TestSynonym;
GO
CREATE SYNONYM dbo.TestSynonym
FOR dbo.TableDoesNotExist;
GO
CREATE OR ALTER VIEW dbo.TestView
AS
 SELECT*
 FROM dbo.TestSynonym;
GO

SQL Server returns error 5313 in this case:

Msg 5313, Level 16, State 1, Procedure TestView, Line 4 [Batch Start Line 5]
Synonym 'dbo.TestSynonym' refers to an invalid object.

🔄 How Circular Views Come into Existence

It is possible to have circular references between views in SQL Server. In this case, 2 or more views are mutually dependent upon one another. This typically happens by a process like the following:

• View dbo.V1 is created referencing only tables in the database
• View dbo.V2 is created with a reference to dbo.V1
• View dbo.V1 is edited and a dependency to dbo.V2 is added

SQL Server allows this progression of changes to be deployed, but this results in a situation where views dbo.V1 and dbo.V2 cannot be successfully created in an empty database using their full definitions.

Function Behavior Varies by Type

I’m looking at scalar, inline (single statement) table valued functions, and multi-statement table valued functions separately because some get deferred name resolution and some don’t.

Scalar Functions

✅ Schema and/or Object Does Not Exist

Scalar functions can get deferred name resolution. The following code referencing a table which does not exist succeeds:

CREATE OR ALTER FUNCTION
    dbo.TestScalarFunction()
RETURNS varchar(128)
AS
BEGIN
    DECLARE
        @i integer;

    SELECT
        @i = 2
    FROM dbo.TableDoesNotExist;

    RETURN 1;
END;
GO

You might wonder if a call to this function works, since 1 is returned no matter what happens within the function. Let’s test that out with the following code:

SELECT dbo.TestScalarFunction();
GO

This fails with error 208:

Msg 208, Level 16, State 1, Line 11
Invalid object name 'dbo.TableDoesNotExist'.

❌ Table or View Exists but Doesn’t Have the Column Listed

Just like with stored procedures, if you create a scalar function referencing a table or view which exists, SQL Server will check the columns you specify and require that they are valid. This code to create dbo.TestScalarFunction fails:

DROP TABLE IF EXISTS dbo.T1;
GO
CREATE TABLE dbo.T1 (i INT IDENTITY);
GO
CREATE OR ALTER FUNCTION dbo.TestScalarFunction()
RETURNS VARCHAR(128) 
AS
BEGIN
 DECLARE @i INT;
 SELECT @i=DoesNotExist FROM dbo.T1;
 RETURN 1;
END
GO

SQL Server returns error 207 and does not create the function.

Msg 207, Level 16, State 1, Procedure TestScalarFunction, Line 6 [Batch Start Line 4]
Invalid column name 'DoesNotExist'.

✅ Object in Another Database Which Is Offline

Like stored procedures scalar functions which reference tables in offline databases can be created successfully. This code succeeds:

IF DB_ID('PutMeOffline') IS NULL
 CREATE DATABASE PutMeOffline;
GO
ALTER DATABASE PutMeOffline SET OFFLINE
GO
CREATE OR ALTER FUNCTION dbo.TestScalarFunction()
RETURNS VARCHAR(128) 
AS
BEGIN
 DECLARE @i INT;
 SELECT @i=2 FROM PutMeOffline.dbo.TableDoesNotExist;
 RETURN 1;
END
GO

Stored procedures have a gotcha where they fail if they reference another stored procedure in an offline database. I don’t believe that scalar functions can call stored procedures 🤔, so there is no issue here.

I did test a scalar function which calls a scalar function in an offline database. This code succeeds:

IF DB_ID('PutMeOffline') IS NULL
 CREATE DATABASE PutMeOffline;
GO
ALTER DATABASE PutMeOffline SET OFFLINE
GO
CREATE OR ALTER FUNCTION dbo.TestScalarFunction()
RETURNS VARCHAR(128) 
AS
BEGIN
 DECLARE @i INT;
 SELECT @i=dbo.SomeFunction(2) FROM PutMeOffline.dbo.TableDoesNotExist;
 RETURN 1;
END
GO

❌ Object Referenced via a Linked Server

Creating a scalar function referencing an object across a linked server seems like a recipe for extreme pain, but SQL Server does allow this.

However, to create the function SQL Server will need to cross the linked server and validate that the object, and any columns referenced in it, exist.

CREATE OR ALTER FUNCTION dbo.TestScalarFunction()
RETURNS VARCHAR(128) 
AS
BEGIN
 DECLARE @i INT;
 SELECT @i=2 FROM LinkedServerDoesNotExist.SomeDatabase.dbo.TableDoesNotExist;
 RETURN 1;
END
GO

Inline Table Valued Functions (TVFs)

Unlike scalar functions, inline TVFs do NOT get deferred name resolution.

❌ Schema and/or Object Does Not Exist

CREATE OR ALTER FUNCTION dbo.TestInlineTableValuedFunction()
RETURNS TABLE
AS
    RETURN ( SELECT * FROM dbo.TableDoesNotExist );
GO

This fails with the error:

Msg 208, Level 16, State 1, Procedure TestInlineTableValuedFunction, Line 4 [Batch Start Line 0]
Invalid object name 'dbo.TableDoesNotExist'.

❌ Table or View Exists but Doesn’t Have the Column Listed

If you reference a table which exists in an inline TVF, you can only reference columns which exist. Here is our demo code:

DROP TABLE IF EXISTS dbo.T1;
GO
CREATE TABLE dbo.T1 (i INT IDENTITY);
GO
CREATE OR ALTER FUNCTION dbo.TestInlineTableValuedFunction()
RETURNS TABLE
AS
 RETURN ( SELECT DoesNotExist FROM dbo.T1 );
GO

This fails with error 207:

Msg 207, Level 16, State 1, Procedure TestInlineTableValuedFunction, Line 4 [Batch Start Line 4]
Invalid column name 'DoesNotExist'.

❌ Object in Another Database Which Is Offline

Inline TVFs cannot reference objects in offline databases. Here is our example code:

IF DB_ID('PutMeOffline') IS NULL
 CREATE DATABASE PutMeOffline;
GO
ALTER DATABASE PutMeOffline SET OFFLINE
GO
CREATE OR ALTER FUNCTION dbo.TestInlineTableValuedFunction()
RETURNS TABLE
AS
 RETURN ( SELECT * FROM PutMeOffline.dbo.SomeTable );
GO

This fails with the error:

Msg 942, Level 14, State 4, Procedure TestInlineTableValuedFunction, Line 4 [Batch Start Line 5]
Database 'PutMeOffline' cannot be opened because it is offline.

❌ Object Referenced via a Linked Server

If you reference a linked server in your inline TVF definition, SQL Server will need to successfully access the linked server and validate that the object and any columns referenced exists. Here is our demo code:

CREATE OR ALTER FUNCTION dbo.TestInlineTableValuedFunction()
RETURNS TABLE
AS
 RETURN ( SELECT * FROM LinkedServerDoesNotExist.SomeDatabase.dbo.TableDoesNotExist );
GO

This code fails with error 7202:

Msg 7202, Level 11, State 2, Procedure TestInlineTableValuedFunction, Line 4 [Batch Start Line 0]
Could not find server 'LinkedServerDoesNotExist' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Multi-Statement Table Valued Functions (TVFs)

Multi-statement table valued functions can get deferred name resolution sometimes. In my testing, I found the behavior of multi-statement TVFs to match the behavior of stored procedures regarding deferred name resolution.

✅ Schema and/or Object Does Not Exist

This create statement for a multi-statement table valued function succeeds, although it references a table which does not exist:

CREATE OR ALTER FUNCTION
    dbo.TestMultiStatementTableValuedFunction()
RETURNS @doggo TABLE
(
    doggo varchar(128)
)
AS
BEGIN
    INSERT @doggo
    (
        doggo
    )
    SELECT
        doggo
    FROM dbo.TableDoesNotExist;

    RETURN;
END;
GO

❌ Table or View Exists but Doesn’t Have the Column Listed

Consistent with what we’ve seen in other examples, if an object exists then any columns listed for the object must exist as well. Here is our example code to test this in a multi-statement table valued function:

DROP TABLE IF EXISTS dbo.T1;
GO

CREATE TABLE
    dbo.T1
(
    i integer IDENTITY
);
GO

CREATE OR ALTER FUNCTION
    dbo.TestMultiStatementTableValuedFunction()
RETURNS @doggo TABLE
(
    doggo varchar(128)
)
AS
BEGIN
    INSERT @doggo
    (
        doggo
    )
    SELECT
        DoesNotExist
    FROM dbo.T1;

    RETURN;
END;
GO

This fails with error 207:

Msg 207, Level 16, State 1, Procedure TestMultiStatementTableValuedFunction, Line 4 [Batch Start Line 4]
Invalid column name 'DoesNotExist'.

❌ Object in Another Database Which Is Offline

Like stored procedures, I found that multi-statement TVFs can reference tables and views in offline databases. This code executes successfully:

IF DB_ID('PutMeOffline') IS NULL
BEGIN
    CREATE DATABASE PutMeOffline;
END;
GO

ALTER DATABASE
    PutMeOffline
SET OFFLINE;
GO

CREATE OR ALTER FUNCTION
    dbo.TestMultiStatementTableValuedFunction()
RETURNS @doggo TABLE
(
    doggo varchar(128)
)
AS
BEGIN
    INSERT @doggo
    (
        doggo
    )
    SELECT
        *
    FROM PutMeOffline.dbo.T1;

    RETURN;
END;
GO

However, if I add a statement which calls a stored procedure in the offline database, the code fails:

IF DB_ID('PutMeOffline') IS NULL
BEGIN
    CREATE DATABASE PutMeOffline;
END;
GO

ALTER DATABASE
    PutMeOffline
SET OFFLINE;
GO

CREATE OR ALTER FUNCTION
    dbo.TestMultiStatementTableValuedFunction()
RETURNS @doggo TABLE
(
    doggo varchar(128)
)
AS
BEGIN
    EXECUTE PutMeOffline.dbo.ProcedureDoesNotExist;

    INSERT @doggo
    (
        doggo
    )
    SELECT
        *
    FROM PutMeOffline.dbo.T1;

    RETURN;
END;
GO

This fails with error 942:

Msg 942, Level 14, State 4, Procedure TestMultiStatementTableValuedFunction, Line 4 [Batch Start Line 19]
Database 'PutMeOffline' cannot be opened because it is offline.

❌✅ Object Referenced via a Linked Server

Finally, if you attempt to create a multi-statement TVF which references a table or view via a linked server, as with our other test cases SQL Server must access the linked server and validate that the object and any referenced columns exist.

CREATE OR ALTER FUNCTION
    dbo.TestMultiStatementTableValuedFunction()
RETURNS @doggo TABLE
(
    doggo varchar(128)
)
AS
BEGIN
    INSERT @doggo
    (
        doggo
    )
    SELECT
        doggo
    FROM LinkedServerDoesNotExist.SomeDatabase.dbo.TableDoesNotExist;

    RETURN;
END;
GO

This fails with error 7202:

Msg 7202, Level 11, State 2, Procedure TestMultiStatementTableValuedFunction, Line 4 [Batch Start Line 0]
Could not find server 'LinkedServerDoesNotExist' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

However, as with stored procedures, multi-statement TVFs might execute a procedure across a linked server. Oddly enough, this code succeeds:

CREATE OR ALTER FUNCTION dbo.TestMultiStatementTableValuedFunction()
RETURNS @doggo TABLE(doggo VARCHAR(128))
AS BEGIN
    EXEC LinkedServerDoesNotExist.SomeDatabase.dbo.Something
    RETURN;
END;
GO