To create a database in SQL Server using the GUI:

  1. From the Object Explorer, right click on Databases and select New database...
  2. 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...:

Screenshot of right-clicking on the Object Explorer in SQL Server 2014.

Right-clicking on an option in the Object Explorer usually brings up a contextual menu that enables you to choose an option.

Name the Database

Name the database, adjust settings as required, then click OK:

Screenshot of the New Database dialog in SQL Server 2014

The “New Database” dialog in SQL Server 2014. Here you provide a name for the database and adjust other settings if required.

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:

Screenshot of right-clicking on a database in the Object Browser in SQL Server 2014.

Right-clicking on the database in the Object Explorer opens a contextual menu that you can select properties and other options from.

This brings up the Database Properties dialog:

Screenshot of Database Properties dialog in SQL Server 2014.

The Database Properties dialog box allows you to configure the properties of your database. You can find a property by selecting the applicable page from the left menu.

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.