question

JohnCouch-7779 avatar image
0 Votes"
JohnCouch-7779 asked OuryBa-MSFT commented

Name resolution in Azure SQL Database

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...

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered OuryBa-MSFT commented

Hello @JohnCouch-7779 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


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

0 Votes 0 ·

104991-screenshot-2021-06-11-172710.png



This is a subset of actual functionality.

0 Votes 0 ·

@JohnCouch-7779 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



0 Votes 0 ·
Show more comments