A database is a system of files and objects that are organized and linked within the database through keys. In SQL Server databases, you can find objects such as tables, views, functions, procedures, triggers, and more. Essentially, there are two ways to create a database. The first option is to create the database using the SQL management studio, or you can create it using a script.

Creating a Database via SQL Script

This is a more convenient way to create a database. Using a script, you essentially perform the same setup as creating a database through the Management Studio (see below).

Description of the database creation script:

• A new database is created using the CREATE DATABASE clause.
• The ON PRIMARY command specifies that the data file is located in the primary Filegroup. Following that is the code section within parentheses where you define properties of the database file where data will be stored:
  • Database name (NAME)
  • Path to the database data file (FILENAME)
  • Initial size of the database in MB (SIZE)
  • Maximum size of the database in MB (MAXSIZE) – or optionally, unlimited (UNLIMITED)
  • Database growth limit – Autogrowth (FILEGROWTH) – i.e., by how much the data file should increase when it reaches its size limit
• The LOG ON command means that you define a log file for the transaction log. In parentheses, you specify the same file properties as in the previous step.

USE [master]
GO
CREATE DATABASE [Nova_databaze]
ON PRIMARY
( NAME = N'Nova_databaze', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Nova_databaze.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'Nova_databaze_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Nova_databaze_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Creating a Database via SQL Server Management Studio

Right-click on Database – New Database

Database Properties

When the window opens, you set database properties. On the “General” page, enter the Database name, and you can leave the owner as default (the owner will be the user creating the database). You’ll notice that when you create a database, two physical files are created:

• The primary database file (.mdf)
• The log file containing the transaction log (.ldf)

For the main file, consider how much data will be stored in it in the long run and set:

1. Initial file size in MB (Initial Size)
2. Autogrowth – in case the file size is exhausted, Autogrowth determines how much the file should increase. There are two ways to set Autogrowth:
   • Fixed increase in MB
   • Percentage increase (not recommended)

Database Collation

On the “Options” page, you set Collation and recovery model (Full, Bulk-logged, or Simple). You don’t need to set anything else, and the new database is configured. You can confirm with OK.

Note: On the Filegroups page, you can create multiple Filegroups and assign database files to them (see step 2). Multiple filegroups are used, for example, in partitioning tables when you want to archive part of the table to a different physical location and speed up queries for more frequently used data.