The SQL Week: Create Database Statement

Source: Author

Don’t worry!

It’s not as difficult as the image above suggests. Of course, there are a few options besides CREATE DATABASE, and you’ll learn about them in this article.

Just an interesting fact before we start: you are limited to 32,767 databases that can be created on a SQL Server instance, be careful and remember to drop some! ;)

Create Database Statement

The CREATE DATABASE statement in SQL Server is used to create a new user-defined database. It allows you to define not only the name of the database but also its file structure, size and growth properties.

You need to define two files:

• .mdf (master data file): This is your primary data file, where all the data and schemas are stored.
• .ldf (log data file): This is your log file, where all the transaction logs (insert, update, delete) are recorded.

We can also create .ndf, which is a secondary data file used when you want to split your database data across multiple files or disks, which is an option for large databases.

So, what happens when you run the following command?

CREATE DATABASE [fin_pulse]

The SQL Server automatically create your .mdf and your .ldf on your default Microsoft SQL Server installation path:

Source: Author

It always creates with a default size that is defined in the model database:

USE model;
EXEC sp_helpfile;

In my case it is (and probably in yours too):

Source: Author

You can see the same size in the files.

Okay, how do you change this size now? Or rather, how do you customize your database creation and add limits?

First of all, let’s drop our current database:

DROP DATABASE [fin_pulse]

Now, let’s create the database with the following statement:

CREATE DATABASE fin_pulse
ON PRIMARY 
(
    NAME = finpulse_data,
    FILENAME = 'C:\SQLData\finpulse_data.mdf',
    SIZE = 10MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
)
LOG ON 
(
    NAME = finpulse_log,
    FILENAME = 'C:\SQLData\finpulse_log.ldf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10MB
);

You can see that everything could be customized based on the parameters you provide.

ON PRIMARY

Defines the data file for the database. The keyword PRIMARY indicates that this filegroup is the default one for storing data.

Get Lorenzo Uriel’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Inside this block:

• NAME = finpulse_data: Logical name used internally by SQL Server to refer to the data file.
• FILENAME = ‘C:\SQLData\finpulse_data.mdf’: Physical file path where the data file (.mdf) is stored. This path must exist on the server.
• SIZE = 10MB: Initial size of the data file. This is the amount of space allocated at creation.
• MAXSIZE = 200MB: The maximum size this file can grow to. Prevents uncontrolled disk usage.
• FILEGROWTH = 10MB: Determines how much space the file grows each time it runs out of room.

LOG ON

Defines the transaction log file(s).

Inside this block:

• NAME = finpulse_log: Logical name for the log file.
• FILENAME = ‘C:\SQLData\finpulse_log.ldf’: Physical file location for the log file.
• SIZE = 10MB: Initial size of the log file.
• MAXSIZE = 100MB: Maximum size allowed for the log file.
• FILEGROWTH = 10MB: Growth increment for the log file.

So, if you check the folder you will see your files:

Source: Author

What happens if you need to change you database?

It’s easy, you can run the ALTER DATABASE statement.

ALTER DATABASE

With this statement you can modify or resize your file settings:

ALTER DATABASE fin_pulse 
MODIFY FILE 
(
    NAME = finpulse_data,  -- Logical name of the .mdf file
    SIZE = 100MB,
    FILEGROWTH = 10MB,
    MAXSIZE = 500MB
);

ALTER DATABASE fin_pulse 
MODIFY FILE 
(
    NAME = finpulse_log, -- Logical name of the .ldf file
    FILEGROWTH = 20MB
);

And with the same statement you can add new files if needed:

ALTER DATABASE fin_pulse 
ADD FILE 
(
    NAME = finpulse_archive,
    FILENAME = 'C:\SQLData\finpulse_archive.ndf',
    SIZE = 100MB,
    FILEGROWTH = 10MB
);

You cannot directly rename or move the file location using ALTER DATABASE, just the file structure.

If you are finished and want to remove, just run:

DROP DATABASE fin_pulse 

Enjoying the content?

Support me saying thanks by buying me a coffee! ☕

Thanks and see ya!

Doing What Needs to Be Done.