This post is part of out series of blogs titles, “An Introduction to SQL Server”. These are aimed at people who new to SQL Server and need to learn the basics. We have tried to put this together in a structured way that provides some form of flow. This post is all about creating databases. In SQL Server, a database is a structured collection of data that is stored and accessed electronically. SQL Server databases are pivotal to managing and organising your data effectively. Understanding how to create and configure a database is fundamental for anyone working with SQL Server.

A user database in SQL Server is a database created by the user to store application data. Unlike system databases, which are created and managed by SQL Server itself for internal operations, user databases are meant to hold the data specific to your applications and business needs. Examples of user databases include databases for customer information, product inventories, and financial records. If you are wondering where the system databases material is we covered them in an earlier post

In SQL Server Management Studio (SSMS), user databases are managed under the “Databases” folder in the Object Explorer. When you expand the “Databases” folder, you will see a list of all databases present on the server, including both system and user databases. The system databases will have their own sub folder called system databases

A SQL Server database consists of at least two types of files:

1. Data File (.mdf): This file contains the actual data and objects such as tables, indexes, stored procedures, and views. The primary data file has the extension .mdf.
2. Log File (.ldf): This file contains the transaction log information, which is used to ensure data integrity by recording all transactions and changes made to the database. The log file has the extension .ldf.

In addition to the primary data file, a database can have secondary data files with the extension .ndf, which can be used to spread data across multiple disks for improved performance and storage management.

When setting up your database files, consider the following best practices to optimise performance and manageability, some of the below is the traditional method of laying out disk and database files, these days with the advents of SAN and other storage technology these might not be as applicable as they once were but it is still common to split the log and data drives/mounts points etc:

• Separate Data and Log Files: Place the data files (.mdf and .ndf) and log files (.ldf) on separate physical drives. This improves performance because SQL Server reads and writes to data files and log files differently. gRa
• Allocate Sufficient Space: Ensure that your data and log files are initially sized appropriately to minimize the need for auto-growth operations, which can impact performance.
• Monitor File Growth: Regularly monitor the size and growth of your database files to manage disk space effectively and avoid unexpected issues.

Here’s a step-by-step guide to creating a new database in SQL Server Management Studio (SSMS):

1. Open SQL Server Management Studio: Launch SSMS and connect to your SQL Server instance.

2. Connect to the Database Engine: In the “Connect to Server” window, select the appropriate server type, server name, and authentication method, then click “Connect.”

3. Open the New Database Dialog: In the Object Explorer, right-click the “Databases” folder and select “New Database…”

4. Enter Database Name: In the “New Database” window, enter a name for your database in the “Database name” field.

5. Configure Database Files:

   • Data File: In the “Database files” section, you’ll see an entry for the primary data file. You can specify the logical name, file type (Data), filegroup (PRIMARY), initial size, and file path.
   • Log File: Similarly, configure the log file by specifying its logical name, file type (Log), initial size, and file path.

6. Specify File Growth: Configure the auto-growth settings for your data and log files by clicking the ellipsis button (…) under the “Autogrowth” column. This allows you to set growth increments by percentage or fixed size.

7. Set Options (Optional): You can further configure options such as collation, recovery model, and other database properties by navigating to the “Options” page in the “New Database” window.

8. Create the Database: After configuring the necessary settings, click “OK” to create the new database. SQL Server will create the database with the specified files and settings.

9. Verify Database Creation: In the Object Explorer, expand the “Databases” folder to see your newly created database. You can further expand this database to view its objects such as tables, views, and stored procedures.

By following these steps, you can successfully create a new database in SQL Server Management Studio, ready to store and manage your application data.

You can also create a database using T-SQL Code. The following code would create the database you have seen created in the screen shots above

CREATE DATABASE [MyDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\Data\MyDB.mdf' , SIZE = 1048576KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'MyDB_log', FILENAME = N'C:\Logs\MyDB_log.ldf' , SIZE = 73728KB , FILEGROWTH = 65536KB )
WITH LEDGER = OFF
GO

Creating and managing databases is a foundational skill for working with SQL Server, and understanding the basics of database files, best practices, and configuration options will help you ensure your databases are optimized for performance and reliability.

Conclusion

Creating and managing databases is a foundational skill for working with SQL Server, and understanding the basics of database files, best practices, and configuration options will help you ensure your databases are optimised for performance and reliability.

Ready to put your new knowledge into practice? Don’t stop here! Dive deeper into SQL Server with our next post in the “An Introduction to SQL Server” series. Subscribe to our blog and get the latest updates delivered straight to your inbox. Join our community of SQL enthusiasts today and take your database skills to the next level. Click the subscribe button now and stay ahead in your SQL Server journey!

Useful Links

What are the files that make up a SQL Server Database?

Your Guide to Migration Options from SQL Server to Azure SQL Database