SQL Server - Validation During Function Creation

Now here's a little thing I didn't know until recently. Seems like this is happening a lot lately and I really should get around to documenting these at some point. Anyhoo, my latest finding is as follows...

We have a process that, every time a script is checked into our version control system, rebuilds all of the databases from scratch, starting from Release 1 and then applying all updates in order. This normally ensures a problem free release into production, however, this most recent time was different. This time the release into production failed when trying to create a function in one of the databases. So what was different between production and the build process? If I mention that we have a stored procedure that creates a temporary table that is used by the function, does that prompt any ideas of what went wrong?

Well, here's the thing...
For inline table functions, attempting to create one when the underlying object does not exist will fail. However, for scalar and multi-statement table functions things are a little different. If the underlying objects do not exist the functions will be created with no issue. However, if the underlying objects do exist, a check is performed to ensure that their definition matches that expected by the function.

So in our case, because the system is live in production the SP has been called many times and thus the table has been created and dropped many times. However, in the new update the structure of this table has been changed. Now because the SP is never actually called during the build process no table has been created and the function can be deployed. In production however, the SP has been called and the old version of the table does exist, causing the installation to fail.

Stored procedures exhibit this same behaviour but don't have the differences related to inline and multi-statement/scalar functions.

Try it yourself:

/**
 * Create functions.
 */ 
-- Successful.
CREATE FUNCTION [dbo].[udf_scalar_works]() 
RETURNS INT 
AS
BEGIN 
  RETURN ( SELECT TOP 1 [col2] FROM [dbo].[bar] );
END;
GO

-- Successful.
CREATE FUNCTION [dbo].[udf_multi_works]()
RETURNS @x TABLE (aaa INT)
AS
BEGIN
  INSERT INTO @x (aaa)
  SELECT [col2]
  FROM [dbo].[bar];
  
  RETURN;
END;
GO

-- Error: Invalid object name 'dbo.bar'.
CREATE FUNCTION [dbo].[udf_inline_doesnt_work]()
RETURNS TABLE
AS
  RETURN ( SELECT [col2]
           FROM [dbo].[bar] );
GO


/**
 * Create table without the required column.
 */
CREATE TABLE [dbo].[bar] ( [col1] INT );GO


/**
 * Try create functions again.
 */
-- Invalid column name 'col2'.
CREATE FUNCTION [dbo].[udf_scalar_doesnt_work]()
RETURNS INT
AS
BEGIN
  RETURN ( SELECT TOP 1 [col2] FROM [dbo].[bar] );
END;
GO

-- Error: Invalid column name 'col2'.
CREATE FUNCTION [dbo].[udf_multi_doesnt_work]()
RETURNS @x TABLE (aaa INT)
AS
BEGIN
  INSERT INTO @x (aaa)
  SELECT [col2]
  FROM [dbo].[bar];
  
  RETURN; 
END;
GO

-- Error: Invalid column name 'col2'.
CREATE FUNCTION [dbo].[udf_inline_doesnt_work]()
RETURNS TABLE
AS
  RETURN ( SELECT [col2]
           FROM [dbo].[bar] );
GO