What's the database id with MSSQL

  • Microsoft numbers all the databases as they are created. So the first one is 1, then 2...
  • The database_id is important because it is used all over by the dynamic management views.
  • The dynamic management views are used tell you how the SQL Server is doing. With that you can:
    1. Monitor the health
    2. Diagnose problems
    3. Tune performance.

Database_Id

  1. SELECT SUBSTRING(db.name,1,30) AS Database_Name,
  2.        db.database_id AS Database_ID
  3. FROM sys.databases db
  4. ORDER BY Database_ID;
  5. go
Database_Name                  Database_ID
------------------------------ -----------
master                         1
tempdb                         2
model                          3
msdb                           4
ReportServer                   5
ReportServerTempDB             6
AdventureWorksDW               7
AdventureWorks                 8
AdventureWorksLT               9
sql911                         10

(10 row(s) affected)

Please note the order of the database creation:

  1. master
  2. tempdb
  3. model
  4. msdb

Tempdb may have a number greater than 2 if tempdb was dropped and recreated at a later date.

Applies to:

  • Microsoft SQL Server 2005