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.

Renaming a SQL Server object is a fairly common operation when working with a product. Overtime, objects need to be enhanced as and when new features are introduced. One of the most frequently used mechanisms to rename objects is to use the system stored procedure: sp_rename.

However, this system stored procedure comes with a word of caution – it is not applicable to all SQL Server object types. Here’s an example:

Renaming a Column

To demonstrate the renaming of a column using sp_rename, let us first create a simple table with 2 columns:

USE tempdb;

GO

–Safety Check

IF OBJECT_ID(‘dbo.Student’,’U’) IS NOT NULL

    DROP TABLE dbo.Student;

GO
–Create the test Table

CREATE TABLE dbo.Student (StudentId INT IDENTITY(1,1),

                          SchoolId INT,

                          CONSTRAINT pk_StudentId PRIMARY KEY CLUSTERED (StudentId)

                         );

GO

Let us now check the table properties:

USE tempdb;

GO

–Check the table values

SELECT sc.name,

       sc.object_id,

       sc.column_id

FROM sys.columns AS sc

WHERE sc.object_id = OBJECT_ID(‘dbo.Student’,’U’);

GO

Now, let us rename the column – SchoolId to EstablishmentId and check the table properties again.

USE tempdb;

GO

–Rename a column: SchoolId to EstablishmentId

sp_rename ‘dbo.Student.SchoolId’,’EstablishmentId’,’COLUMN’;

GO
–Check if the column has been renamed

SELECT sc.name,

       sc.object_id,

       sc.column_id

FROM sys.columns AS sc

WHERE sc.object_id = OBJECT_ID(‘dbo.Student’,’U’);

GO

As can be seen from the screenshot above, the column renaming was successful. Now, let us extend this example to rename a stored procedure.

Renaming a Stored Procedure

Using sp_rename

Let us first create a new stored procedure using the table created above.

USE tempdb;

GO

–Creat the test procedure

CREATE PROCEDURE dbo.proc_GetStudents

AS

BEGIN

    SELECT s.StudentId,

           s.EstablishmentId

    FROM dbo.Student AS s;

END;

GO

Let us take a look at the object definition in the sql_modules:

USE tempdb;

GO

–Check the procedure properties

SELECT sm.object_id AS ObjectId,

       OBJECT_NAME(sm.object_id) AS ProcedureName,

       sm.definition AS ObjectDefinition

FROM sys.sql_modules AS sm;

GO

Now, let us rename the stored procedure using sp_rename and check the stored procedure properties.

USE tempdb;

GO

–Rename the procedure from proc_GetStudents to proc_GetAllStudents

sp_rename ‘proc_GetStudents’,’proc_GetAllStudents’,’OBJECT’;

GO
–Check the procedure properties

SELECT sm.object_id AS ObjectId,

       OBJECT_NAME(sm.object_id) AS ProcedureName,

       sm.definition AS ObjectDefinition

FROM sys.sql_modules AS sm;

GO

As can be seen from the screenshot above, SQL Server is left in a state of partial meta-data update. This is therefore, not the correct way to rename a stored procedure.

The Correct Way to Rename a Stored Procedure

The correct way to rename a stored procedure is to drop and recreate the object. To demonstrate this, let us re-create the stored procedure first.

USE tempdb;

GO

IF OBJECT_ID(‘dbo.proc_GetStudents’,’P’) IS NOT NULL

    DROP PROCEDURE dbo.proc_GetStudents;

GO
IF OBJECT_ID(‘dbo.proc_GetAllStudents’,’P’) IS NOT NULL

    DROP PROCEDURE dbo.proc_GetAllStudents;

GO
–Creat the test procedure

CREATE PROCEDURE dbo.proc_GetStudents

AS

BEGIN

    SELECT s.StudentId,

           s.EstablishmentId

    FROM dbo.Student AS s;

END;

GO
–Check the procedure properties

SELECT sm.object_id AS ObjectId,

       OBJECT_NAME(sm.object_id) AS ProcedureName,

       sm.definition AS ObjectDefinition

FROM sys.sql_modules AS sm;

GO

Let us now drop and recreate the procedure:

USE tempdb;

GO

–Rename the procedure from proc_GetStudents to proc_GetAllStudents

IF OBJECT_ID(‘dbo.proc_GetStudents’,’P’) IS NOT NULL

    DROP PROCEDURE dbo.proc_GetStudents;

GO

–Creat the test procedure

CREATE PROCEDURE dbo.proc_GetAllStudents

AS

BEGIN

    SELECT s.StudentId,

           s.EstablishmentId

    FROM dbo.Student AS s;

END;

GO
–Check the procedure properties

SELECT sm.object_id AS ObjectId,

       OBJECT_NAME(sm.object_id) AS ProcedureName,

       sm.definition AS ObjectDefinition

FROM sys.sql_modules AS sm;

GO

As can be seen from the screenshot above, the stored procedure rename has now succeeded.

Conclusion

In conclusion, the following thumb rules can be established:

• The sp_rename can be used to rename a column, database, index, statistics and/or user defined data types
  
• The sp_rename can also be used to rename constraints (check, primary-key, foreign-key and unique-key), user tables and rules
  
• The sp_rename should NOT be used for renaming stored procedures, functions, views and triggers. They MUST be dropped and recreated

Further Reading

• sp_rename [Link]

Until we meet next time,

Be courteous. Drive responsibly.