Database IDs
- by fatherjack
Just a quick post, mainly to test out the new blog format but related to a question on the #sqlhelp hashtag.
The question came from Justin Dearing (@zippy1981) as:
So I take it database_id isn’t an ever incrementing value. #sqlhelp
When a new database is created it is given the lowest available ID. This either is in a gap in IDs where a database has been dropped or the database ID is incremented by one from the highest current ID if there are no gaps to fill.
To see this in action, connect to your sandbox server and try this:
USE MASTER
GO
CREATE DATABASE cherry
GO
USE cherry
GO
SELECT DB_ID()
GO
CREATE DATABASE grape
GO
USE grape
GO
SELECT DB_ID()
GO
CREATE DATABASE melon
GO
USE melon
GO
SELECT DB_ID()
GO
USE MASTER
GO
DROP DATABASE grape
GO
CREATE DATABASE kiwi
GO
USE kiwi
GO
SELECT DB_ID()
GO
USE MASTER
GO
DROP DATABASE cherry
DROP DATABASE melon
DROP DATABASE kiwi
You should get an incrementing series of database IDs as the databases are created until the last one where the new database gets allocated the ID that is missing because one was dropped.
