azure sql DB_ID doesn't work

Question

I have created manually a database called SnapshotsDb in Azure Sql Server via SSMS.

Trying to get the ID of the SnapshotsDb using DB_ID() function:

DECLARE @db_id int;  
SET @db_id = DB_ID(N'SnapshotsDb')
PRINT @db_id

Not output

If I try same logic on a local instance of sql server, it does return an int which matches the ID of the database.

In Azure SQL the Database ID makes no sense - you don't have access to multiple databases or the server, you only have access to a *single* database. — Panagiotis Kanavos, Jun 19 '18 at 09:28
In any case you should use `SELECT` instead of `PRINT`.PRINT doesn't return results, it returns messages that won't appear in the result sets. You'd have to ask for information messages explicitly using the client's API. With ADO.NET you'd have to handle the [InfoMessage](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.infomessage%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396) event — Panagiotis Kanavos, Jun 19 '18 at 09:30
@PanagiotisKanavos I'm not sure what do you mean by not having access to multiple databases. I'm connecting via SSMS and see all the databases — Cristian E., Jun 19 '18 at 09:35
@PanagiotisKanavos But i see your point of Database ID being unreliable. When a function like DB_ID is not supported by azure sql i'd expect to see an exception or at least some docs. Thanks for help! — Cristian E., Jun 19 '18 at 09:50
You missed the point. `DB_ID` isn't unreliable. It's meaningless. There's no reason it should return anything other than a NULL in this case. What do you want to do with that `DB_ID` anyway, it can't be used to identify the database when there's only ONE database — Panagiotis Kanavos, Jun 19 '18 at 10:44
According to this [MSDN](https://learn.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql?view=sql-server-2017#examples-includesssdwfullincludessssdwfull-mdmd-and-includesspdwincludessspdw-mdmd) it is viable to use `DB_ID` in the context of Azure SQL. Is the documentation wrong? Can you please file an answer so the community benefits as well from your expertise. — Cristian E., Jun 19 '18 at 10:54
@CristianE., I agree the limitations of `DB_ID()` for Azure SQL Database should be called out in the doc. See this [Related question](https://dba.stackexchange.com/questions/138809/database-id-for-our-database-in-sys-databases-does-not-match-that-of-db-id-o). The database id is transient in the Azure SQL Database world so it's of limited use. — Dan Guzman, Jun 19 '18 at 10:55
@CristianE. did you try `SELECT DB_ID()` instead of `PRINT`? — Panagiotis Kanavos, Jun 19 '18 at 10:56
@PanagiotisKanavos Yes, it returns a row with a null entry. P.S. The `PRINT` was used for demo purposes, but it seems it caused more confusion than it helped. — Cristian E., Jun 19 '18 at 11:14

score 4 · Accepted Answer · answered Jun 19 '18 at 11:13

If you need the database_id of a database you need to query the sys.databases system view and filter on the WHERE clause by the name of the database. The database_id filed in sys.databases is consistent and won’t change in the life time of the database.

You cannot rely on DB_ID() since the function may return different values depending on which of the databases you run it as part of a geo-replication setup. You can read more here.

azure sql DB_ID doesn't work

1 Answers1