Name resolution in Azure SQL Database

John Couch 181

We all know you are not supposed to name your object sp_ in SQL Server as it is typically reserved for system objects and will normally result in a master database search first, HOWEVER, seeing how Azure SQL Database is not a typical SQL server installation, is that still the case?

Not looking for lectures on why does it matter just don't do it, I am curious if it still works the same or not. I realize its technically still a SQL Server with restrictions to limit you to a scoped database and using resource governor, but...

2 answers

Alberto Morillo 33,536 Reputation points MVP

2021-06-11T22:49:03.397+00:00

I tested with a stored procedure. I copy the stored procedure with the same name but I added the "sp_" prefix, so I have one copy of the stored procedure with the prefix and the other one without the prefix. To make sure I made the test on equal conditions I run both of them with a cold memory (after scaling up and down the service tier) and after clearing the procedure cache.

The elapsed execution time of the stored procedure with or without the the prefix was consistently 77ms or 78 ms.

Based on my limited test the performance impact of using "sp_" prefix that we see on SQL Server instances, does not apply on Azure SQL Database.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Oury Ba-MSFT 18,601 Reputation points Microsoft Employee

2021-06-11T23:32:40.61+00:00

Hello @John Couch Thank you for posting your question. It is, Azure SQL DB is built on the same engine as SQL Server.
Find more information in this document. Please accept answer if it helpful
Regards,
Oury
Please sign in to rate this answer.
John Couch 181 Reputation points

2021-06-12T00:17:50.887+00:00

Yes, it's built using the same query engine, but there is no concept of cross database querying, and there is significant other differences in functionality. Even accessing master requires you to disconnect and reconnect in SSMS. The fact that you cannot create a test procedure in master to check for cache miss when calling from your user database makes me question it even more. So, while the general query engine works (roughly) the same, is it really performing a cache miss and checking master?

John Couch 181 Reputation points

2021-06-12T00:28:33.69+00:00

This is a subset of actual functionality.

Oury Ba-MSFT 18,601 Reputation points Microsoft Employee

2021-07-01T00:31:44.967+00:00

@John Couch Sorry for the late reply on this. I was working with our product group to get more insights on the above.

Creating user objects in master database, In SQL Server, user with sysadmin permissions can do that in the system database. In SQLDB, customer does not get sysadmin rights. so creating user objects in master (which is bad practice) is not possible. Only objects like logins can be created in master in SQLDB.SQLMI does allow customer to provision logins with sysadmin privileges so it can work like in SQL Server.
Please let me know if you have further queries

Kind Regards,
Oury

John Couch 181 Reputation points

2021-07-01T01:01:23.297+00:00

Hi Oury,

I understand the lack of access to master, but when you call a procedure

EXEC dbo.sp_XYZ

Does SQL Server look in the master database first if I am not the owner or have dbo rights? i.e. Will there be an initial cache miss during name resolution?

Oury Ba-MSFT 18,601 Reputation points Microsoft Employee

2021-07-08T01:01:28.503+00:00

Hi @ JohnCouch-7779 I am not sure because we don't document those details. The sp_prefix is meant for system stored procedures and user objects shouldn't use that name.
Regards,
Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Name resolution in Azure SQL Database

2 answers

Your answer