SQL Create Database Valuable Tutorial with T-SQL and SSMS

Problem

There is always the need to create a new database in a SQL Server and in this article, we look at how this can be done using the SSMS GUI and also using T-SQL scripts to create a SQL Server database.

Solution

This tutorial will discuss how to create a database in SQL Server. The SQL Server database can be created using SQL Server Management Studio or CREATE DATABASE statements. First, we will discuss a brief overview of SQL Server databases, followed by step-by-step instructions to create the database using both methods as well as examples.

Understanding Databases in SQL Server

In relational database management systems (RDBMS), a database stores user data. Many RDBMSs are available, including Oracle, SQL Server, MySQL, PostgreSQL, etc., but this article focuses on SQL Server.

Let’s begin with a high-level summary of SQL Server databases. They can be created with SQL Server Management Studio (SSMS) or using T-SQL CREATE DATABASE statements. SQL Server offers two types of databases: System databases and User databases.

System Databases

System databases store the details of SQL Server instances and metadata information, but not user data. SQL Server has four primary system databases:

Master: The master database contains the information of SQL Server instance, logins, linked server, and other metadata information.
Model: The model database is a template database for user-defined databases.
Msdb: The msdb database stores the details of the SQL Server agent jobs, backup and recovery details, maintenance plans, etc.
TempDB: The tempdb database stores temporary tables and table variables that are used during query execution.

SQL Server also includes other system databases created when we enable certain configurations. First, it is the distribution database that is made when we configure replication. Other system databases are report server and report server TempDB – they are created when we configure SQL Server Reporting Services (SSRS).

User Database

As the name suggests, user databases contain the application and user data of data-driven applications. This data is stored in tables, and it is accessible through T-SQL queries that are created by developers or administrators on the instance. Each user database must have a unique name.

Prerequisites and Limitations

Below is a list of prerequisites for creating a new database in SQL Server:

Any version of SQL Server must be installed and running.
Sufficient space must be available on the server.
The user who is creating a database must be granted a sysadmin or dbcreator role.
The instance service account needs the required permissions on disk to create files.

The limitations of the SQL Server database depend on the version you are using. Refer to the maximum capacity specifications documentation for information about the limitations of a SQL Server database. Finally, you can learn more about all SQL Server editions and their limitations from the editions and supported features of SQL Server documentation.

Create a SQL Server Database using SSMS

If you want to create a database with default parameters, the process is very simple. Let us create a sample database named VS_HRMS.

Open your SSMS and connect to your instance. In my case, my instance is named Nisarg-PC. Right-click on Databases and select New Database…

Create Database Screen in SSMS

Database Name

Enter the desired database name. Database names are always unique per instance. Note: If you use a space in the database name, it must be used in square brackets [Test DB] or quotes ‘Test DB’.

Owner

Specify the database owner. By default, it is the current login the connected to the SQL Server instance. Ideally, a database owner must be a dedicated SQL login with a sysadmin role. If you want to assign granular permissions, you can create a separate server role with the required permission and assign it to the SQL login that we are using as a database owner.

Database Files

Specify the database files. By default, the wizard creates one database filegroup that contains one database file and one transaction log file. Here, you can see the following parameters:

Logical Name: The default logical name of the database file is the same as the database name and default logical name of the transaction log file will be databasename_log. It is preferred to give some appropriate unique names to database files so it can be identified easily. If you are working on a server that has hundreds of database files, an appropriate logical file name will be a big help.
File Type: It is the type of database file. It can be ROWs data, Transaction Log, or FILESTREAM data. The ROW data contains the database objects and user data. The transaction log contains the record of each transaction that occurred in the database. The FILESTREAM data contains large binary data.
Filegroup: Name of the filegroup in which you want to keep the data file. The filegroup is a group of SQL Server data files. When we create a database, SQL Server creates a Primary filegroup which is default filegroup. You can also add secondary filegroups. There are many benefits of using multiple filegroups, which I will explain later.
Initial size: The initial size of the database file when the database is created. The default value is 8MB. The initial size of a database must be set based on the application workload and anticipated growth of the database. If the database file’s initial size is too small, frequent autogrowth events will occur which leads to performance issues. For our demonstration, the initial size of the VS_HRMS data file is 1GB and the transaction log file is 512 MB.
Autogrowth: The autogrowth is amount of space to be allocated when the data file is full. For example, the autogrowth parameter is 512 MB. Now, when the data file is full, SQL Server automatically allocates 512MB to it. Autogrowth value can be set in percentage (20%) or fixed size (X MB/GB). It is advisable to keep it fixed size to avoid physical fragmentation and ensure uniform growth.
Maxsize: The maxsize parameter is the maximum amount of space that a data file can grow. You can set the maxsize as unlimited, or you can set it to a fixed size. Usually, we keep the maxsize value unlimited. In this example, the maxsize of VS_HRMS database is unlimited.
Path: It is the location of the database file. The datafiles of the VS_HRMS database are in D:\HRMSDB\DATA\, and the transaction log file of the VS_HRMS database is in D:\HRMSDB\LOG\.

Here is the screenshot for the VS_HRMS database.

Additional Options

If you want to configure additional options, click Options.

Collation

Collation is a set of rules that determine how data will be sorted and compared. SQL Server supports numerous collation options to store the data as per your requirements.

Recovery Model

The recovery model controls how transactions will be logged and stored in the database transaction log. It has a major impact on performance and database recovery. The database RTO and RPO are dependent on the recovery model used. SQL Server supports three recovery models:

Full
- It logs and maintains all completed transactions in transaction log files.
- We perform point-in-time recovery with minimal or no data loss. The logged transactions will be removed once we take a transaction log backup.
- If we are using any HA and DR features of SQL Server like database mirroring, log shipping, or always on availability group, the database must be in full recovery model.
- The transaction logs must be sized and monitored properly.
- We must take the backup of transaction logs frequently so the transaction logs do not get full and make the database inaccessible.
- The index maintenance must be configured and scheduled carefully because it is a logged operation, and if the indexes are larger, it might fill the transaction log files and might lead to database unavailability.
Simple
- Once the transaction is complete, the SQL Server automatically removes transaction records from transaction log files.
- In case of failure, we can restore the last successful differential backup. If the database is not mission-critical (Production) or is used only for business reports, you can use a simple recovery model with hourly differential backups.
Bulk Logged
- Like the full recovery model, the bulk insert, update, and delete are minimally logged. The bulk-logged recovery model is idle for the databases in which you want to perform point-in-time recovery and optimize the bulk operations.
- The transaction log files start growing, and it also causes performance issues.
- If you are planning to create a database in which you run bulk operations frequently, it is advisable to use a bulk-logged recovery model.

You can select from the Recovery model drop-down box:

Compatibility Level

The compatibility level shows which version of SQL Server is installed on your server. The default value is the compatibility level of your installed version. You can change it if needed. Compatibility level must be chosen based on the application requirement, because as a new SQL Server version is introduced, few syntaxes and features become deprecated. Also, sometimes it causes performance issues. So, before you change the compatibility level, make sure you perform the required testing.

Containment Type

The contained database is an isolated database that has users but does not have logins associated with them. The Containment type drop-down box includes Yes or No options.

Additional advanced options that help configure the database are based on the version and edition of the SQL Server that you are using. You can read Database Properties (Options Page) to know more about various configuration parameters and their impact on SQL Server’s performance. For the VS_HRMS database, I am using only the default configuration.

Click OK to create a database.

Once the database is created, you can view the details by executing the following query:

use master
go
select sd.name, sp.name, sd.create_date
from sys.databases sd 
inner join sys.server_principals sp 
  on sd.owner_sid=sp.sid 
where database_id >5
order by sd.create_date des

Output

As you can see, the VS_HRMS database has been created.

Creating a SQL Server Database Using T-SQL

The CREATE DATABASE command is used to create a new database in SQL Server. Using the CREATE DATABASE statement helps to automate the deployment process. For example, you are deploying a database application, and as per requirement, the database configuration must follow the standard parameters. For such use cases, the deployment process can be automated much easier.

As in the previous scenario, SQL Server can do it with the default options or allow you to provide additional specifications for the database. Let us review these use cases.

Example 1: Create a Database with Default Settings

The syntax of the T-SQL query applied to create a database with default settings is as follows:

CREATE DATABASE HRMS_Patients

This query creates a new database named HRMS_Patients with the default options.

Example 2: Create a Database with Multiple Database Files and File Groups

There are many benefits of creating a database in multiple file groups:

If we use multiple filegroups, we can get benefits of parallel I/O operations which helps to improve the performance of read and write operations.
Multiple filegroups can be placed on different storage which helps to improve performance. For example, you can place OLTP tables on separate filegroups and move the filegroups to faster storage like SSDs.
Data management is easy because you can move tables and indexes across filegroups.
You can get the flexibility to perform index maintenance on tables of specific filegroups.
You can get benefits from the backup and restore process. SQL Server allows you to backup and restore a specific filegroup. To learn more about it, read the Back-Up Files and Filegroups and Restore Files and Filegroups documentation.

When you create a database with multiple filegroups, you must add a ‘FILEGROUP <filegroup name>’ clause in the CREATE DATABASE statement. The syntax is:

CREATE DATABASE DB_Name
ON
  PRIMARY
  (
    NAME = logical_name_Primary_filegroup_file,
    FILENAME = 'file_path_for_primary_file'
  ),
  FILEGROUP FG_1
  (
    NAME = logical_name_FG_1_File,
    FILENAME = 'Location_FG_1_File'
  ),
  FILEGROUP FG_2
  (
    NAME = logical_name_FG_2_File,
    FILENAME = ' Location_FG_2_File'
  )
LOG ON
(
  NAME = logical_name_for_log_file,
  FILENAME = 'log_file_path'
);

Create Database Example with Multiple Filegroups

Now, let us see a simple example.

Suppose we want to create a database named HRMS_Medical_Store that has multiple filegroups. The CREATE DATABASE statement is written as follows:

CREATE DATABASE [HRMS_Medical_Store]
ON PRIMARY
( NAME = N'HRMS_Medical_Store',     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Medical_Store.mdf' ),
 FILEGROUP [HRMS_Medicine]
( NAME = N'HRMS_Medicines',          FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Medicines.ndf'),
 FILEGROUP [HRMS_Medicine_Stock]
( NAME = N'HRMS_Medicine_Stock',    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Medicine_Stock.ndf'),
 FILEGROUP [HRMS_Patient_Receipts]
( NAME = N'HRMS_Patient_Receipts',  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Patient_Receipts.ndf')
 LOG ON
( NAME = N'HRMS_Medical_Store_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Medical_Store_log.ldf')
GO

To verify the details of the database, files, and filegroups from the T-SQL commands above then run the following query:

USE [master];
GO
SELECT name [Database Name],
       create_date [Database Create Date],
       compatibility_level [Database Compatibility Level],
       collation_name [default collation],
       user_access_desc [User Access],
       state_desc [Database State],
       recovery_model_desc [Recovery Model]
FROM sys.databases
WHERE name = 'HRMS_Medical_Store';
GO
USE [HRMS_Medical_Store];
GO
SELECT DB_NAME() AS DatabaseName,
       df.type_desc [File Type],
       df.name [File Name],
       physical_name [File Location],
       state_desc [Database File status]
FROM sys.database_files df
    LEFT JOIN sys.filegroups sf
        ON df.data_space_id = sf.data_space_id;

Output

list of sql server database and database files

Advanced Database Creation

This section covers different scenarios for creating an SQL Server database with additional options.

Example 1: Create a Database with a Simple Recovery Model

If you want to create a database with different collations, the database’s recovery model is SIMPLE, and the compatibility level is 120 (SQL Server 2014). Note: These parameters must be changed after creating the database, therefore we must use the ALTER DATABASE statement after the CREATE DATABASE statement.

Here is the T-SQL query to create the HRMS_Laboratory_Test database with compatibility level 120 and a SIMPLE recovery model.

CREATE DATABASE [HRMS_Laboratory_Tests]
GO
ALTER DATABASE [HRMS_Laboratory_Tests] SET COMPATIBILITY_LEVEL = 120
GO
ALTER DATABASE [HRMS_Laboratory_Tests] SET RECOVERY SIMPLE
GO

To view the details of the database, run the following query next:

USE [master];
GO
SELECT name [Database Name],
       create_date [Database Create Date],
       compatibility_level [Database Compatibility Level],
       collation_name [default collation],
       user_access_desc [User Access],
       state_desc [Database State],
       recovery_model_desc [Recovery Model]
FROM sys.databases
WHERE name = 'HRMS_Laboratory_Tests';
GO

Output

Example 2: Create a Database with a FILESTREAM Filegroup

To illustrate this example, the FILESTREAM feature is used to store and manage large binary data (such as documents, images, audio, and video files) in the file system. It provides transactional consistency and database-level control on binary data. You can refer to the FILESTREAM tips on MSSQLTips.com, which includes syntax and practical use cases.

Let’s see a simple example. Suppose we want to create a database named HRMS_Account with a FILESTREAM filegroup. The HRMS_Account database contains one FILESTREAM filegroup named HRMS_Account_Receipt, which stores the soft copies of invoices. The filestream location is D:\HRMS_Account_Receipts\HRMS_Account_Receipt.

The T-SQL command to create a database is as follows:

CREATE DATABASE [HRMS_Account]
ON PRIMARY
    (
           NAME = N'HRMS_Account',
           FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Account.mdf'
    ),
FILEGROUP [HRMS_Account_Receipts] CONTAINS FILESTREAM
    (
           NAME = N'HRMS_Account_Receipt',
           FILENAME = N'D:\HRMS_Account_Receipts\HRMS_Account_Receipt'
    )
LOG ON
    (
           NAME = N'HRMS_Account_log',
           FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Account_log.ldf'
    );
 
GO

To demonstrate, run the following query to view the details of the database:

Output

get list of sql server database settting and files

Example 3: Create a Memory-optimized (In-memory OLTP) Database

Next, is a code example for the In-Memory OLTP feature in SQL Server that is designed to improve the performance of transaction processing workloads by storing and processing data entirely in memory. You can read In-Memory OLTP overview and usage scenarios documentation to learn more.

The syntax to create a memory-optimized SQL Server database is:

CREATE DATABASE [DBName]
ON PRIMARY
   (NAME = 'LogicalName_FG_Primary', FILENAME = 'Location_FG_Primary')
    FILEGROUP [MemoryOptimize_FG] CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = 'LogicalName_MemoryOptimize_FG', FILENAME = 'Location_MemoryOptimize_FG')
LOG ON
( NAME = N'LogicalName_LogFile', FILENAME = N'Location_LogFile')

In the syntax:

DBName: Specify the desired database name.
LogicalName_fg_Primary: Specify the logical name of the data file of the Primary filegroup.
Location_FG_Pary: Specify the location of the Primary filegroup data file.
LogicalName_MemoryOptimize_FG: Specify the logical name of the file of the memory-optimized filegroup.
Location_MemoryOptimized_FG: Specify the location where In-memory datafiles will be stored.
LogicalName_LogFIle: Logical name of the transaction log file.
Location_LogFile: Location of the transaction log file.

Create Database Example for In-Memory OLTP Database

We want to create a highly transactional memory-optimized database named HRMS_Patient_Registration. Specify the “CONTAINS MEMORY_OPTIMIZED_DATA” option with a name of memory optimized file group and its location in CREATE DATABASE statement.

Execute this T-SQL query to create the HRMS_Patient_Registration database:

CREATE DATABASE [HRMS_Patient_Registration]
ON PRIMARY
       (
           NAME = N'HRMS_Patient_Registration',
           FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Patient_Registration.mdf',
           SIZE = 8192KB,
           FILEGROWTH = 65536KB
       ),
FILEGROUP [HRMS_Patient_Registration_Memory_optimized_data] CONTAINS MEMORY_OPTIMIZED_DATA
       (
           NAME = N'HRMS_Patient_Registration_IMOLTP',
           FILENAME = N'D:\HRMS_Patient_Registration_Memory_optimized_data\HRMS_Patient_Registration_IMOLTP'
       )
LOG ON
       (
           NAME = N'HRMS_Patient_Registration_log',
           FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Patient_Registration_log.ldf',
           SIZE = 8192KB,
           FILEGROWTH = 65536KB
       );
GO
 
USE [HRMS_Patient_Registration];
GO
 
IF NOT EXISTS
(
    SELECT name
    FROM sys.filegroups
    WHERE is_default = 1
          AND name = N'PRIMARY'
)
    ALTER DATABASE [HRMS_Patient_Registration]
    MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

Next, run the following query to view the details of the database:

USE [master];
GO
 
SELECT name [Database Name],
       create_date [Database Create Date],
       compatibility_level [Database Compatibility Level],
       collation_name [default collation],
       user_access_desc [User Access],
       state_desc [Database State],
       recovery_model_desc [Recovery Model]
FROM sys.databases
WHERE name = 'HRMS_Patient_Registration';
GO
 
USE [HRMS_Patient_Registration];
GO
 
SELECT DB_NAME() AS DatabaseName,
       df.type_desc [File Type],
       df.name [File Name],
       physical_name [File Location],
       state_desc [Database File status]
FROM sys.database_files df
    LEFT JOIN sys.filegroups sf
        ON df.data_space_id = sf.data_space_id;

Output

Summary

In summary, we learned about SQL Server databases, specifically the basics of the SQL Server system and user database. We learned how to create a SQL Server database using SQL Server Management Studio and T-SQL scripts from scratch.

Next Steps

Finally, Check out the following resources:
- Database Administration Tips
- DBA Best Practices Tips

Resources

Community

Subscribe