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]
CREATE DATABASE [Nova_databaze]
( 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 )
( 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 )

Creating a Database via SQL Server Management Studio

Right-click on Database – New Database

Založení databáze v SQL Server management studio

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)

Vytvoření databáze v SQL Server management studio - krok 2

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.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *