Why use master to create a database?

Question

I have a short question, why do I use use master; to create a database? Here is the example from the Microsoft documentation

USE master ;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\...\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,  
    FILENAME = 'C:\Program Files\...\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;

score 22 · Accepted Answer · answered Oct 28 '18 at 16:31

It is absolutely not a requirement in this very specific case, but it is a requirement in many other scenarios. If you're creating a database called Sales, and you arelady have a database called Sales, you'll need to change your database context before you:

Restore with replace; or,
Drop the current database and then:
- Create from scratch; or,
- Create for attach.

There are plenty of other scenarios outside of database creation that also require either (a) not being in the context of the current database, or (b) being in the context of master specifically (or at least not a specific database), and many of these things you may be doing during or around creating databases:

Setting a database to a different state, like single_user
Preventing errors when a script has a USE command but that user database may be offline or otherwise inaccessible
Granting server-level permissions like CREATE DATABASE
Granting server-level role membership
Marking a module as a system object (sp_MS_marksystemobject) or as a startup procedure
Certain types of certificate, server audit, and Availability Group operations

Probably a slew of other things. USE master; isn't always necessary, but sometimes it is, and it doesn't hurt to always execute server-level commands from that database.

score 17 · Answer 2 · answered Oct 28 '18 at 11:29

I believe it's not a requirement that you should use the master database to create a database. Since the create database command should be run in a database context the documentation always uses a default database which is master and it's a system database which will be always there no matter what so the script doesn't fail!

score 4 · Answer 3 · answered Oct 28 '18 at 16:32

I believe that we, a very very long time ago, had to be in master when executing the CREATE DATABASE command. I'm too lazy to fire up some 20 years old version to verify. So probably a combination of that and that it "feels natural" to be in master. Like if you are to create a folder (imagine there are just one level of folders), you probably feel "better" to say CD \ first (comparing the root here with master database).

Why use master to create a database?

3 Answers3