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.