Monday, November 14, 2011

DBIDs are not consistent always...


Today one of fellow developers asked me a question on db_id which prompted me to do some analysis and find out the discovery which I'm sharing here. Database ids will not always point to same database! Before you get fully shocked let me clarify.Database ids will get reassigned if original dbs associated to them are deleted.
To illustrate this fact please see example below.


As you see from above sys.databases lists the available databases in the server.

Now suppose I add a new db test123 to server and deleting databases test and newtest.




Now lets see what has happened in sys.databases.


As you see from above, the id of the deleted database test gets reclaimed and assigned to newly created db test123. So this clearly implies that the database ids always doesnt point to same db.
So keep in mind that db_id() values wont always mean its same database and avoid any check using db_id function but rather use db_name function for all the checking.