Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

Question

SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them from the actions of the script.

Also, if I run SELECT name, schema_id FROM sys.schemas; on a new user database, I get:

name                 schema_id
dbo                  1
guest                2
INFORMATION_SCHEMA   3
sys                  4
db_owner             16384
db_accessadmin       16385
db_securityadmin     16386
db_ddladmin          16387
db_backupoperator    16389
db_datareader        16390
db_datawriter        16391
db_denydatareader    16392
db_denydatawriter    16393

I ran that query on two different instances, one a SQL Server 2016 and the other a SQL Server 2005, and both returned that same result.

Questions:

1. Is there any situation (or sql server version) in which the system databases master, tempdb,
   model and msdb would have a database_id other than 1,2,3,4
   respectively?
2. Can I really trust on the fact that the schemas I listed will always have the same IDs on any instance of SQL Server so that I can write maintenance scripts based on those IDs?

asked 2020-02-12 by Ronaldo

---

Answer

Practically speaking, those IDs are going to be the same for all currently released versions of SQL Server.

Note that the distribution & SSISDB databases are also system
databases, but will not have a predictable database_id.

However, there are no guarantees that this will always be the case, or that this will continue to be the case in the future. Microsoft does not provide documentation nor guarantees that these IDs are always the same. There are guarantees that someone has gone into a database and messed with these system schemas.

In the next version of SQL Server, the Product team could decide to make a change to the ID number scheme. That change could be backported to existing versions in a Service Pack or Cumulative Update.

This might be unlikely in the near future, so you might not care to worry about it. But if you write awesome code, your code may still be in use a decade or two from now, when this does happen. And then, your successors will have a rat’s nest to untangle, and your code ceases to be “awesome code.”

Future changes aside, code that depends on cryptic, hard coded ID values is not readable. Not everyone knows that schema_id > 16384 indicates a range of special schemas. (I’ve been using SQL Server since the turn of the century and I would be confused by that line of code.)

Make your code read like prose, and you’ll have better, more maintainable code.

answered 2020-02-12 by Andy Mallon