Whenever a database with Service Broker enabled is restored, we notice that:

• The Service Broker is disabled in the restored copy
• The Service Broker GUID is NOT re-generated by default
• We need to manually re-enable Service Broker after the restore

The reason we need to manually re-enable Service Broker after the database restore is to prevent misdirection of broker messages to an incorrect copy of the database. I looked at this reason in my post here [Link].

After the post, I received a couple of questions on various channels. The most common question was:

We use Service Broker, and we need to have two identical copies of the same database for testing purposes. How can we ensure that Service Broker continues to work on both databases without any conflict?

The answer is actually quite simple. As mentioned in my previous post, we need to regenerate the Service Broker GUID. The scripts below show the various statements that one can use to enable Service Broker.

New Database AND/OR Restoring a copy of the database (for purposes other than recovery)

We need to generate a new Service Broker GUID in the following cases:

• Service Broker is being enabled in the database for the first time
• A copy of the database is being restored for purposes other than database recovery (testing, troubleshooting or other purposes)

--Enable the Broker and re-generate the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET NEW_BROKER;
GO

Please NOTE: Using NEW_BROKER will clear out any conversations in the database, however no error messages will be sent to the peer endpoint.

Restoring to a point-in-time

When restoring to a database to a point-in-time, it may happen that the existing conversations in the database may be invalid. In such cases, we need to reject/error these messages and send the appropriate message to the peer end-point.

Point-in-time restores are generally required during a database recovery which means that we cannot change the Service Broker GUID (else all messaging routes to the broker identifier will become invalid). To achieve this, we have the ERROR_BROKER_CONVERSATIONS option:

--Error out all conversations in the database
--Preserve the Broker GUID
--Enable the Broker message delivery
ALTER DATABASE AdventureWorks2012 SET ERROR_BROKER_CONVERSATIONS;
GO

Enabling Service Broker on an Existing Database

Enabling Service Broker on an existing database is quite simple – all one needs to do is use the ENABLE_BROKER option.

--Enable the Broker message delivery
--Preserve the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;
GO

Disabling Service Broker on an Existing Database

The process of disabling the Service Broker on an existing database is similar to the enable process – one needs to do is use the DISABLE_BROKER option.

--Disable the Broker message delivery
--Preserve the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET DISABLE_BROKER;
GO

IMPORTANT NOTE:

Please note that when we enable the Service Broker, database lock is requested. Hence, please close all open connections to the database before attempting to enable the broker. If on the MSDB database, please also stop SQL Server Agent.

Further Reading

• Database Restore – manually enabling Service Broker – Msg 9772 [Link]
• Managing Service Broker Identities [Link]

Until we meet next time,

Be courteous. Drive responsibly.

In most development and QA environments, one would find that a baseline copy of the database is restored and upgraded again to “begin from a clean state”. If your database architecture uses service broker to carry out background processes, you would have observed that whenever the database is restored on the same instance or on a different instance, we need to re-enable the broker manually:

ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;
GO

The Question

Our database architecture uses Service Broker. Recently, we encountered this situation in a test lab where a production database was brought in-house for some testing, and that got us thinking:

Why do we need to manually re-enable Service Broker on a restored database?

Answer

The answer is available (as always) in the MSDN documentation, the extracts from which are shown below.

• Service Broker routing relies on a unique identifier in each database to correctly deliver messages
• Each database contains a Service Broker identifier that distinguishes it from all other databases in the network. The service_broker_guid column of the sys.databases catalog view shows the Service Broker identifier for each database in the instance
• Service Broker routing uses the Service Broker identifier to ensure that all messages for a conversation are delivered to the same database
• To correctly support message delivery, each Service Broker identifier should be unique across all instances of the Database Engine on the same network. Otherwise, messages could be misdirected. When a new database is created, it is assigned a new Service Broker identifier that should be unique in the network. The identifier is restored when the database is either restored or attached

If the restore is intended to replace the original database (also applicable if the database is being moved to a different instance), then we would not want the Service Broker identifier to change. However, if the purpose of restoring the backup is to create a copy of the database, then the identifier should be changed.

To provide a clear explanation, I have come up with a small demo below:

Base State

To begin with, let us create a test database and enable service broker on the same.

USE master;
GO

--Create a test database
CREATE DATABASE ServiceBrokerFlagTesting;
GO

--Enable Service Broker on the database
ALTER DATABASE ServiceBrokerFlagTesting SET ENABLE_BROKER;
GO

--Check the database properties
SELECT 'Before Backup' AS Stage,
       sd.is_broker_enabled AS IsServiceBrokerEnabled,
       sd.service_broker_guid AS ServiceBrokerGUID,
       sd.name AS DatabaseName, 
       sd.database_id AS DatabaseId
FROM sys.databases AS sd WHERE sd.name = 'ServiceBrokerFlagTesting';
GO

Let us now create a backup of this database and drop the database so that we can restore it again from the backup. (Simple scripts to back-up and restore databases from a UNC path and/or disk are available here. You may customize them for your requirements.)

--Backup the database
USE master;
GO
EXEC SP_ADDUMPDEVICE 'disk',
                     'networkdrive',
                     'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLBackupServiceBrokerFlagTesting.bak'

BACKUP DATABASE ServiceBrokerFlagTesting TO networkdrive;

EXEC SP_DROPDEVICE 'networkdrive';
GO

--Drop the database
ALTER DATABASE ServiceBrokerFlagTesting SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE ServiceBrokerFlagTesting;
GO

Restore 2 copies from the same backup and query Service Broker Information

Now, let us restore two copies of database from the same backup and query the Service Broker information.

USE master;
GO
--Now, restore the backup to two databases
EXEC SP_ADDUMPDEVICE 'disk',
                     'networkdrive',
                     'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLBackupServiceBrokerFlagTesting.bak';

RESTORE DATABASE ServiceBrokerFlagTesting FROM networkdrive
WITH
MOVE 'ServiceBrokerFlagTesting' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerFlagTesting.mdf',
MOVE 'ServiceBrokerFlagTesting_log' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerFlagTesting_Log.ldf';

RESTORE DATABASE ServiceBrokerDummy FROM networkdrive
WITH
MOVE 'ServiceBrokerFlagTesting' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerDummy.mdf',
MOVE 'ServiceBrokerFlagTesting_log' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerDummy_log.ldf';

EXEC SP_DROPDEVICE 'networkdrive';
GO

--Check the Service Broker status - notice the Broker GUID
SELECT 'After Restore' AS Stage,
       sd.is_broker_enabled AS IsServiceBrokerEnabled,
       sd.service_broker_guid AS ServiceBrokerGUID,
       sd.name AS DatabaseName, 
       sd.database_id AS DatabaseId
FROM sys.databases AS sd 
WHERE sd.name = 'ServiceBrokerFlagTesting' 
   OR sd.name = 'ServiceBrokerDummy';
GO

As can be seen from the screenshot above, both the databases have the same value for the service_broker_guid. Also, notice that Service Broker is not enabled in either of the databases.

Enabling Service Broker and Error Msg 9772

Now, assume that both databases can be enabled to process the broker messages. If this is allowed, then we would see misdirected messages, i.e. a message that may be bound for database id 9 is being processed by database id 8 which would not be good. It is just like having two roads on opposite ends of the city leading to confusion amongst the motorists.

It is for this reason that SQL Server by default always disables message delivery when a database is restored or is taking part in mirroring and/or is the destination in a log shipping scenario.

Attempting to enable the service broker for both databases results in the following error message when the 2nd database is being processed:

--Enable the Broker
ALTER DATABASE ServiceBrokerFlagTesting SET ENABLE_BROKER;
GO

/*
Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "ServiceBrokerDummy" cannot be enabled because there is already an enabled Service Broker with the same ID.
*/
ALTER DATABASE ServiceBrokerDummy SET ENABLE_BROKER;
GO

Msg 9772, Level 16, State 1, Line 1

The Service Broker in database "ServiceBrokerDummy" cannot be enabled because there is already an enabled Service Broker with the same ID.

Summary

When working with Service Broker, one needs to be careful about message delivery. If a service broker enabled database needs to be restored for troubleshooting and/or data recovery purposes, then:

• When a database is restored/attached, the message delivery to the database would always be disabled
• If Service Broker needs to be enabled for a copy of the same database on the same network, then the service broker GUID must be changed via the use of the NEW_BROKER option. This option activates the broker and also generates a new service broker identifier for the database
• Care must be taken to ensure that only one database with a given Service Broker identifier has message delivery active. Otherwise messages would be misdirected to a wrong copy of the database

Further Reading

• Managing Service Broker Identities [Link]
• Service Broker Routing [Link]

Until we meet next time,

Be courteous. Drive responsibly.

A long time ago, I wrote a piece on how primary keys can be designed to use a non-clustered index. The post was quite popular and has some very interesting discussion associated with it. After that post, I have kept my eyes and ears open for myths and misconceptions around the whole primary key-clustered index-unique index combination and was surprised that a lot of novice developers think that all 3 are the same, i.e. a primary key is always a clustered index which is unique and that a unique index is same as a clustered index.

Now, obviously, there is a fundamental understanding issue here. In my earlier post, I broke the link between a primary key and a clustered index. Today, I will provide a few examples that help understand that:

Primary keys are always unique. But, a clustered index may not be unique and a unique index may not be clustered.

Please NOTE that this post is only intended to clear out a concept, and is not intended to be a recommendation or a best practice.

The Demonstration

It goes without saying that if we create a table with a primary key in the “usual” way (i.e. with a clustered index on the primary key), it will be both clustered and unique. Hence, I will address the demo in the following parts:

1. First, I will prove that even a non-clustered primary key is unique (it has to be, by definition)
   
2. Next, I will create a clustered Index on a column other than the primary key and prove that it is not unique
   
3. Finally, I will create a non-clustered unique index to prove that unique indexes are not necessarily clustered

Primary Keys are always Unique

The code provided below creates a clustered primary key on a test table and goes on to check the nature of the indexes created by the CREATE TABLE statement.

USE tempdb;

GO

–Safety Check

IF OBJECT_ID(‘tblIsClusteredPKUnique’) IS NOT NULL

    DROP TABLE tblIsClusteredPKUnique;

GO
–Create test table definition

–Notice that we are creating a clustered primary key

CREATE TABLE tblIsClusteredPKUnique (Id INT NOT NULL IDENTITY(1,1),

                                     DummyId INT NOT NULL,

                                     KeyName VARCHAR(20),

                                     CONSTRAINT pk_tblIsClusteredPKUnique_Id

                                                PRIMARY KEY CLUSTERED (Id)

                                    );

GO
–Check the types of Indexes that have been created

SELECT si.object_id,

       OBJECT_NAME(si.object_id) AS ObjectName,

       si.name,

       si.index_id,

       si.type,

       si.type_desc,

       si.is_unique,

       si.is_unique_constraint,

       si.is_primary_key

FROM sys.indexes AS si

WHERE si.object_id = OBJECT_ID(‘tblIsClusteredPKUnique’);

GO

As can be seen from the output from sys.indexes, we can confirm that: The clustered primary key was created as a unique index.

Now, let me create a non-clustered primary key and check the uniqueness of that non-clustered index.

USE tempdb;

GO

–Safety Check

IF OBJECT_ID(‘tblIsNonClusteredPKUnique’) IS NOT NULL

    DROP TABLE tblIsNonClusteredPKUnique;

GO
–Create test table definition

–Notice that the primary key has been defined as NON-CLUSTERED

CREATE TABLE tblIsNonClusteredPKUnique (Id INT NOT NULL IDENTITY(1,1),

                                        DummyId INT NOT NULL,

                                        KeyName VARCHAR(20),

                                        CONSTRAINT pk_tblIsNonClusteredPKUnique_Id

                                                   PRIMARY KEY NONCLUSTERED (Id)

                                       );

GO
–Check the types of Indexes that have been created

SELECT si.object_id,

       OBJECT_NAME(si.object_id) AS ObjectName,

       si.name,

       si.index_id,

       si.type,

       si.type_desc,

       si.is_unique,

       si.is_unique_constraint,

       si.is_primary_key

FROM sys.indexes AS si

WHERE si.object_id = OBJECT_ID(‘tblIsNonClusteredPKUnique’);

GO

As can be seen from the output from sys.indexes, we can confirm that: The Primary Key, although non-clustered, was created as a unique index. From an academic perspective, notice that the table was created as a heap, but that is the subject of another discussion.

Clustered Indexes may not be Unique

The code provided below creates a clustered index (without a primary key definition) on a test table and goes on to check the nature of the indexes created by the CREATE TABLE statement.

USE tempdb;

GO

–Safety Check

IF OBJECT_ID(‘tblIsClusteredIndexUnique’) IS NOT NULL

    DROP TABLE tblIsClusteredIndexUnique;

GO
–Create test table definition

–Notice that NO primary key has been defined

CREATE TABLE tblIsClusteredIndexUnique (Id INT NOT NULL IDENTITY(1,1),

                                        DummyId INT NOT NULL,

                                        KeyName VARCHAR(20)

                                       );

GO
–Now create a clustered index on a column other than the Primary Key

CREATE CLUSTERED INDEX idx_tblIsClusteredIndexUnique_DummyId

ON tblIsClusteredIndexUnique (DummyId);

GO
–Check the types of Indexes that have been created

SELECT si.object_id,

       OBJECT_NAME(si.object_id) AS ObjectName,

       si.name,

       si.index_id,

       si.type,

       si.type_desc,

       si.is_unique,

       si.is_unique_constraint,

       si.is_primary_key

FROM sys.indexes AS si

WHERE si.object_id = OBJECT_ID(‘tblIsClusteredIndexUnique’)

GO

As can be seen from the output from sys.indexes, we can confirm that: The clustered index was NOT created as a unique index by default.

Unique Indexes may not be Clustered

The code provided below creates a unique, non-clustered index on a test table and goes on to check the nature of the indexes created by the CREATE TABLE statement.

USE tempdb

GO

–Safety Check

IF OBJECT_ID(‘tblIsUniqueIndexClustered’) IS NOT NULL

    DROP TABLE tblIsUniqueIndexClustered

GO
–Create test table definition

–Notice that NO primary key (clustered or otherwise) has been defined

CREATE TABLE tblIsUniqueIndexClustered (Id INT NOT NULL IDENTITY(1,1),

                                        DummyId INT NOT NULL,

                                        KeyName VARCHAR(20)

                                       )

GO
–Now create a clustered index on a column other than the Primary Key

CREATE UNIQUE NONCLUSTERED INDEX idx_tblIsUniqueIndexClustered_DummyId

ON tblIsUniqueIndexClustered (DummyId);

GO
–Check the types of Indexes that have been created

SELECT si.object_id,

       OBJECT_NAME(si.object_id) AS ObjectName,

       si.name,

       si.index_id,

       si.type,

       si.type_desc,

       si.is_unique,

       si.is_unique_constraint,

       si.is_primary_key

FROM sys.indexes AS si

WHERE si.object_id = OBJECT_ID(‘tblIsUniqueIndexClustered’);

GO

As can be seen from the output from sys.indexes, we can confirm that: The non-clustered index was created as a unique index due to the use of the UNIQUE keyword.

Conclusion

In conclusion, I would present the following points:

• A primary key is always unique
  
• A primary key may or may not be clustered
  
• A clustered index may not be unique
  
• A unique index may not be clustered
  
• The UNIQUE keyword is essential for defining an index as unique, unless it is an index created as a result of the primary key definition

Further Reading

• Primary keys may not be clustered [Link]
  
• Clustered Index Design Recommendations [Link]
  
• sys.indexes [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Here’s an interesting observation I made when responding to one of the forums/Twitter feeds yesterday. As most of you know, SQL Server 2012 introduced a couple of new T-SQL functions. We will focus our attention to two of these today:

• TRY_CAST
  
• TRY_CONVERT

The basic functionality of both functions is the same – to check if a supplied value in one data-type can be successfully CAST/CONVERT to another data-type. Both functions return a value cast to the specified data type if the cast succeeds; otherwise, return null. This begs the question – What’s the difference between the two functions?

From a syntax perspective, both TRY_CAST and TRY_CONVERT have parameters similar to the CAST and CONVERT functions respectively. To demonstrate the functionality, I have taken the example from Books On Line as the base and modified it a bit:

/* STEP 01 - Demonstrate the functionality of TRY_CAST v/s TRY_CONVERT */
USE tempdb;
GO
SET NOCOUNT ON;

SELECT 
    CASE WHEN TRY_CAST('test' AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CAST(2013 AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS IntToFloat;
GO

SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CONVERT(float, 2013) IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS IntToFloat;
GO
/********************
RESULTS
********************/
/*
CharToFloat IntToFloat
----------- --------------
Cast failed Cast succeeded

CharToFloat    IntToFloat
-------------- -----------------
Convert failed Convert succeeded
*/

As can be seen from the results above, no error/exception was thrown during a failed CAST/CONVERT attempt. Our code was gracefully able to handle the error.

Now, let us study a couple of interesting points about these new functions:

TRY_CAST is NOT a keyword, TRY_CONVERT is

Colours are some of the most expressive elements of any user interface. If we look at the query editor window in the SQL Server Management Studio, we see that TRY_CONVERT is shown the default pink colour to indicate a keyword (in SQL 2012) whereas TRY_CAST is not indicated as a keyword.

Even Intellisense tells us that “’TRY_CAST’ is not a recognized built-in function name.”

TRY_CAST works with all compatibility levels on SQL 2012, TRY_CONVERT needs 110

A simple test shows us that TRY_CAST works with all compatibility levels supported on SQL Server 2012. However, the TRY_CONVERT keyword requires a compatibility level of 110.

USE master;
GO
--Safety Check
IF DB_ID('TRY_CAST_CONVERT_Testing') IS NOT NULL
BEGIN
    ALTER DATABASE TRY_CAST_CONVERT_Testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TRY_CAST_CONVERT_Testing;
END
GO

CREATE DATABASE TRY_CAST_CONVERT_Testing;
GO

--Let's take the database to the lowest supported level
ALTER DATABASE TRY_CAST_CONVERT_Testing SET COMPATIBILITY_LEVEL = 90;
GO

USE TRY_CAST_CONVERT_Testing;
GO
--Now, create a table:
CREATE TABLE dbo.TRYCASTCONVERT (AttemptId INT,
                                 FailingValue VARCHAR(10),
                                 PassingValue INT
                                );
GO
-- Insert some test data
INSERT INTO dbo.TRYCASTCONVERT (AttemptId, FailingValue, PassingValue)
VALUES (1, 'test', 7);
GO

USE TRY_CAST_CONVERT_Testing;
GO
--Execute the test
SET NOCOUNT ON;

SELECT 
    CASE WHEN TRY_CAST(FailingValue AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CAST(PassingValue AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS IntToFloat
FROM dbo.TRYCASTCONVERT;
GO

SELECT 
    CASE WHEN TRY_CONVERT(float, FailingValue) IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CONVERT(float, PassingValue) IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS IntToFloat
FROM dbo.TRYCASTCONVERT;
GO

--Cleanup
USE master;
GO
IF DB_ID('TRY_CAST_CONVERT_Testing') IS NOT NULL
BEGIN
    ALTER DATABASE TRY_CAST_CONVERT_Testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TRY_CAST_CONVERT_Testing;
END
GO

/***********************
RESULTS
***********************/
/*
CharToFloat    IntToFloat
-------------- --------------
Cast failed    Cast succeeded


Msg 195, Level 15, State 10, Line 3
'TRY_CONVERT' is not a recognized built-in function name.
*/

The result is that while the call to TRY_CAST succeeds, the call to TRY_CONVERT returns an error – Msg 195 – ‘TRY_CONVERT’ is not a recognized built-in function name.

TRY_CAST can work on Azure as well, TRY_CONVERT can’t

This one should not come as a surprise at all. Because CAST is an ANSI standard (and therefore available in Azure),  the corresponding exception-handled version (TRY_CAST) would be easier to wire up in the Azure platform.

Internal implementation – TRY_CONVERT is implemented as TRY_CAST

We know that in Microsoft SQL Server, CAST is internally implemented as a CONVERT. Keeping the Include Actual execution plan option on (Ctrl+M), I conducted a test for TRY_CAST and TRY_CONVERT.

USE AdventureWorks2012;
GO
SET NOCOUNT ON;

SELECT 
    CASE WHEN TRY_CONVERT(float, ProductNumber) IS NULL 
    THEN 'Convert failed'
    ELSE 'Convert succeeded'
END AS Result
FROM Production.Product;
GO

As can be seen from the properties window, contrary to our observations about CAST and CONVERT, we realize that TRY_CONVERT is actually implemented as a TRY_CAST internally!

The remaining question…

With these observations, I now have a final question:

Why do TRY_CAST and TRY_CONVERT exhibit a behaviour opposite to that of CAST and CONVERT?

Do leave your thoughts in the blog comments as you continue with the day.

Further Reading

• CAST v/s CONVERT [Link]
  
• TRY_CAST [Link]
  
• TRY_CONVERT [Link]

Until we meet next time,

Be courteous. Drive responsibly.

In any practice, misconceptions are always around. In the case of SQL Server, one of the misconceptions that never seems to go away is that stored procedures are always pre-compiled and ready for use.

I recently ran into a developer who was scratching his head because SQL Server did not prevent him from deploying a stored procedure which clearly had a script that would fail to parse. I plan to use today’s post to prove to anyone I come across who harbours this myth that:

Stored procedures are not compiled at creation time – they are compiled during the first execution.

The proof

In the script below, I create a stored procedure that attempts to select data from a table that does not exist. Notice that Intellisense is clearly telling me that the table does not exist.

USE tempdb;
GO
IF OBJECT_ID('dbo.proc_WhenAreProceduresCompiled','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_WhenAreProceduresCompiled;
GO

CREATE PROCEDURE dbo.proc_WhenAreProceduresCompiled
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.SomeTableThatDoesNotExist;

    --The following can also be used for the test
    --SELECT 1/0
END;
GO

Executing this script gives the first confirmation – the stored procedure is created successfully, and it can be confirmed by the fact that we get a valid OBJECT_ID() value:

USE tempdb;
GO
--Confirm that the Stored Procedure is indeed created
SELECT OBJECT_ID('dbo.proc_WhenAreProceduresCompiled','P') AS ObjectId;
GO

/************************
ObjectId
-----------
821577965
************************/

Attempting to execute the stored procedure, however fails with the missing object error.

USE tempdb;
GO
--Attempt to execute the stored procedure
EXEC dbo.proc_WhenAreProceduresCompiled;
GO

Msg 208, Level 16, State 1, Procedure proc_WhenAreProceduresCompiled, Line 7
Invalid object name ‘dbo.SomeTableThatDoesNotExist’.

This test is a basic confirmation of the fact that stored procedures are compiled when they are executed for the first time, not at the time of object creation.

Until we meet next time,

Be courteous. Drive responsibly.