To create a database in SQL Server using the GUI:
- From the Object Explorer, right click on Databases and select New database...
- Name the database, adjust settings if required, then click OK
Once you’ve created the database, you can create tables and other database objects. You can also modify any database properties as required.
Below are screenshots for the above steps.
The Object Explorer
From the Object Explorer, right click on Databases and select New database...:
Name the Database
Name the database, adjust settings as required, then click OK:
The database will now appear in the Object Explorer under Databases.
Database Properties
You can adjust the database properties by right-clicking on the database in the Object Explorer:
This brings up the Database Properties dialog:
The Model Database
When you create your database, it uses the model database as a template.
The model database is created when you install SQL Server. You can see the database in the list of databases in the Object Explorer even before you create your first database.
If you create new databases regularly, and immediately find yourself having to modify them with the same changes each time, you can modify the model database to incorporate those changes. You will no longer need to modify every database you create with the same changes.
For example, you could set permissions, add objects such as tables, functions, or stored procedures, and more.
Restrictions of the Model Database
However, there are some tasks that can’t be performed on the model database. As of SQL Server 2016 these are:
- Adding files or filegroups.
- Changing collation. The default collation is the server collation.
- Changing the database owner. model is owned by sa.
- Dropping the database.
- Dropping the guest user from the database.
- Enabling change data capture.
- Participating in database mirroring.
- Removing the primary filegroup, primary data file, or log file.
- Renaming the database or primary filegroup.
- Setting the database to OFFLINE.
- Setting the primary filegroup to READ_ONLY.
- Creating procedures, views, or triggers using the WITH ENCRYPTION option. The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.