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.